CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Giuseppe Girardi
Giuseppe Girardi

Reputation: 3

Google Apps Script reads a value different from that of the cell

Since the RAND() function in Google Sheets updates every time a cell is edited, I've been trying to make a Google Apps script that reads the randomly generated value of a specific cell, to then store it in a variable for later use.

I've managed to set up the program to do so, but whenever I run it it seems to be reading a different random value than that actually displayed in the cell.

Here's the simple code:

function CopyPaste(){
  var KanaSpreadsheet = SpreadsheetApp.getActive(); 
  var KanaSheet = KanaSpreadsheet.getSheets()[0];   
  Logger.log(KanaSheet.getRange('I8').getValue());
  //cell I8 is just simply set to =RAND()
}

I'm running the code with cell I8 set to "0,4356...", but Apss Script's Log actually displays a different value of "0,6741...". The weird thing is that the value in cell I8 actually stays the same (since I'm not modifying the sheet), just as running the code multiple times in a row still gives the same value of "0,6741...". Is Google Apps Script reading on a "version of the sheet" "different" from the one that is being displayed in Google Sheets, or am I just missing something on how reading random values works?

PS: I'm new to Apps Script, but I haven't managed to find documentation online on this kind of problem.

Upvotes: 0

Views: 211

Answers (1)

doubleunary
doubleunary

Reputation: 18733

Is Google Apps Script reading on a "version of the sheet" "different" from the one that is being displayed in Google Sheets, or am I just missing something on how reading random values works?

Volatile functions such as now(), rand(), randbetween(), randarray() and coinflip() get recalculated every time any value in the spreadsheet is edited. When you have the spreadsheet open in a web browser, recalculation takes place in the web browser. Volatile results get saved to the server along with other results, but they also get immediately recalculated in the browser, and thus are "one step behind" at the server.

Google Apps Script runs on Google's servers rather than in the end device, and see the most recently saved values, which may thus at times differ from the values the user at the keyboard sees in the web browser.

You can "freeze" volatile functions so that their result is retained without recalculation like this:

=let( 
  freeze_, lambda(_, _), 
  random_, lambda(rand()), 
  freeze_(random_()) 
)

The formula uses a volatile function but its result will not change when you edit values elsewhere in the spreadsheet. Note that the values will still change if you edit the formula or any cell the formula references outside of the freeze() wrapper. The result will also get recalculated the first time you refresh the spreadsheet after editing the formula, because of the way the web browser syncs with the server.

Once the web browser has synced with the server, the value seen in Apps Script will match the value seen by the user at the keyboard.

To get an array of random numbers that stay put but can still be updated at will, Insert > Checkbox in cell K1 and use this formula in a free area of the spreadsheet:

=let( 
  freeze_, lambda(_, _), 
  refresh, K1, 
  randomNumbers_, lambda(r, c, randarray(r, c)), 
  freeze_(randomNumbers_(10, 5)) 
)

To refresh the random numbers, click the checkbox in K1. To make sure the updated values are saved to the server, edit any cell and reload the spreadsheet.

Upvotes: 2

Related Questions