John Doe
John Doe

Reputation: 23

Google sheets script, timed out just after 30ish something seconds

I created a scrpit to turns time copied from Excel to proper format.
Because 12.30, 09.34, 07.40, or 08.00 would end up as 12.3, 9.34, 7.4, and 8 when I pasted them into google sheets. Some tables event contain time format like 1230, 934, or 740 so I have to fix that too. Here is my code.

function onEdit(event) {
  if (event.range.getColumn() == 3 ) {
    for(var i = 1; i<= range.getNumRows() ; i++){
      var r = range.getCell(i, 1);
      var rvalue = r.getValue();
      var rvalues = rvalue.toString();
      var rvalength = rvalues.length;
      if (rvalue != ""){
        if (rvalength == 3) {
          if (parseInt(rvalues.substring(0,2))>24) {
            r.setValue(rvalues.substring(0,1)+"."+rvalues.substring(1,3));
          }
          else
          {
            r.setValue(rvalues.substring(0,2)+"."+rvalues.substring(2,3));
          }
        }
        else if ( rvalength == 4 ) {
          r.setValue(rvalues.substring(0,2)+"."+rvalues.substring(2,4));
        }
        else if ( rvalength == 2 ) {
          r.setValue("."+rvalues);
        }
        r.setNumberFormat("00.00");
      }
    }
  }
}

It works fine when it's a small table, but when it's getting big like it's 200x500 table, the code just stopped. Execution log said the status is "Timed Out" and the duration is always 30-33 seconds.
I heard that Google App Scripts has 6 minutes timeout so I have no idea why my code always Timed Out at about 30ish something seconds.

I don't think the reason is because of my code but I think it can also be optimized for better performance, I just don't know how.

Upvotes: 1

Views: 2855

Answers (2)

John Doe
John Doe

Reputation: 23

I've optimized my code based on Diego's answer

So I made it reads and operates the data as I want as an array first. Then use setValues() to put everything into the sheet at once instead of doing "read->calculate->write" each cell one by one. The result is that even 1000x1000 tables only takes 1-2 seconds to run now.

Here is the code:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var datValue = new Array(numRows);
  var numFormat = new Array(numRows);
  for(var x = 0; x< numRows ; x++){
    datValue[x] = new Array(numCols);
    numFormat[x] = ["00.00"];
    for(var y = 0; y< numCols ; y++){
      var evalue = range.getCell(x+1,y+1).getValue();
      if ( range.getCell(x+1,y+1).getColumn() == 3 ){
        if ( evalue > 99 ) {
            datValue[x][y] = evalue/100;
        }
        else {
        datValue[x][y] = evalue;
        }
      }
    }
  }
  var range2 = sheet.getRange(range.getRow(), 3,  numRows,1);
  range2.setNumberFormats(numFormat);
  range.setValues(datValue);
}

edit: further optimization which only operate values on the column 3 instead of all pasted values

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var datValue = new Array(numRows);
  var numFormat = new Array(numRows);
  for(var x = 0; x< numRows ; x++){
    numFormat[x] = ["00.00"];
    for(var y = 0; y< numCols ; y++){
      var evalue = range.getCell(x+1,y+1).getValue();
      if ( range.getCell(x+1,y+1).getColumn() == 3 ){
        if ( evalue > 99 ) {
            datValue[x] = [evalue/100];
        }
        else {
        datValue[x] = [evalue];
        }
      }
    }
  }
  var range2 = sheet.getRange(range.getRow(), 3,  numRows,1);
  range2.setNumberFormats(numFormat);
  range2.setValues(datValue);
}

Upvotes: 1

Diego
Diego

Reputation: 9571

Simple triggers like onEdit() cannot run for longer than 30 seconds.

You could try converting it to an installable trigger, although this may not automatically work for others unless if they first authorize the script. (To convert it, basically rename the function and follow these instructions...event source should be "spreadsheet" and event type should be "on edit".)

Alternatively, given the variability of the data formats that you need to clean up, you may want to instead call the function from a custom menu instead of relying on the on-edit trigger. You can select the table in your spreadsheet and then execute your function on the range returned by Spreadsheet.getActiveRange().

Regarding optimization... if you do it right, and your script could clearly be optimized, you could make this run much faster and well within the 30-second limit. Please review the batch operations section of best practices. From what I see in your code, you would specifically want to take advantage of:

Upvotes: 8

Related Questions