Custom google sheets function that auto updates when change is made wont change when others edit

Question

I have a custom function that auto updates when a change is made on the google spreadsheet, but when some other users edits the sheet the function doesn't work for them, how can i make it update for anyone that uses the sheet.

The function auto updates using this:

function onEdit(e) {
  SpreadsheetApp.getActiveSheet().getRange('Z1').setValue(Math.random());
}

Then passing the parameter of Z1.

Answer 1

Explanation:

Just a couple of notes to make sure we are on the same page.

• This is not a custom function but an onEdit trigger function.

• As the name suggests it is triggered when user changes the value of any cell in a spreadsheet.

• The function is only triggered when the change of the cell is made by a user and not by formulas or scripts.

Potential Issues:

1. Make sure the other users have edit permissions on the target cell. If they are not allowed to edit cell Z1 in the activeSheet which might be any sheet in the spreadsheet file, then the script won't work for them. You can either give them access, or make the script work only under particular sheets when you can give them access.

2. Some users sometimes have experienced issues when tried to chain with active methods directly. See reference links here and here although I can't reproduce that issue, it might be the cause in your case.

Potential Solution:

Please take care of the first issue.

Regarding the second issue, modify your code as follows:

function onEdit(e) {
  const active_sheet = e.range.getSheet();
  active_sheet.getRange('Z1').setValue(Math.random());
}

This is anyway the recommended way to use an onEdit trigger as you should take advantage of the event object.