google sheets app script generate 4 random number generation

Question

I have the following code, which is used to generate random numbers at range(1,1). I use triggers to generate it at intervals.

function triggerAutoRefresh() {  
    SpreadsheetApp.getActive().getSheetByName('random').getRange(1, 1).setValue(getRandomInt(1, 200));
}
// Basic Math.random() function
function getRandomInt(min, max) {
    min = Math.ceil(min);
    max = Math.floor(max);
    return Math.floor(Math.random() * (max - min + 1)) + min;
}

I now have an idea:

• After range(1,1) generates a random number, copy this number to range(1,2) after 1 minute;
• After range(1,1) generates a random number, copy this number to range(1,3) after 2 minutes;
• After range(1,1) generates a random number, copy this number to range(1,4) after 3 minutes. In this way, I can get random numbers of different time series in 4 cells. How can I modify the code to complete this function?

Currently, I can only generate a random number at intervals. So I copied the code four times and used four triggers to roughly implement my idea. But I will receive a warning: Exception: Too many simultaneous invocations. So I want to use one code and one trigger to complete it, but I have no idea now.

----Additional explanation----

As shown in the figure, the four random numbers are currently independent of each other, and are generated by 4 triggers, each triggered once every 5 minutes. My idea is to improve it and complete it with only one trigger every 5 minutes. But I can't let this random number change at the same time. As shown in the figure, suppose range(1,1) generates a random number 105 at 0:00; then at 0:01, copy 105 to range(1,2); at 0:02, copy 105 to range(1,3); and at 0:03, copy 105 to range(1,4).

Answer 1

Generate 4 Random Numbers on Code Run

Just add a for loop in your Script and use Utilities.sleep() to have a time interval.

This is based on:

I now have an idea:

• After range(1,1) generates a random number, copy this number to range(1,2) after 1 minute;
• After range(1,1) generates a random number, copy this number to range(1,3) after 2 minutes;
• After rang(1,1) generates a random number, copy this number to range(1,4) after 3 minutes. In this way, I can get random numbers of different time series in 4 cells. How can I modify the code to complete this function?

Sample Output:

Added Script:

for (let i = 1; i <= 4; i++) {
    ss.getRange(1,i).setValue(ss.getRange(1, 1).getValue());
    ss.getRange(1, 1).setValue(getRandomInt(1, 200))
    Utilities.sleep(60000);
  }
ss.getRange(1, 1).setValue(getRandomInt(1, 200));

As you can see for a loop less than equal 4 and for each iteration it generates a random number on the start and move the generated number after in every minute.

Whole Script:

function triggerAutoRefresh() {
  var ss = SpreadsheetApp.getActive().getSheetByName('random');
  for (let i = 1; i <= 4; i++) {
    ss.getRange(1, i).setValue(ss.getRange(1, 1).getValue());
    ss.getRange(1, 1).setValue(getRandomInt(1, 200))
    Utilities.sleep(60000);
  }
  ss.getRange(1, 1).setValue(getRandomInt(1, 200))
}

// Basic Math.random() function
function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

---

References:

• Utilities.sleep()