HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

Parse and format data on Google Sheets

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
formattingcodereviewgoogle-sheetsgoogle-apps-scriptstackoverflowparsingperformance
formatgooglesheetsparseanddata

Problem

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{

Solution

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;
}

Code Snippets

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))");
}
/**
 * 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;
}

Context

StackExchange Code Review Q#84797, answer score: 4

Revisions (0)

No revisions yet.