CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetscustom-function
Никита Слуцкер
Никита Слуцкер

Reputation: 5

Global Variables in Apps Script

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

Upvotes: 0

Views: 313

Answers (1)

Kristkun
Kristkun

Reputation: 5953

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.

enter image description here

enter image description here

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

Upvotes: 1

Related Questions