4

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.

  • Google Apps Script has built-in algorithms to optimize the spreadsheet updates, actually there is a command to send the pending changes to the spreadsheet: SpreadsheetApp.flush(). Please add a minimal complete example, including a demo spreadsheet and script, to help us to understand your scenario. – Rubén Oct 27 '19 at 22:57
5

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 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);

}
| improve this 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 enter image description here

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.

| improve this answer | |
  • 1
    This will be a valuable answer for youself and future users. Code answers are OK but in this case there's no direct relationship to the question, so we don't know the characteristics of the function attributes, nor what you're setting out to do, and how and why. Remember, your answer will be read by people in the future so you need to add a description so that they can understand this solution. – Tedinoz Oct 30 '19 at 5:02
  • @Tedinoz thanks! ok I added some more notes on it. – toddmo Oct 30 '19 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.