A script to automate data load from existing National Cold Call spreadsheet.

load_old_ncc_data.gs

/**
 * == A script to automate data load from existing National Cold Call spreadsheet. ==
 *
 * Adapted from and using the logging and configuration functions from the script:
 * 'A Google Apps Script for importing CSV data into a Google Spreadsheet' by Ian Lewis.
 *  https://gist.github.com/IanLewis/8310540
 * @author ianmlewis@gmail.com (Ian Lewis)
 * @author dunn.shane@gmail.com (Shane Dunn)
 * De Bortoli Wines Nov 2016
*/

/* =========== Globals ======================= */
var SPREADSHEET_ID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

/* =========== Setup Menu ======================= */
/**
 * Create a Menu when the script loads. Adds a new csvconfig sheet if
 * one doesn't exist.
 */
function onOpen() {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Add a menu.
  activeSpreadsheet.addMenu(
    "Load Data", [
      {name: "Get Data", functionName: "getNCCData"},
      {name: "Create Report", functionName: "createCSVReport"}
    ]
  );
  
  getOrCreateSheet_(CSV_CONFIG);
}

/* =========== Support functions ======================= */
/**
 * Check if the string is numeric with a seperating dash ('-')
 * [expecting strings like 70-222 ie. nn-nnn].
 */
function isNumeric(str)
{
  var allowedChars = "0123456789-";     //  For Checking Decimal , allowedChars = "0123456789.";
  var isDigit=true;
  var char;
  for (i = 0; i  < str.length && isDigit == true; i++) {
    char = str.charAt(i);
    if (allowedChars.indexOf(char) == -1)  isDigit = false;
  }
  return isDigit;
}

function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

function isValidNumber(n) {
  if(!isNaN(parseFloat(n)) && isFinite(n)){
    return true;
  } else {
    return false;
  }
}

/**
 * Check if the array is has anything in it
 */
Array.prototype.isNull = function (){
    return this.join().replace(/,/g,'').trim().length === 0;
};

/* =========== Main Extration functions ======================= */

function getNCCData(e) {
    setupLog_();
    var now = new Date(), i, config, configName, dsheet, esheet;
    log_('Running on: ' + now);
  
    var configs = getConfigs_(getOrCreateSheet_(CSV_CONFIG));
  
    if (!configs.length) {
        log_('No report configurations found');
    } else {
        log_('Found ' + configs.length + ' report configurations.');
      
      for (i = 0; config = configs[i]; ++i) {
          configName = config.query;
          if (config['sheet-name'] && config['error-name']) {
              if (config.url) {
                  try {
                      log_('Getting NCC Data: ' + configName);
                      dsheet = getOrCreateSheet_(config['sheet-name']);
                      esheet = getOrCreateSheet_(config['error-name']);
                      load_data(dsheet, esheet, config.url);
                  } catch (error) {
                      log_('Error executing ' + configName + ': ' + error.message);
                  }
              } else {
                  log_('No URL found: ' + configName); 
              }
          } else {
              log_('No sheet-name found: ' + configName);
          }
      }
      
      
    }
    log_('Script done');
    
    // Update the user about the status of the queries.
    if( e === undefined ) {
      displayLog_();
    }
}

