Optimize read/write in Google Apps Script

Question

It's my first time with JS or Apps Script and I've written a working script that reads in rows of data, and depending on one of the values in each row, writes out new rows to a second sheet, like this:

(I've simplified the script and just included two cases to demonstrate the transformation—the "input" sheet has rows and each row has a value "band"; the function checks this band value and, depending on which band, writes a set of rows to output)

function convertToOutput() {
var sheet = ss.getSheetByName('Input');
var output = ss.getSheetByName('Output');
var last = sheet.getLastRow();

output.getRange('A2:P').clearContent();
output.getRange('A2:P').setNumberFormat('@STRING@');

for(var i=2;i<last+1;i++){
  var courseCode = sheet.getRange(i,1).getValue();
  var sectionID= sheet.getRange(i,2).getValue();
  var band = sheet.getRange(i,3).getValue();

if( band == 'A')
   {
// insert rows for band into output
    var periodID = '1';
    var cycleDay = "#10100";
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);

    var periodID = '2'
    var cycleDay = "#00001";
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);

    var periodID = '7'
    var cycleDay = "#01000";
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);
    }
if( band == 'B')
  {
// insert rows for band into output
    var periodID = '2';
    var cycleDay = "#10100";
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);

    var periodID = '3'
    var cycleDay = "#00001";
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);

    var periodID = '7'
    var cycleDay = "#00010"
    output.appendRow([courseCode,sectionID,periodID,cycleDay]);
    }

It works as intended but right now I have it calling google apps for all the read/writes; I've read this answer and these best practices, but I haven't found an example close enough to the kind of transformation I'm doing to be able to figure out how to apply these practices without help.

How can I refactor using batching or the cache service to reduce the calls my function is making?

Tanaike · Accepted Answer · 2018-07-15T06:45:18.153

How about this modification? I think that there are several answers for your situation. So please think of this as one of them.

Modifiaction point :

Using getValue() and appendRow() in the for loop lead to higher cost. So I used getValues() and setValues() instead of them, respectively.

Modified script :

function convertToOutput() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Input');
  var output = ss.getSheetByName('Output');
  output.getRange('A2:P').clearContent();
  output.getRange('A2:P').setNumberFormat('@STRING@');
  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
  var appendValues = [];
  var pattern1 = [["1", "#10100"], ["2", "#00001"], ["7", "#01000"]];
  var pattern2 = [["2", "#10100"], ["3", "#00001"], ["7", "#00010"]];
  values.forEach(function(e) {
    var temp = [];
    if (e[2] == 'A') {
      temp = pattern1.map(function(f) {return [e[0], e[1], f[0], f[1]]});
    } else if (e[2] == 'B') {
      temp = pattern2.map(function(f) {return [e[0], e[1], f[0], f[1]]});
    }
    if (temp.length > 0) {
      Array.prototype.push.apply(appendValues, temp);
    }
  });
  output.getRange(output.getLastRow() + 1, 1, appendValues.length, appendValues[0].length).setValues(appendValues);
}

Note :

As a reference, you can see the difference between setValues() and appendRow() at here.

References :

If this result was not what you want, I'm sorry. At that time, can you provide the sample input and output you want? By this, I would like to modify it.

This is really brilliant, thank you—I learned a lot from taking this technique and expanding it to encompass more complex data — zqe, Aug 07 '18 at 01:19
@zqe Thank you for your response. I'm glad your issue was solved. Thank you, too. — Tanaike, Aug 07 '18 at 01:40

Optimize read/write in Google Apps Script

1 Answers1

Modifiaction point :

Modified script :

Note :

References :