Google script time out

110
January 23, 2020, at 7:50 PM

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);
    }
  }
}
Answer 1

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.

READ ALSO
Move text insertion point (caret) on click

Move text insertion point (caret) on click

To enhance UX by catching miss-clicks in my form, I'm using the following code on a bounding box around each text field:

108
reactjs custom component input value doesnt update with state change

reactjs custom component input value doesnt update with state change

I am working on a list app, and I am having issues with the components not updating correctlyI pull the users list from a JSON file and save it in a state

72
Counter animation for Django variable

Counter animation for Django variable

I want to do one of those cool dashboard counter animations using my Django variablesMy code isn't rendering any numbers yet so I'm doing something wrong

111
Wait until all jQuery Ajax requests are done?

Wait until all jQuery Ajax requests are done?

How do I make a function wait until all jQuery Ajax requests are done inside another function?

90