Long processing time likely due to getValue and cell inserts

Question

I've just written my first google apps scripts, ported from VBA, which formats a column of customer order information (thanks to you all of your direction).

Description:

The code identifies state codes by their - prefix, then combines the following first name with a last name (if it exists). It then writes "Order complete" where the last name would have been. Finally, it inserts a necessary blank cell if there is no gap between the orders (see image below).

Problem:

The issue is processing time. It cannot handle longer columns of data. I am warned that

Method Range.getValue is heavily used by the script.

Existing Optimizations:

Per the responses to this question, I've tried to keep as many variables outside the loop as possible, and also improved my if statements. @MuhammadGelbana suggests calling the Range.getValue method just once and moving around with its value...but I don't understand how this would/could work.

Code:

function format() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var lastRow = s.getRange("A:A").getLastRow();
var row, range1, cellValue, dash, offset1, offset2, offset3;

  //loop through all cells in column A
  for (row = 0; row < lastRow; row++) {
    range1 = s.getRange(row + 1, 1);

    //if cell substring is number, skip it
    //because substring cannot process numbers
    cellValue = range1.getValue();
    if (typeof cellValue === 'number') {continue;};
    dash = cellValue.substring(0, 1);

    offset1 = range1.offset(1, 0).getValue();
    offset2 = range1.offset(2, 0).getValue();
    offset3 = range1.offset(3, 0).getValue();

    //if -, then merge offset cells 1 and 2
    //and enter "Order complete" in offset cell 2.
    if (dash === "-") {
       range1.offset(1, 0).setValue(offset1 + " " + offset2);
       //Translate
       range1.offset(2, 0).setValue("Order complete");
     };

    //The real slow part...
    //if - and offset 3 is not blank, then INSERT CELL
    if (dash === "-" && offset3) {
       //select from three rows down to last
       //move selection one more row down (down 4 rows total)
       s.getRange(row + 1, 1, lastRow).offset(3, 0).moveTo(range1.offset(4, 0));
     };    
  };
}

Formatting Update:

For guidance on formatting the output with font or background colors, check this follow-up question here. Hopefully you can benefit from the advice these pros gave me :)

Answer 1

Issue:

• Usage of .getValue() and .setValue() in a loop resulting in increased processing time.

Documentation excerpts:

• Minimize calls to services:

Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on.

• Look ahead caching:

Google Apps Script already has some built-in optimization, such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.

• Minimize "number" of read/writes:

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes.

• Avoid alternating read/write:

Alternating read and write commands is slow

• Use arrays:

To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.

Slow script example:

/** 
 * Really Slow script example
 * Get values from A1:D2
 * Set values to A3:D4
 */

function slowScriptLikeVBA(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  //get A1:D2 and set it 2 rows down
  for(var row = 1; row <= 2; row++){
    for(var col = 1; col <= 4; col++){
      var sourceCellRange = sh.getRange(row, col, 1, 1);
      var targetCellRange = sh.getRange(row + 2, col, 1, 1);
      var sourceCellValue = sourceCellRange.getValue();//1 read call per loop
      targetCellRange.setValue(sourceCellValue);//1 write call per loop
    }
  }
}

• Notice that two calls are made per loop(Spreadsheet ss, Sheet sh and range calls are excluded. Only including the expensive get/set value calls). There are two loops; 8 read calls and 8 write calls are made in this example for a simple copy paste of 2x4 array.
• In addition, Notice that read and write calls alternated making "look-ahead" caching ineffective.
• Total calls to services: 16
• Time taken: ~5+ seconds

Fast script example:

/** 
 * Fast script example
 * Get values from A1:D2
 * Set values to A3:D4
 */

function fastScript(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  //get A1:D2 and set it 2 rows down
  var sourceRange = sh.getRange("A1:D2");
  var targetRange = sh.getRange("A3:D4");
  var sourceValues = sourceRange.getValues();//1 read call in total
  //modify `sourceValues` if needed
  //sourceValues looks like this two dimensional array:
  //[//outer array containing rows array
  // ["A1","B1","C1",D1], //row1(inner) array containing column element values
  // ["A2","B2","C2",D2],
  //]
  //@see https://stackoverflow.com/questions/63720612
  targetRange.setValues(sourceValues);//1 write call in total
}

• Total calls to services: 2
• Time taken: ~0.2 seconds

References:

• Best practices
• What does the range method getValues() return and setValues() accept?

Answer 2

Using methods like .getValue() and .moveTo() can be very expensive on execution time. An alternative approach is to use a batch operation where you get all the column values and iterate across the data reshaping as required before writing to the sheet in one call. When you run your script you may have noticed the following warning:

The script uses a method which is considered expensive. Each invocation generates a time consuming call to a remote server. That may have critical impact on the execution time of the script, especially on large data. If performance is an issue for the script, you should consider using another method, e.g. Range.getValues().

Using .getValues() and .setValues() your script can be rewritten as:

function format() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var lastRow = s.getLastRow(); // more efficient way to get last row
  var row;

  var data = s.getRange("A:A").getValues(); // gets a [][] of all values in the column
  var output = []; // we are going to build a [][] to output result

  //loop through all cells in column A
  for (row = 0; row < lastRow; row++) {
    var cellValue = data[row][0];
    var dash = false;
    if (typeof cellValue === 'string') {
      dash = cellValue.substring(0, 1); 
    } else { // if a number copy to our output array
      output.push([cellValue]); 
    }
    // if a dash  
    if (dash === "-") {
      var name = (data[(row+1)][0]+" "+data[(row+2)][0]).trim(); // build name
      output.push([cellValue]); // add row -state
      output.push([name]); // add row name 
      output.push(["Order complete"]); // row order complete
      output.push([""]); // add blank row
      row++; // jump an extra row to speed things up
    } 
  }
  s.clear(); // clear all existing data on sheet
  // if you need other data in sheet then could
  // s.deleteColumn(1);
  // s.insertColumns(1);

  // set the values we've made in our output [][] array
  s.getRange(1, 1, output.length).setValues(output);
}

Testing your script with 20 rows of data revealed it took 4.415 seconds to execute, the above code completes in 0.019 seconds