Is there any way to set different values for multiple ranges all at once?

Question

Is there any way via script to put "Apple" in cell A1 and "Banana" in cell B4 simultaneously?

The motivation to do so would be that the sheet would only recalculate one time. Currently it re-calculates twice, when I put each of the two values into their ranges via setValue.

I know I can do it if the range is contiguous, with setValues(["Apple","Banana"]), but unfortunately these destinations can't be made contiguous.

I also know that if I make a RangeList out of A1 and B4, I can do SetValue("Apple") on my RangeList and have "Apple" immediately go into both cells, but unfortunately, I need Apple and Banana, different values.

If I try SetValue(["Apple","Banana"]) on my RangeList, it just uses Apple and ignores Banana.

I also looked for spreadsheet.SuspendCalculation(), but unfortunately, that does not exist.

Answer 1

You want to update the value in Cells A1 and B4 at the same time.

The solution is to update the values as an array.

In this script:

• var values = range.getValues() - gets the entire range A1:B4
• values[0][0]= "Apple" and values[3][1] = "Banana" - update the array for the equivalent of cells A1 and B4 respectively.
• range.setValues(values) - updates the entire range with the updated array. The two modified cells are updated.

---

function wa135847() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "wa_135847";
  var sheet = ss.getSheetByName(sheetname);
  
  // define the range
  var range = sheet.getRange(1,1,4,2);
  
  // get the array values
  var values = range.getValues();
  // Logger.log("DEBUG: A1 = "+values[0][0]+", B4 = "+values[3][1]);
  
  // adjust some array values
  values[0][0]= "Apple" ; 
  values[3][1] = "Banana";
  
  // update the range for the adjusted array values
  range.setValues(values);
  
}

Answer 2

Based off the accepted answer I created utility functions to work with ranges (and also named ranges since that's how I'm specifying the cells):

This function setNamedRangeValues takes the sheet containing the named ranges, an array of strings which are the names of the ranges, and an array of values that you want to put into each range. The rangeName and values arrays are a 1 to 1 correspondence so they need to be the same size.

The range names can be anywhere on the sheet and the function will place the values into all the named ranges all at once, thus speeding up scripts.

This function setRangeValues takes an array of ranges and an array of values that you want to put into each range.

The ranges must be on the same sheet, and the two arrays must be the same size.

Update

The initial code did have a bug. It would blow away any formulas within the union (combined area) of the ranges. So I have updated with a better solution, using Sheets API, that does not do that

Setting up for using API

1. Go to https://console.developers.google.com/apis/credentials
2. Create API Key for everything or just for Google Sheets
3. In your script project, go to Resources, Advanced Google Services, and turn on Google Sheets API

Range.gs

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadSheetId = spreadSheet.getId();

function setNamedRangeValues(sheet, rangeNames, values) {

  // get the ranges
  var ranges = rangeNames.map(function(rangeName){
    return sheet.getRange(rangeName);
  });

  setRangeValues(ranges, values);
}

function setRangeValues(ranges, values) {
  // find the range that encompassess all the named ranges
  var minRow = Number.MAX_VALUE;
  var maxRow = 0;
  var minColumn = Number.MAX_VALUE;
  var maxColumn = 0;

  ranges.forEach(function(range){
    var row = range.getRow();
    var column = range.getColumn();
    minRow = Math.min(minRow, row);
    maxRow = Math.max(maxRow, row);
    minColumn = Math.min(minColumn, column);
    maxColumn = Math.max(minColumn, column);
  });

  var sheet = ranges[0].getSheet();
  var spanRange = sheet.getRange(minRow, minColumn, maxRow - minRow + 1, maxColumn - minColumn + 1);

  // get the array values
  var spanRangeValues = spanRange.getValues()
  .map(function(rowValues) {
    return rowValues
    .map(function(value) {
      return undefined;
    });
  })

  // adjust some array values
  var index = 0;
  values.forEach(function(value){
    spanRangeValues[ranges[index].getRow() - spanRange.getRow()][ranges[index].getColumn() - spanRange.getColumn()] = value;
    index += 1;
  });

  var valueRange = Sheets.newValueRange();
  valueRange.values = spanRangeValues;
  var result = Sheets.Spreadsheets.Values.update(
    valueRange, 
    spreadSheetId, 
    sheet.getName() + '!' + spanRange.getA1Notation(), 
    {
      valueInputOption: 'USER_ENTERED'
    }
  );
}

Range.Tests.gs

var rangeTests = RangeTests();

function testSetRangeValues(){
  rangeTests.testSetRangeValues();
}

function testSetNamedRangeValues(){
  rangeTests.testSetNamedRangeValues();
}

function RangeTests() {

  var testSheet = spreadSheet.getSheetByName("Tests");

  function testSetRangeValues() {

    var ranges = [
      testSheet.getRange("A1"),
      testSheet.getRange("B7"),
      testSheet.getRange("B4"),
      testSheet.getRange("G2"),
    ];
    var values = [
      "alpha one",
      "beta seven",
      "beta four",
      "gamma two",
    ];
    setRangeValues(ranges, values);
  }

  function testSetNamedRangeValues(){
    var rangeNames = [
      "TestAlphaOne",
      "TestBetaSeven",
      "TestBetaFour",
      "TestGammaTwo"
    ];
    var values = [
      "alpha one",
      "beta seven",
      "beta four",
      "gamma two",
    ];
   setNamedRangeValues(testSheet, rangeNames, values);
  }

  return {
    testSetRangeValues: testSetRangeValues,
      testSetNamedRangeValues: testSetNamedRangeValues
  };

}

I'll edit it if I find any bugs.