javascriptgoogle-sheetsgoogle-apps-scriptcustom-function

How to refresh random JS variables in Google Sheets


I have a script in Google Sheets that generates a random Hiragana character and it works fine, but it doesn't refresh the random unless I rewrite the function. Is there a way to refresh random JavaScript elements without having to rewrite the function or putting it back in the sheet?

Here is my code:

function RandomVowel() {
  var array = ["あ","い","う","え","お"];
  var RandomValue = array[Math.floor(Math.random() * array.length)];
  return RandomValue
}

Solution

  • In order to be able to execute the installable time trigger you mentioned every minute you should execute the function in the cell you want to use from your script instead of calling the function in the sheet as a formula.

    To achieve what you are aiming for you must first create a time based installable trigger (in your script IDE go to Edit -> Current project's triggers and create a new trigger with your function name, time based and to run it every minute) and then run the following self explained piece of code:

    function RandomVowel() {
      var array = ["あ","い","う","え","お"];
      var RandomValue = array[Math.floor(Math.random() * array.length)];
      
      // Get the sheet you want your characters to be shown
      var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
      
      // Choose the cell you want to set to these random values every minute
      sheet.getRange('A1').setValue(RandomValue);
      // For a range of cells
      // sheet.getRange('B1:C3').setValues(RandomValue);
    }
    

    To achieve this in a range, you must first wrap the part where we set the values into two for loops (one for rows and one for columns) to make sure that in every single iteration the random value will be different (as oppossed to just set the variable to the whole range which would cause all the cells to have the same value).

    In the following example we are setting the first 10 rows and 2 columns to these random values (i.e from A1:B10):

    function RandomVowel() {
      var array = ["あ","い","う","え","お"];
      
      var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
      
      // Sets the first 10 rows of the first 2 column to random values (i.e A1:B10)
      for(j=0;j<2;j++){ // for the columns
        for(i=0;i<10;i++){ // for the rows
          var RandomValue = array[Math.floor(Math.random() * array.length)];
          sheet.getRange(i+1,j+1).setValue(RandomValue);
        }
      }
    }