Is there a way to iterate a formula across cells in a multiple selection - Google sheet

0
votes

In a google sheet, this simple script works to select a single cell, then click a button to decrease the value in the selected cell by 1:

function tally() {
 
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var value = cell.getValue();
  cell.setValue(value-1);
}

Is there a way to do a multiple selection - i.e., select multiple / scattered cells in a table, then iterate the function in each of the selected cells?

1
javascriptarraysloopsgoogle-apps-scriptgoogle-sheets

1 Answers

1
votes

Explanation:

  • You can use getActiveRange and assuming that you have a 2D array, you can use double map to deduct one from each cell.
  • Then set all the values back to the sheet.

Solution:

function tally() {
  const rng = SpreadsheetApp.getActiveSheet().getActiveRange();
  const values = rng.getValues().map(r=>r.map(c=>c-1));
  rng.setValues(values);
}