google script taking too much time. getting error service time out

Question

I have A simple code to copy content from one sheet to other and it was working fine earlier. Suddenly it is taking too much time and failing with error "service

I tried to copy whole google worksheet and run function manually it worked fine. Even scheduling the function also worked fine but when I tried later neither able to run manually, also scheduled trigger is also failing even in new google worksheet with error Service timed out: Spreadsheets (line 6, file "ORB_1")

function ORB_1H() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName('values');
  var logSheet = ss.getSheetByName('1HR');
  logSheet.clearContents();
  var lastRow = logSheet.getLastRow();
  logSheet.getRange(lastRow + 1, 1).setValue(new Date());  // insert timestamp
  var range = currentSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 2, 1), {contentsOnly: true});
}

Data should be copied automatically as it was working fine earlier.

Some questions: (1) How big is the sheet "1HR"? It looks like this will insert 2 rows per execution, and it will gradually get bigger and bigger. (2) Did you look at the execution transcript to see where the most time is being spent? — Paul
data needs to copied frm other sheet names "values" (in same workbook) having only 197 records & 9 columns .earlier i was happening smoothly in 30 sec. let me know the steps to "look at the execution transcript to see where the most time is being spent?" — MGGameLab
Whilst in the script editor, go to the "View" tab and it's the first option. That will show you the execution transcript with the number of seconds spent on each part. It should give you some indication where it's having problems. — Paul

Cooper Cooper · Accepted Answer · 2019-05-18T13:47:43

Since you appear to be having problems around line 6 how about we change the function to this since you cleared contents the line before it shouldn't make any difference and maybe the problem will go away.

function ORB_1H() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName('values');
  var logSheet = ss.getSheetByName('1HR');
  logSheet.clearContents();
  logSheet.getRange(1, 1).setValue(new Date());  // insert timestamp
  var range = currentSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 2, 1), {contentsOnly: true});
}

google script taking too much time. getting error service time out

1 Answers