Long Processing Time Likely Due to Getvalue and Cell Inserts

Long processing time likely due to getValue and cell inserts

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

Long processing time likely due to getValue and cell inserts

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

Google script stops after 5mins of execution

You can manage triggers programmatically using Script Service, for what you're trying to do, this will involve checking the runtime regularly using something like:

taskStartTime = new Date();

at the beginning of the script and preiodically checking how long the script has been running with something like:

endTime = new Date();
duration = Math.round((endTime.getTime() - taskStartTime.getTime())/1000);
tasks_Processed_Array[tasks_Processed_Array.length - 1].duration = duration;

And if the duration is close to the max, then create a trigger for later and start where you left off.

However, there's no need to do this for your script. You are overusing .getValue() and .setValue() and this is slowing down the script significantly. Instead use getValues() to gather all data from a sheet at once, and then manipulate it in the script. Use setValues() to paste all the values at once. This post explains it more.

Every time you use getValue() the script needs to wait for Google to respond. It can be frustrating working with the Arrays that getValues() returns, but used correctly it will take a fraction of the execution time.

Here are a few examples:
var name1 = data[x][3]
var name2 = data[x][4]

Better yet, define name once before the if statement.

The setValues part is a little more challenging. Define an array outside the loop var ary = [] and then add elements to the array using ary.push([colToPasteIn, name]) that way you have a reference for where it should be pasted.

But getValue() slows down the script much more than setValue() so I think fixing the first part will be sufficient. More explanation here.

Triggers maintaining an activation sequence according to their creation and protecting spreadsheet data on every run

python

You can force a timeout.

the call may not even activate the GAS script because it will expire before the request arrives

There are two different types of timeout: Connect and read. Connect timeout waits for the first connection. Read timeout waits for the data to be sent by the server. Set the connect timeout to larger than a minute, but the read timeout to less than a few seconds.

# connect timeout to 60s(to make sure the request is actually sent) and read timeout to 1s
requests.get(webAppsUrl + "?backteam=b1", timeout=(60,1))

---

google-apps-script

There's rarely reason for a simple script to take 30 seconds. Your code can be optimized a lot by taking out all unnecessary getValue() calls and using arrays. See: Long processing time likely due to getValue and cell inserts

values are superimposed instead of being placed on different lines

This can be avoided by using .appendRow instead of setValues() as appendRow operation is atomic.

in addition to not respecting the sequence of the data sent

This cannot be avoided due to the async nature of the process. But, you can always rearrange the data later based on the id.

generating a queue of executions if necessary, and go on with my life without worrying about how long it will take to execute.

There's a simultaneous execution limit of 30

---

Related Topics