CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Evgeny Vostok
Evgeny Vostok

Reputation: 245

Service invoked too many times in a short time: exec qps. (Google Script)

Recently some of my app scripts are showing errors when executed simultaneously. Everything worked fine for years, apparently the change happened on Google side.

This is the execution transcript result: enter image description here

Unfortunately, the error does not show file name and line number to debug it properly, but my first line is: 

var ss = SpreadsheetApp.getActiveSpreadsheet()

Therefore I assume that calling SpreadsheetApp simultaneously by instances of the app is causing the error. SpreadsheetApp is not in the loop, nor inside any function. It is called to define global variable ss to be used by the app.

My apps are executed under my user name, since users should not have access to the Spreadsheet I'm accessing. I'm afraid I can't keep the entire Spreadsheet in Cache Service - I want app to update the Spreadsheet when it it called.

UPDATE:

Here is some relevant code from one of the apps.

function doGet(e){

  var ref = e.parameter.ref;
  var ss = SpreadsheetApp.openById('########')
  var logsheet = ss.getSheetByName('LOG');
  logsheet.appendRow([new Date(),ref,JSON.stringify(e.parameter)])
  try{
     //Main code goes here
     return ContentService.createTextOutput(JSON.stringify({result:"ok"})).setMimeType(ContentService.MimeType.JSON);
  }catch(e){
     return ContentService.createTextOutput(JSON.stringify(e)).setMimeType(ContentService.MimeType.JSON)}
}

As you can see I have try {} catch(e) in my function, and since the error I'm getting is not caught by it, I assume it happens right before.

Upvotes: 3

Views: 2380

Answers (2)

k06a
k06a

Reputation: 18795

Just discovered manual hack to avoid this error:

Open your spreadsheet from private browser mode. This will increase number of document viewers and will increase limits :)

Upvotes: 2

Amit Agarwal
Amit Agarwal

Reputation: 11268

You can put everything in the try-catch block. Also remember to publish a new version of the web app before execution.

function doGet(e) {
    var response = {};
    try {
        SpreadsheetApp.openById('########')
            .getSheetByName('LOG')
            .appendRow([new Date(), e.parameter.ref, JSON.stringify(e.parameter)]);
        response.result = "OK";
    } catch (error) {
        response.result = "Error: " + error.toString();
    }

    return ContentService
        .createTextOutput(JSON.stringify(response))
        .setMimeType(ContentService.MimeType.JSON);
}

Upvotes: 1

Related Questions