1

The following formula works well with less than 6000 rows. With more than 6000 rows or 100s it fails.

Any suggestions?

function removed() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 9, sheet.getLastRow(), 16);
  var formulas = range.getFormulas().map(r => r.splice(1));
  var values = range.getValues().map(([a, ...b], i) => a == 'Removed' ? b : formulas[i]);
  range.offset(0, 1, values.length, 15).setValues(values);
}

The error

Nov 3, 2022, 3:23:12 PM Error   Exception: Service Spreadsheets timed out while accessing document with id xxx
    at removed(Remove Formula:6:41)

Error

I have also used this variation starting at row 2. Both fail the same way.

function removed() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(2, 9, sheet.getLastRow()-1, 16);
  var formulas = range.getFormulas().map(r => r.splice(1));
  var values = range.getValues().map(([a, ...b], i) => a == 'Removed' ? b : formulas[i]);
  range.offset(0, 1, values.length, 15).setValues(values);
}
nord_poster
  • 31
  • 5
  • Unfortunately for you, this looks pretty efficient. Maybe do it in chunks or use the Query function in sheets? – pgSystemTester Nov 03 '22 at 13:44
  • @pgSystemTester but why does it stop at 100s, this i dont understand, shouldnt the timeout limit be 6 mins – nord_poster Nov 03 '22 at 13:45
  • Agreed, that's what I understand the timeout to be also. Is the destination sheet big enough as far as number of rows? Maybe there's some limit to the amount of data you can throw in a variable array. – pgSystemTester Nov 03 '22 at 13:55
  • @pgSystemTester do you think you could help with a suggestion for using chunks or query fucntion as you suggested, as i really dont know how to implement it – nord_poster Nov 03 '22 at 14:11
  • Post errors as text, as it is easy to search, be searched and to copy/paste. – TheMaster Nov 03 '22 at 14:27
  • Read the linked issues carefully. Workaround is to use sheets api instead. – TheMaster Nov 03 '22 at 14:43
  • @TheMaster Thanks for the links, I will, however, i don't know almost any of google script or API, if I can't figure it out myself, could i reach out to you again? – nord_poster Nov 03 '22 at 14:51
  • 1
    You can. You're also free to post a new question with the "specific part" you're having trouble with. – TheMaster Nov 03 '22 at 14:53

0 Answers0