I'm sending 5 requests to my Web App:

import requests

backodds = "3.00"
layteam = "Flamengo"
layodds = "1.50"
advantage = "25.55"
webAppsUrl = "https://script.google.com/macros/s/XXXX/exec"

for i in range(5):
    requests.get(webAppsUrl   "?backteam="   str(i)   "&backodds="   backodds   "&layteam="   layteam   "&layodds="   layodds   "&advantage="   advantage)

If I could wait for each of the requests to execute, I could just leave my Web App like this:

function doGet(e) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(360000)) {
    try {
      var backteam = e.parameter.backteam;
      var backodds = e.parameter.backodds;
      var layteam = e.parameter.layteam;
      var layodds = e.parameter.layodds;
      var advantage = e.parameter.advantage;

      Utilities.sleep(30000) // I swapped the tasks for a sleep just for testing

      var second_sheet = SpreadsheetApp.openById('XXXXXXX');
      var second_sheet_page = second_sheet.getSheetByName('STACKTEST');
      var r = 1;
      while (second_sheet_page.getRange(r, 1).getValue()) {
        r  ;
      }
      second_sheet_page.getRange(r, 1, 1, 5).setValues([[backteam,backodds,layteam,layodds,advantage]]);
    } catch (e) {
      //pass
    } finally {
      lock.releaseLock();
    }
  } else {
    //pass
  }
}

As I don't want to wait for each request to finish executing (because it takes more than 30 seconds each and I don't care what happens during and after the execution), I'm creating a trigger for each request:

var RECURRING_KEY = "recurring";
var ARGUMENTS_KEY = "arguments";

function setupTriggerArguments(trigger, functionArguments, recurring) {
  var triggerUid = trigger.getUniqueId();
  var triggerData = {};
  triggerData[RECURRING_KEY] = recurring;
  triggerData[ARGUMENTS_KEY] = functionArguments;

  PropertiesService.getScriptProperties().setProperty(triggerUid, JSON.stringify(triggerData));
}

function handleTriggered(triggerUid) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(360000)) {
    var scriptProperties = PropertiesService.getScriptProperties();
    var triggerData = JSON.parse(scriptProperties.getProperty(triggerUid));

    var second_sheet = SpreadsheetApp.openById('XXXXXX');
    var second_sheet_page = second_sheet.getSheetByName('StackOverflow');
    var r = 1;
    while (second_sheet_page.getRange(r, 1).getValue()) {
      r  ;
    }
    var to_sheet = [
      [triggerData.arguments[0],triggerData.arguments[1],triggerData.arguments[2],triggerData.arguments[3],triggerData.arguments[4]]
    ];
    Utilities.sleep(30000) // I swapped the tasks for a sleep just for testing

    second_sheet_page.getRange(r, 1, to_sheet.length, to_sheet[0].length).setValues(to_sheet);
  } else {
    console.error("Timeout");
  }
  if (!triggerData[RECURRING_KEY]) {
    deleteTriggerByUid(triggerUid);
  }

  return triggerData[ARGUMENTS_KEY];
}

function deleteTriggerArguments(triggerUid) {
  PropertiesService.getScriptProperties().deleteProperty(triggerUid);
}

function deleteTriggerByUid(triggerUid) {
  if (!ScriptApp.getProjectTriggers().some(function(trigger) {
      if (trigger.getUniqueId() === triggerUid) {
        ScriptApp.deleteTrigger(trigger);
        return true;
      }

      return false;
    })) {
    console.error("Could not find trigger with id '%s'", triggerUid);
  }

  deleteTriggerArguments(triggerUid);
}

function deleteTrigger(trigger) {
  ScriptApp.deleteTrigger(trigger);
  deleteTriggerArguments(trigger.getUniqueId());
}

function doGet(e) {
  var trigger = ScriptApp.newTrigger("triggerfunct").timeBased()
    .after(1)
    .create();

  setupTriggerArguments(trigger, [e.parameter.backteam, e.parameter.backodds, e.parameter.layteam, e.parameter.layodds, e.parameter.advantage], false);
}

function triggerfunct(event) {
  var functionArguments = handleTriggered(event.triggerUid);
  console.info("Function arguments: %s", functionArguments);
}

But the current result, in addition to not respecting the sequence of the data sent, it is noticed that values are superimposed instead of being placed on different lines, even though I have placed a lock on the document so that this does not happen.

My expected result is this:

Is there a way to solve these problems?

My real need is to activate the code in GAS via requests, but I don't want to wait for the end of each execution of the entire code so that the requests has been completed.

In other words, I want to activate my code in GAS as many times as I want, generating a queue of executions if necessary, and go on with my life without worrying about how long it will take to execute.

Putting requests in the background in Python using subprocess for example I can't because it would continue to be executed and this significantly increases the monthly cost, so what I really need is a way to totally unlink the execution of the GAS with the request made.

To speed up the process I modified the code:

var second_sheet = SpreadsheetApp.openById('XXXXXXX');
var second_sheet_page = second_sheet.getSheetByName('STACKTEST');
var r = 1;
while (second_sheet_page.getRange(r, 1).getValue()) {
  r  ;
}
second_sheet_page.getRange(r, 1, 1, 5).setValues([[backteam,backodds,layteam,layodds,advantage]]);

To (Enable the Google Sheets API advanced service):

var sheet_id = 'XXXXXXX';
var sheet = SpreadsheetApp.openById(sheet_id);
var sheet_page = sheet.getSheetByName('STACKTEST');
var avals = Sheets.Spreadsheets.Values.get(sheet_id, 'STACKTEST!A1:A').values;
var r = avals.length   1;
var to_sheet = [
  [triggerData.arguments[0],triggerData.arguments[1],triggerData.arguments[2],triggerData.arguments[3],triggerData.arguments[4]]
];
sheet_page.getRange(r, 1, to_sheet.length, to_sheet[0].length).setValues(to_sheet);

Test reduce time

Using the first method when 12800 rows filled (so add the values in row 12801):

Timeout Error (exceeded the 6 minute runtime limit)

Using Google Sheets API:

804ms

Using the first method when 1000 rows filled (so add the values in row 1001):

3493ms

Using Google Sheets API:

833ms

CodePudding user response：

python

You can force a timeout.

the call may not even activate the GAS script because it will expire before the request arrives

There are two different types of timeout: Connect and read. Connect timeout waits for the first connection. Read timeout waits for the data to be sent by the server. Set the connect timeout to larger than a minute, but the read timeout to less than a few seconds.

# connect timeout to 60s(to make sure the request is actually sent) and read timeout to 1s
requests.get(webAppsUrl   "?backteam=b1", timeout=(60,1))

google-apps-script

There's rarely reason for a simple script to take 30 seconds. Your code can be optimized a lot by taking out all unnecessary getValue() calls and using arrays. See: Long processing time likely due to getValue and cell inserts

values are superimposed instead of being placed on different lines

This can be avoided by using .appendRow instead of setValues() as appendRow operation is atomic.

in addition to not respecting the sequence of the data sent

This cannot be avoided due to the async nature of the process. But, you can always rearrange the data later based on the id.

generating a queue of executions if necessary, and go on with my life without worrying about how long it will take to execute.

There's a simultaneous execution limit of 30