Is there a way to call a custom function every couple of seconds in Google Apps Script?

Question

I'm trying to make a silly but simple little program in google apps script and sheets where it picks a random dad joke to show you every couple of seconds. I tried using setInterval(), but I found out that it isn't included in google apps script. Any suggestions?

code:

function LOL() {
  let messageList = ["Where do dads keep their jokes? In a dad-abase!","When does a joke become a dad joke? When it becomes a-parent!","Two men walk into a bar. You'd think the second one would've noticed!","Does your face hurt? 'Cause it's killing me!"]
  function randInt() {
    let listLength = messageList.length
    let random = Math.floor(Math.random() * listLength);
    return random
  }
  function showMessage() {
    let int = randInt()
    console.log(int)
    return messageList[int]
  }
  return showMessage()
}

It would choose a random message from my list every minute to put in whatever cell has =LOL().

Could you show us your code and the desired output? – Nikko J. Oct 27 '21 at 15:13 — Nikko J., Oct 27 '21 at 15:13
@NikkoJ. I added the code into it. – EnderCodesLol Oct 27 '21 at 16:03 — EnderCodesLol, Oct 27 '21 at 16:03

Nikko J. · Accepted Answer · 2021-10-27T17:53:23.163

Here I found a solution by Tanaike that will update a custom function by using a TextFinder and combined it with Time driven trigger to automatically refresh it every minute.

Try this:

Code:

function LOL(){
  let messageList = ["Where do dads keep their jokes? In a dad-abase!","When does a joke become a dad joke? When it becomes a-parent!","Two men walk into a bar. You'd think the second one would've noticed!","Does your face hurt? 'Cause it's killing me!"]
  let listLength = messageList.length
  let random = Math.floor(Math.random() * listLength);
  return messageList[random];
}

function refresher() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const formula = "=LOL";
  sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith("Loading");
  sheet.createTextFinder("Loading").matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
}

Installable Trigger Setup:

Output:

This works, but every so often when it runs it will take significantly long to load the cells. What is with this? — EnderCodesLol, Oct 27 '21 at 18:09
@EnderCodesLol - This is the downside of using Custom function. Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server and executing it multiple times within a small period of time could result to a slow loading. What you can do is to change the interval to 5 minutes or try this quick fix by [TheMaster](https://stackoverflow.com/a/63167066/14606052) — Nikko J., Oct 27 '21 at 20:13

score 0 · Answer 2 · answered Oct 27 '21 at 18:07

You can use the native Utilities.sleep from Serge's Answer, although I would recommend using a Trigger or async functions if you need to do anything else in the script.

Here's a quick example of implementation:

function myFunction() {
  var delayInMilliseconds = 5000; //1 second
  while (true){
    Utilities.sleep(delayInMilliseconds)
    showMessage()
  }
}

score -1 · Answer 3 · answered Oct 27 '21 at 14:47

-1

You can manage Time-driven triggers manually, e.g. run a particular function everyHour(). See here

answered Oct 27 '21 at 14:47

Neven Subotic

1,399
1
6
18

Is there a way to call a custom function every couple of seconds in Google Apps Script?

3 Answers3