Prevent a function in Google App Scripts from updating on a cell edit

Question

I am trying to sort a list of 32 names in column A randomly to column B, I found this formula to do this:

=sort('Sheet1'!A2:A33,arrayFormula(randbetween(sign(row('Sheet1'!A2:A33)),100)),true)

This works great, but I then want to be able to use the new/random list in column B to use in other formulas. The problem is that any time you update any cell (even on another sheet) the list re-randomizes, preventing me from using any of the cells from the random list.

So I tried to take the formula out of the cell it was in and putting the formula in a menu option, thinking it would prevent the sheet/cell from updating with this Apps Script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Menu1', 'menuOption')
      .addToUi();
}

function menuOption() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("B2");
  cell.setFormula("=sort('Sheet1'!A2:A32,arrayFormula(randbetween(sign(row('Sheet1'!A2:A33)),100)),true)");
}

But it still re-randomizes the list in column B whenever a cell is edited. Is it possible to call a formula or App Script function only once, when ran?

Thanks,

If you only need to do it once consider copying your randomized names and pasting values only. — iMarketingGuy

Hink Hink · Accepted Answer · 2018-01-12T08:16:10

Formulas inside cells update with every edit, for the problem you are having you must be calling it within a cell somewhere on the spreadsheet.

You need to remove this call and only run it via the scripts interface or the menu button.

or

If you don't mind wasting google server resources set a boolean value in a cell somewhere on your spreadsheet and check the value before you run the sorting aspect of the script.

Prevent a function in Google App Scripts from updating on a cell edit

1 Answers