Global Variables in Apps Script

Question

Get code from this link Global variables in Google Script (spreadsheet)

I create a custom function and there I declare a variable using PropertiesService

Globals.init('queue', []);

When i try to stretch out my custom function for example on 10 rows

function pstatTest(url){

  Globals.init('queue', []);

  counter.push(url);

  Globals.flush();  

  return "Loading...";

}

Not all data is written to a variable declared earlier

As I suppose, this is due to the fact that the writing is asynchronous and the return in the function is called faster than writing.

Please tell me how to fix this code so that when pulling into a global variable, all data from the cells is written

Answer 1

You can use LockService to prevent concurrent access to sections of code.

Sample Code:

/**
 * @customfunction
 */
function pstatTest(url){
  const lock = LockService.getScriptLock();
  var ret; 
  lock.tryLock(2000);

  if(lock.hasLock()){
    Globals.init('queue', []);
    queue.push(url);
    Globals.flush();  
    Logger.log("after: "+queue);
    ret = "Loading...";
    lock.releaseLock();
  }else{
    ret = "Currently locked, try again"
  }
  
  return ret;
}

Output:

I dragged the custom function from B1 up to B10.

• the result was not sorted based on the arrangement in the sheet, but all data were added successfully in the global variable.