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?