Triggers maintaining an activation sequence according to their creation and protecting spreadsheet data on every run

Question

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

@TheMaster a huge reduce, even with 12800 rows filled exceeds the time limit, since with Google Sheet API it kept more or less the same average as when there are only 1000 rows filled, below 900ms — Digital Farmer, Jul 02 '22 at 18:38
Great. There are also [benchmarks](https://gist.github.com/tanaikech/d102c9600ba12a162c667287d2f20fe4) in the tag info page, if you're interested. — TheMaster, Jul 02 '22 at 18:46

TheMaster · Accepted Answer · 2022-07-02T18:18:12.677

1

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

edited Jul 02 '22 at 18:18

answered Jul 02 '22 at 15:42

TheMaster

45,448
6
62
85

Thanks for support TheMaster , It really isn't simple code, the tasks I've hidden make it take a long time, because it accesses an API, does webscrapping, compares data and only after registering the values I sent in Python requests. About the other information, I will make these modifications to work around, but I'm still in doubt, if the ```LockService``` exists to protect from two executions colliding, why doesn't it work in this case? – Digital Farmer Jul 02 '22 at 15:50
@DigitalFarmer I can with confidence say, code like this: `while (second_sheet_page.getRange(r, 1).getValue()) { r++; }` takes unnecessarily long time. As to lockservice, maybe one entry even failed to take the lock? Could you show logs? – TheMaster Jul 02 '22 at 15:56
About the logs, I will do more tests and send as soon as possible – Digital Farmer Jul 02 '22 at 16:06
Hi again @ThaMaster , I tested some ways to find the correct row to add the values without using ```append```, because ```append``` can't specify the column to be parsed, and the fastest way if i found was using *Google Sheets API*, I added this modification to the question so I don't need to use looping and always adding after the last value even if there are blank lines between values. – Digital Farmer Jul 02 '22 at 17:57
1

@DigitalFarmer Reading the requests library, I think timeout should work. – TheMaster Jul 02 '22 at 17:59
Hmmm @TheMaster The risk of using it is this: when Google servers are slow (not uncommon), the call may not even activate the GAS script because it will expire before the request arrives. But I will do tests using it and return with information, thanks for this add! – Digital Farmer Jul 02 '22 at 18:08
1

@DigitalFarmer Check my edit. There are two different types of timeout. – TheMaster Jul 02 '22 at 18:08
Ah, now I understand the idea, using different timeouts of connect and read, amazing! – Digital Farmer Jul 02 '22 at 18:09

Triggers maintaining an activation sequence according to their creation and protecting spreadsheet data on every run

Test reduce time

1 Answers1

Linked