0

I have a google spreadsheet and a randomly selected row is always shown in row 3:

A3:

=round(rand() * 808 + 1)

B3:

=VLOOKUP(A3;A8:B815;2)

When i click on a button, i want the formula to reload and also jump to that row, so i made a macro:

function Refresh() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').setValue("");
  SpreadsheetApp.flush();
  spreadsheet.getRange("A7").offset(spreadsheet.getRange('A3').getValue(),0).activate()
};

The reason, that i didnt put the calculation of random into the macro is, that i also want this random row in spreadsheets where macros are disabled (mobile + users who dont know).

When i run the macro, a new value is calculated in A3 (from flush) and then the corresponding row is activated. The issue is, that with the last row of the macro the value of A3 is recalculated because something changed and then the number does not match up anymore with the activated row.

Is there a way to block calculations? Or to scroll to the row without activating recalculations?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
terrafox
  • 54
  • 6
  • Tried removing `flush()`? The `getValue()` before `activate()` should activate the flush automatically. That'll make random calculate once and exactly once. – TheMaster Aug 26 '22 at 12:37
  • 1
    I just tried but it doesn't change the behavior. What's really wierd is: ``` function Refresh() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('A1').setValue(""); selected = spreadsheet.getRange('A3').getValue() console.log(selected) spreadsheet.getRange("A7").offset(selected,0).activate() console.log(spreadsheet.getRange('A3').getValue()) }; ``` Loggs the same value twice but its doesn't have anything to do with the value i see in A3... – terrafox Aug 26 '22 at 15:26
  • 1
    Could you share the sample sheet you are working on? Whenever possible, try to include a [minimal example](https://stackoverflow.com/help/minimal-reproducible-example) that reproduces the issue. – Gabriel Carballo Aug 26 '22 at 16:42
  • 1
    Do note that [your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Aug 26 '22 at 19:24
  • 1
    @GabrielCarballo Doesn't the already provided example reproduce the issue? – TheMaster Aug 27 '22 at 07:48

2 Answers2

3

Tl;Dr: It's not possible to avoid the recalculation of RAND() or any other Google Sheets volatile function (NOW(), TODAY(), RANDBETWEEN()).

RAND() is a volatile function and this kind of function are recalculated every time that the spreadsheet is opened, an edit is made and optionally every certain time set in the spreadsheet settings. Also it's recalculated on the Google servers when the spreadsheet is get / opened in Google Apps Script.

You might try to use an on edit trigger instead of a "macro". Simple on edit triggers doesn't require authorization to run, installable triggers require authorization of the user who is installing the trigger, so they will be triggered by any edit made by any user but not when a macro or script are executed.

Below is an example of a simple on edit trigger. It sets the value of Sheet1!A1 to a random number.

function onEdit(e){ 
 const value = Math.random();
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 spreadsheet.getRange('Sheet1!A1').setValue(value);
}

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
2

In addition to Ruben's answer, I want to add

  1. Some macros like onEdit do work on mobile apps
  2. Nevertheless, activate() doesn't work on mobile apps.
  3. During testing, I can also see that the random numbers shown in mobile apps are completely different from what's shown on desktop for the same cell with the same formula. This number is also completely different from what is logged in apps script. This shows they're not synced properly.
TheMaster
  • 45,448
  • 6
  • 62
  • 85