function parseSheet(data, code) {
  var parsed = [], parsedRow, rowIndex, colIndex, rowData, dtCol, onCol, coCol, aoCol, btCol;
  // 'DATE', 'OUTLET NAME', 'COMMENTS', 'ACCOUNT OPENED', 'BUY THROUGH', 'NOTES'
  // 'Timestamp', 'Username', 'Sales Rep. Code', 'Date', 'Outlet Name', 'Comments', 'Account Opened', 'Buy Through', 'Notes'
  // check for header
  if ( data[0] == undefined || data[0].isnull ) {
    return parsed;
  } else {
    dtCol = 0;
    onCol = 1;
    coCol = 2;
    aoCol = 3;
    btCol = 4;
    rowData = data[0];
    for (colIndex = 0; colIndex <= rowData.length; ++colIndex) {
      var str1 = String(rowData[colIndex]);
      if ( str1 !== undefined && typeof str1 == 'string') {
        // Logger.log(str1);
        if ( str1 == 'DATE'           ) { dtCol = colIndex; }
        if ( str1 == 'OUTLET NAME'    ) { onCol = colIndex; }
        if ( str1 == 'COMMENTS'       ) { coCol = colIndex; }
        if ( str1 == 'ACCOUNT OPENED' ) { aoCol = colIndex; }
        if ( str1 == 'BUY THROUGH'    ) { btCol = colIndex; }
      }
    }
  }
  
  for (rowIndex = 1; rowIndex <= data.length; ++rowIndex) {
    rowData = data[rowIndex];
    if ( rowData !== undefined && rowData.join().replace(/,/g,'').trim() !== '' ) {
      // Logger.log('|' + rowData.join().replace(/,/g,'').trim() + '|');
      parsedRow = [code,NaN,'','','NO','',''];
      for (colIndex = 0; colIndex <= rowData.length; ++colIndex) {
        if ( rowData[colIndex] !== undefined && rowData[colIndex] !== '' ) {
          if ( colIndex == dtCol ) {
            //Logger.log(rowData[colIndex]);
            if ( isValidDate(rowData[colIndex]) ) {
              parsedRow[1] = rowData[colIndex]
            } else {
              parsedRow[6] = parsedRow[6] + ' ' + String(rowData[colIndex]).trim();
              //if ( isValidNumber(rowData[colIndex]) ) {
              //  parsedRow[1] = rowData[colIndex].toString();
              //} else {
              //  if (typeof rowData[colIndex] == 'string' && rowData[colIndex] !== '') {
              //    parsedRow[1] = rowData[colIndex];
              //  }
              //}
            }
          } else {
            if ( colIndex == onCol ) { parsedRow[2] = String(rowData[colIndex]).trim(); 
            } else {
              if ( colIndex == coCol ) { parsedRow[3] = String(rowData[colIndex]).trim();
              } else {
                if ( colIndex == aoCol ) { parsedRow[4] = String(rowData[colIndex]).trim();
                } else {
                  if ( colIndex == btCol ) { parsedRow[5] = String(rowData[colIndex]).trim();
                  } else {
                    parsedRow[6] = parsedRow[6] + ' ' + String(rowData[colIndex]).trim();
                  }
                }
              }
            }
          }
        }
      }
    parsed.push(parsedRow);
    }
  }
  return parsed;
}

function load_data (ddSheet,  edSheet, ssID) {
  var lData = [];
  var eData = [];
  var parsedData = [];
  var cold_call_ss = SpreadsheetApp.openById(ssID);
  var allsheets = cold_call_ss.getSheets();
  //for (var sheetNum = 1; sheetNum < allsheets.length; sheetNum++) {
  //  var data = allsheets[sheetNum].getDataRange().getValues();
  for (var s in allsheets) {
    // Logger.log(s);
    if ( s !== 'isNull' ) {
    var sheet = allsheets[s];
    //Logger.log(sheet.getName());
      var name = sheet.getName().trim();
      if ( isNumeric(name) ) {
        log_('Processing : ' + name);
        var data = sheet.getDataRange().getValues();
        // Logger.log(data);
        parsedData = parseSheet(data, name);
        if (parsedData !== undefined || parsedData.length) {
          for (i = 0; i <= parsedData.length; ++i) {
            var rowData = parsedData[i];
            // Logger.log(rowData);
            if ( rowData !== undefined && rowData !== null ) {
              if ( rowData[1] !== undefined && rowData[1]) {
                lData.push(rowData);
              } else {
                lData.push(rowData);
                eData.push(rowData);
              }
            }
          }
          log_('Processed : ' + i );
        }
      }
    }
  }
  // clear everything in the sheet
  ddSheet.clearContents().clearFormats();
  edSheet.clearContents().clearFormats();
  var nccHeader = [['DATE', 'OUTLET NAME', 'COMMENTS', 'ACCOUNT OPENED', 'BUY THROUGH', 'NOTES']];
  var nccNewHeader = [['Timestamp', 'Username', 'Sales Rep. Code', 'Date', 'Outlet Name', 'Comments', 'Account Opened', 'Buy Through', 'Notes']];
  edSheet.getRange(
    1,               /* first row    */
    1,               /* first column */
    1,               /* rows    */
    nccNewHeader[0].length /* columns */).setFontWeight('bold').setValues(nccNewHeader); // Set the font weight for the given range (“normal” or “bold”).
  // Logger.log(lData);
  ddSheet.getRange(
    1,               /* first row    */
    1,               /* first column */
    1,               /* rows    */
    nccNewHeader[0].length /* columns */).setFontWeight('bold').setValues(nccNewHeader); // Set the font weight for the given range (“normal” or “bold”).

  // set the values in the sheet (as efficiently as we know how)
  // Logger.log(eData);
  edSheet.getRange(
    2,              /* first row    */
    3,              /* first column */
    eData.length,   /* rows    */
    eData[0].length /* columns */).setNumberFormat('@STRING@').setValues(eData);
  // Logger.log(lData);
  if (lData.length) {
  ddSheet.getRange(
    2,              /* first row    */
    3,              /* first column */
    lData.length,   /* rows    */
    lData[0].length /* columns */).setNumberFormat('@STRING@').setValues(lData);
  }
}