6

I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's failing more and more often. There are only 3 triggers on this file onOpen, onEdit(e) and the installed trigger that calls the percentChange function once a day in the morning. It's super simple, it grabs 2 named ranges, one is a column of percentages and the other contains the length of that data and then copies the percentages to another column so that we can track the daily percentage difference.

Here is the code(link to a bare spreadsheet):

function percentChange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overview Status');

  var calcPercentRangeObject = ss.getRange('calcPerc');
  var calcPercentRow = calcPercentRangeObject.getRow()+2;
  var calcPercentColumn = calcPercentRangeObject.getColumn();

  var dataRangeObject = ss.getRange('numCabs');
  var dataRow = dataRangeObject.getRow()+2;
  var dataColumn = dataRangeObject.getColumn();
  var dataLength =  dataRangeObject.getValue();
  var currentPercent = ss.getRange(calcPercentRow, calcPercentColumn, dataLength, 1).getValues();

  ss.getRange(dataRow, dataColumn, dataLength, 1).setValues(currentPercent);

}

Should I add a delay? That seems like a "janky" workaround...

Latest failure

Previous failures and completions

Another question: why does this code take 10+seconds to run from the trigger but generally takes less than a second to run from the script editor?


Edit: It's not really a correct fix but I put in a 400ms delay after line 6 and have only gotten the error message once more...what an odd work around... This stopped working, I tried multiple delays spread out and removing the delay altogether. I still don't understand why it's intermittent without the code changing?!?!


I am the owner. It's shared with less than 60 people I created the trigger manually The only other installable trigger is onEdit() The script is set to run when no-one would really be looking at it a couple hours before the shift would start.

8
  • How complex is your spreadsheet? (number of cells, sheets, use of volatile functions, use of array functions like MMULT, use of import functions like IMPORTRANGE..., custom functions) How long it usually takes the spreadsheet to be ready for editing when opening (recalculation time)? Commented Jun 20, 2019 at 16:26
  • There are some array formulas, import ranges, etc. I could make a copy and share it if that'd be helpful? Even on my phone it doesn't usually take more than a few seconds to load/update. Commented Jun 21, 2019 at 18:29
  • Adding a time delay stopped working reliably, why is this issue so intermittent? It almost makes me think this is a Google issue? Commented Feb 3, 2020 at 14:51
  • The second screenshoot shows several percentChange Time-Driven triggered at the same time. It's very likely that you script has several time-driven triggers, if so, delete all of them. In order to help you to find what caused the problem add more details about you are creating the time-driven trigger. If you think that this is a Google issue, raise an issue report through the Google Apps Script issue tracker. Commented Feb 3, 2020 at 15:56
  • 1
    It's set to run once a day, and is the only time based trigger, at 5-6am. I'll submit to Google and see what comes up. Thanks Commented Feb 4, 2020 at 22:25

3 Answers 3

0

According to https://developers.google.com/apps-script/guides/services/quotas, quotas are per account and per day, so triggers created for other project scripts may be consuming your account quota.

Feature Consumer (e.g., gmail.com)
and
G Suite free edition
(legacy)
Google Workspace
accounts
Triggers total runtime 90 min / day 6 hr / day
-1

0

https://developers.google.com/apps-script/guides/services/quotas

Script invoked too many times per second for this Google user account. This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.

1
  • I don't think that tiny script is anywhere near those limits. Commented May 31, 2019 at 17:39
-2

I think this has to do with the amount of time onEdit is being invoked. Maybe try to limit the amount of times this function is invoked per second. For me, I needed to only send 30 rows to the sheet at a time, let it process, and then try again. Let us know if that helps or you disagree.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.