4
\$\begingroup\$

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))");

 }
 }
\$\endgroup\$
4
\$\begingroup\$

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;
}
\$\endgroup\$
  • \$\begingroup\$ You could use this approach (which is faster) and still use a data structure to represent all the cell values being set (which would lead to cleaner, more maintainable code). \$\endgroup\$ – Snowbody May 8 '15 at 2:15
  • 2
    \$\begingroup\$ @Snowbody - yep! And if feasible, it would be good to move the "template" cell definitions right out of code altogether, and make them data. Maybe in a "template" or "config" sheet, and load the control data structure from there. \$\endgroup\$ – Mogsdad May 8 '15 at 4:02
  • \$\begingroup\$ Hi there! I really appreciate your suggestions. The only problem is that the sheet has many other formulas on it that are not written by the script, and your code seems to be overwriting these formulas and replacing them with plaintext values. Any workarounds? \$\endgroup\$ – cwg83 May 11 '15 at 12:47
  • \$\begingroup\$ I am definitely interested in the "template" or "config" sheet idea as well, but I'm not exactly sure how that would work (might be a little over my head at this time) \$\endgroup\$ – cwg83 May 11 '15 at 13:13
  • \$\begingroup\$ It looks like I need to set the dataRange variable to only the cells I need to be overwritten in order to avoid all of the formulas on the rest of my sheet being replaced with their values? I would rather not add code to the script to setFormula for every cell that contains a formula on my sheet, as there are quite a few. \$\endgroup\$ – cwg83 May 11 '15 at 13:25
2
\$\begingroup\$

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]);
}
\$\endgroup\$
  • \$\begingroup\$ Thank you Snowbody! I will try to combine both of these suggestions - I still need a workaround for the other formulas in my code being replaced with their values due to something in Mogdad's code above (of course he was not aware that there were other formulas present in the document so it is understandable) \$\endgroup\$ – cwg83 May 11 '15 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.