0

Since the RAND() function in Google Sheets updates every time a cell is edited, I've been trying to make a Google Apps script which 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 wierd 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 displyed 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.

Giuseppe Girardi
  • 1
  • Does this answer your question? [How to avoid recalculation of rand() function, when using getValue/activate?](https://stackoverflow.com/questions/73500433/how-to-avoid-recalculation-of-rand-function-when-using-getvalue-activate) – Tedinoz Sep 02 '23 at 11:27
  • Try using range.getDisplayValue() – Cooper Sep 02 '23 at 11:43
  • @Tedinoz Thanks a lot, I’ll try to implement it and see if it solves the problem. The weird thing though is that re-executing the script (and thus re-opening the sheet on the server) doesn’t change the number which is showed on the Log. – Giuseppe Girardi Sep 02 '23 at 11:51
  • @Cooper it almost seems to be working, here's what's happening: - I open the spreadsheet and a random value pops up. - I run the Apps script with getDisplayValue() and a different random number is shown in Log, even though it doesn't update the spreadsheet value in any way. - I run the script again multiple times and always this same value is logged. - I reload the spreadsheet and for a split second it displays this new "different" random value that had previously been logged. - The random value is then updated since I've reopened the sheet. ... – Giuseppe Girardi Sep 02 '23 at 12:10
  • ... So it seems that running the script updates the random value, but just once. And when this happens, for some reason the spreadsheet window that I have opened doesn't "update". – Giuseppe Girardi Sep 02 '23 at 12:12
  • I'll be happy to investigate this situation further however I don't use formulas very much so if you could provide me with the exact code for a range.setFormula() then I can be assured that I am investigating the correct situation. – Cooper Sep 02 '23 at 14:06

0 Answers0