Add multiple ranges in same function in google sheets

Question

I've recently started writing simple functions for google sheets, and I'd like to extend the following function to all the ranges: D10:D12, H10:H12, D16:D18, H16:H18, D22:D24, H22:H24, D28:D30, H28:H30, D34:D36 and H34:H36.

What's the syntax for what will go into the getRange parenthesis?

function nextRound() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var spreadsheet = SpreadsheetApp.getActive();
 var cell = spreadsheet.getRange("D10");
 var cellValue = cell.getValue();

  cell.setValue(cellValue - 1);
}

As this function prperly works for cell D10, but if I change D10 to D10:D12, it will place the sale value from D10 -1 in the other two cells, and that's not what I want. When I run the function, I want the new value in D10 to be D10 - 1 (as it currently works), but the new value of D11 to be D11 - 1, etc.

Oh, also, if possible, I'd like to add a second function to the button to clear the data from this same range of cells when one of the cells reaches 0 or negative numbers.

Answer 1

1. You want to add 1 to all cells of D10:D12, H10:H12, D16:D18, H16:H18, D22:D24, H22:H24, D28:D30, H28:H30, D34:D36 and H34:H36.
2. You want to clear the cell when the value of cell is less than 0.

If my understanding is correct, how about this answer? I used 2 methods of Values.batchGet() and Values.batchUpdate() of Sheets API for your situation, because in this case, I thought that the process cost of Sheets API is lower than that of getValues() and setValues() of SpreadsheetApp. So I think that there are several answers for your situation. So please think of this as just one of them.

When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

For question 1:

Please modify nextRound() as follows.

function nextRound() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheet = SpreadsheetApp.getActive();
  var id = ss.getId();
  var sheetName = spreadsheet.getSheetName();
  var ranges = ["D10:D12", "H10:H12", "D16:D18", "H16:H18", "D22:D24", "H22:H24", "D28:D30", "H28:H30", "D34:D36", "H34:H36"];
  ranges = ranges.map(function(e) {return sheetName + "!" + e});
  var r = Sheets.Spreadsheets.Values.batchGet(id, {ranges: ranges});
  var resource = {
    data: r.valueRanges.map(function(e) {return {
      range: e.range,
      values: e.values.map(function(f) {return [Number(f[0]) + 1]}),
    }}),
    valueInputOption: "USER_ENTERED",
  };
  Sheets.Spreadsheets.Values.batchUpdate(resource, id);
}

For question 2:

Please modify the script of "For question 1" as follows.

From:

values: e.values.map(function(f) {return [Number(f[0]) + 1]}),

To:

values: e.values.map(function(f) {return [Number(f[0]) <= 0 ? "" : Number(f[0])]}),

Note:

• About "For question 2", About "clear" you say, I understood that you want to delete the value. If you want to put other value, please modify it.
• If you want to run the script for "For question 2" while there are several cells with the empty value, please modify as follows.
  • values: e.values.map(function(f) {return [Number(f[0]) <= 0 ? "" : Number(f[0]) || ""]}),.

References:

• spreadsheets.values.batchGet
• spreadsheets.values.batchUpdate

If I misunderstood your question, please tell me. I would like to modify it.

Edit:

The reason of the error was due to the empty cell. So please modify as follows.

For the script of Question 1, please modify as follows.

From:

values: e.values.map(function(f) {return [Number(f[0]) + 1]}),

To:

values: e.values ? e.values.map(function(f) {return [f[0] ? Number(f[0]) + 1 : ""]}) : null,

For the script of Question 2, please modify as follows.

From:

values: e.values.map(function(f) {return [Number(f[0]) <= 0 ? "" : Number(f[0])]}),

To:

values: e.values ? e.values.map(function(f) {return [Number(f[0]) <= 0 || !f[0] ? "" : Number(f[0])]}) : null,