CODEWITHSUNDEEP
javascriptfor-loopgoogle-apps-scriptgoogle-sheets
jjw
jjw

Reputation: 3

Google script time out

My script is timing out, I guess because it's going over the 6 minute limit. Does anyone have an idea how I can optimize it so it runs faster?

The purpose of this script is to copy all the data from one sheet to another sheet, and then fill down some formulas on the destination sheet.

The amount of data copied is quite large (20,000 rows and columns to R) but I don't think it's so much that it should time out? I'm very new to this, so any advice is appreciated!

Here's the script:

function copyPasteTo2ndSheet()
{
  var copyURL = 'ThisIsTheOriginSheetURL';
  var pasteURL = 'ThisIsTheDestinationSheetURL';

  var ssCopy = SpreadsheetApp.openByUrl(copyURL);
  var ssPaste = SpreadsheetApp.openByUrl(pasteURL);

  var copySheet = ssCopy.getSheetByName('Orders');
  var pasteSheet = ssPaste.getSheetByName('Orders');
  var lr = copySheet.getLastRow();
  var copyData = copySheet.getRange('A2:R'+lr).getDisplayValues(); 
  var pasteLr = pasteSheet.getLastRow();
  pasteSheet.getRange('A2:R'+pasteLr).clear();
  pasteSheet.getRange('A2:R'+lr).setValues(copyData);

  copyPasteFormula(pasteSheet)
}

function copyPasteFormula(sheet)
{
  var formulas = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getFormulas()[0];
  var lr = sheet.getLastRow();
  for(var i in formulas)
  {
    var formula = formulas[i];
    if(formula !== '')
    {
      var j = parseInt(i)+1;
      sheet.getRange(3, j, lr, 1).setFormula(formula);
    }
  }
}

Upvotes: 0

Views: 62

Answers (1)

Michael Pearson
Michael Pearson

Reputation: 96

From the experience that I have had, the sheet.getRange(3, j, lr, 1) line, that has to repeat for 20,000 cells is likely causing the issue.

Commands that get or set to the sheet often take longer and use more memory to run. As a result, it is recommended to either batch your gets or, in this case, you can likely copy an entire row paste it. Copying a formula and pasting it in a different cell will update cell references, just as it does in the UI.

Upvotes: 1

Related Questions