Long processing time likely due to getValue and cell inserts

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. 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?

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