0

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
Никита Слуцкер
  • 5
  • 1
  • It could be that PropertiesService is not available to custom functions. [ref](https://developers.google.com/apps-script/guides/sheets/functions?hl=en#advanced) – Cooper Apr 05 '21 at 16:51
  • I'm not sure how you dragged your url data if it is referenced to other cell but when I tried this using =pstatTest(A1:A5), all data in A1:A5 were added successfully. But if you used your formula in this way =pstatTest("www.sample.com?id="&A1). Then you might want to use a different approach in saving your url's into your global variable. I would suggest create a custom menu that will ask for input ranges. Then process that range create an array of url then save that into your global variable on a 1 time write procedure – Ron M Apr 05 '21 at 18:16
  • Just let me know if you want to explore the suggested custom menu approach so I could give some example. I would appreciate it as well if you could share a sample sheet for reference. [Share a test sheet](https://webapps.stackexchange.com/a/138383) – Ron M Apr 05 '21 at 18:22
  • If you enter a range of cells, then everything should work fine, but I need a case when the user enters a custom function, and then extends it to other cells – Никита Слуцкер Apr 05 '21 at 19:07
  • @RonM If you enter a range of cells, then everything should work fine, but I need a case when the user enters a custom function, and then extends it to other cells – Никита Слуцкер Apr 05 '21 at 19:10

1 Answers1

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.

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.
Ron M
  • 5,791
  • 1
  • 4
  • 16
  • Glad to help. By the way, If we answered your question, please click the accept button. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [How to accept answer](https://meta.stackexchange.com/a/5235) – Ron M Apr 06 '21 at 14:56