I have implemented a modified version of a script for generating random numbers in multiple columns of a spreadsheet, inspired by Tim Cooper's solution on stackoverflow. This script works by selecting a range of cells and running it from an onOpen menu.
My customized function, built into Google Sheets, uses the values from column A as maximum values for generating random numbers in column B. The generated numbers are then used to calculate subsequent input values. Here is a simplified example:
The function reads A1 and generates a random number between 0 and A1 (80) in B1. Subsequently, A2 is calculated by adding 10 to B1, and so on.
| A | B |
1 | 80 | 75 |
2 | 85 | 63 |
3 | 73 | 52 |
4 | 62 | 40 |
5 | 50 | 25 |
However, I am facing an issue where the spreadsheet takes some time to fill in the next input value (such as A2), leading to errors when the custom function needs the input promptly to generate the next random value.
The current custom function is a single block of code that identifies the active sheet and cell range, loops through each row to retrieve input values, calculates random values based on the specified maximums, and writes them to adjacent cells.
function generate_random_numbers()
{
var sheet = SpreadsheetApp.getActiveSheet()
var range = sheet.getActiveRange()
var counter = 0
for (var y = 1; y <= range.getHeight(); y++)
{
counter = counter + 1
if (counter != 3)
{
var maximum = range.getCell(y, 1).getValue()
var random = (maximum != 0) ? Math.floor(Math.random() * maximum) + 1: 0
}
else
{
random = ""
counter = 0
}
range.getCell(y, 2).setValue(random)
}
}
To address this delay issue, I considered splitting the function to handle value retrieval separately, checking for isNaN to confirm success and using setTimeout to pause the function for 100 milliseconds in case of failure. However, Google Apps Script does not seem to support setTimeout functionality.
If anyone has suggestions on improving the interaction between a dynamic value-based spreadsheet and a script to ensure smooth operation, I would greatly appreciate it.