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?

CodePudding user response：

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

• Does Google Sheets have "Volatile" Functions like Excel?

CodePudding user response：

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.