Parse and format data on Google Sheets

Question

This is a script that I use on a Google sheet for parsing data that has been pasted from various other software and getting it into the correct format and columns on another sheet. Data is pasted into the "CL" sheet where it is parsed and formatted via formulas on the sheet.

The data is then sent to either just the "Work Log" sheet or both the "Work Log" sheet and "Represented" sheet if cell D15 is set to "Yes".

The cells that were changed on sheet "CL" are then deleted and/or changed back to the original values so another entry can be pasted.

Is there a more efficient way to setValue on each of the cells that need to be changed? Or do I need to do them one at a time, as I have them here?

function SendToWorkLogCL() { 

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("CL");
  var sheet2 = ss.getSheetByName("Work Log"); 
  var repsheet = ss.getSheetByName("Representing"); 
  var rep = sheet1.getRange("D15");
  var repvalue = rep.getValue();

  if (repvalue == 'Yes') {    
  sheet1.getRange("A2:W2").copyTo(repsheet.getRange(repsheet.getLastRow()+1,1,1,7), {contentsOnly:true});
  sheet1.getRange("A2:W2").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,7), {contentsOnly:true});
  SpreadsheetApp.getActiveSheet().getRange('A3:B23').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('A3').setValue('Ctrl+SHIFT+V');
  SpreadsheetApp.getActiveSheet().getRange('D12').setValue('Chargeback');
  SpreadsheetApp.getActiveSheet().getRange('D13').setValue('Yes');
  SpreadsheetApp.getActiveSheet().getRange('D14:D15').setValue('No');
  SpreadsheetApp.getActiveSheet().getRange('D17').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('D18').setValue('0');
  SpreadsheetApp.getActiveSheet().getRange('D20').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('G12:G15').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('D16').setFormula("=IF(A2=\"\",\"\",VLOOKUP(E18,'Data Validation'!C2:E204,3,FALSE))");

  }else{ 
  sheet1.getRange("A2:W2").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,7), {contentsOnly:true});
  SpreadsheetApp.getActiveSheet().getRange('A3:B23').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('A3').setValue('Ctrl+SHIFT+V');
  SpreadsheetApp.getActiveSheet().getRange('D12').setValue('Chargeback');
  SpreadsheetApp.getActiveSheet().getRange('D13').setValue('Yes');
  SpreadsheetApp.getActiveSheet().getRange('D14:D15').setValue('No');
  SpreadsheetApp.getActiveSheet().getRange('D17').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('D18').setValue('0');
  SpreadsheetApp.getActiveSheet().getRange('D20').setValue('');
  SpreadsheetApp.getActiveSheet().getRange('D16').setFormula("=IF(A2=\"\",\"\",VLOOKUP(E18,'Data Validation'!C2:E204,3,FALSE))");

 }
 }

Answer 1

In Google Apps Script, Less code doesn't always mean greater efficiency wrt real-time. According to best practices, focus first on reducing service calls.

To save real time, use a single .setValues() with ALL the desired content. "Javascript" operations are very fast compared to calls to Google's services.

You do have redundant code because of the structure of your if / then / else blocks; you can move repeated portions outside of that logic.

function SendToWorkLogCL() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("CL");
  var sheet2 = ss.getSheetByName("Work Log");
  var repsheet = ss.getSheetByName("Representing");
  var rep = sheet1.getRange("D15");
  var repvalue = rep.getValue();

  var clRange = sheet1.getRange("A2:W2");
  if (repvalue == 'Yes') {
    clRange.copyTo(repsheet.getRange(repsheet.getLastRow() + 1, 1, 1, 7), {
      contentsOnly: true
    });
  }

  // This part does not need to be in an if/then/else, because it's always done.
  clRange.copyTo(sheet2.getRange(sheet2.getLastRow() + 1, 1, 1, 7), {
    contentsOnly: true
  });

  // Only need this block once, instead of two identical copies.
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  values[3-1][ColA1ToIndex('A')] = 'Ctrl+SHIFT+V';

  // ...getRange('A3:B23').setValue('') handled in loops
  for (var col=ColA1ToIndex('A'); col <= ColA1ToIndex('B'); col++) {
    for (var row=(3-1); row<=(23-1); row++) {
      values[row][col] = '';
    }
  }
  values[12-1][ColA1ToIndex('D')] = 'Chargeback';
  values[13-1][ColA1ToIndex('D')] = 'Yes';
  values[14-1][ColA1ToIndex('D')] = 'No';
  values[15-1][ColA1ToIndex('D')] = 'No';
  values[17-1][ColA1ToIndex('D')] = '';
  values[18-1][ColA1ToIndex('D')] = '0';
  values[20-1][ColA1ToIndex('D')] = '';
  // Finally, one service call to write ALL values. Fast!
  dataRange.setValues(values);
  // Formulas would have been overwritten by values, so need to be refreshed
  sheet.getRange('D16').setFormula("=IF(A2=\"\",\"\",VLOOKUP(E18,'Data Validation'!C2:E204,3,FALSE))");
}

Helper functions: For clear conversion between A1Notation and 0-based array indices (full implementation in this gist):

/**
 * Return a 0-based array index corresponding to a spreadsheet column
 * label, as in A1 notation.
 *
 * @param {String}    colA1    Column label to be converted.
 *
 * @return {Number}            0-based array index.
 */
function ColA1ToIndex( colA1 ) {
  if (typeof colA1 !== 'string' || colA1.length > 2) 
    throw new Error( "Expected column label." );

  var A = "A".charCodeAt(0);

  var number = colA1.charCodeAt(colA1.length-1) - A;
  if (colA1.length == 2) {
    number += 26 * (colA1.charCodeAt(0) - A + 1);
  }
  return number;
}



/**
 * Return a 0-based array index corresponding to a spreadsheet row
 * number, as in A1 notation.
 *
 * @param {Number}    rowA1    Row number to be converted.
 *
 * @return {Number}            0-based array index.
 */
function RowA1ToIndex( rowA1 ) {
  return rowA1 - 1;
}

Answer 2

What you can do is create a data structure (probably an array of arrays) that lists the ranges and the values for each cell/range.

Also, don't repeat the code that's common in the then and else parts of the if. Just move them outside the if.

You could also assign to a temporary variable instead of repeatedly doing SpreadsheetApp.getActiveSheet().

Then your code would just have to loop over all the elements in each data structure and set the appropriate cell to the appropriate value.

e.g.

gR = SpreadsheetApp.getActiveSheet().getRange;
myArray = [ ['A3:B23,''] ,
    ['A3','Ctrl+SHIFT+V'],
    ['D12','Chargeback'],
    ['D13','Yes'],
    ['D14:D15','No'],
    ['D17',''],
    ['D18','0'],
    ['D20',''],
    ['G12:G15',''],
    ['D16',"=IF(A2=\"\",\"\",VLOOKUP(E18,'Data Validation'!C2:E204,3,FALSE))"]]
for(i=0;i<myArray.length;i++) {
   gR(myArray[i][0]).setValue(myArray[i][1]);
}