Google-sheets – ny way to set different formulas for multiple ranges all at once

formulasgoogle sheetsgoogle-apps-script

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good answer for that.

Now I'd like to set multiple different formulas across dis-contiguous cells all at once, but there's an issue. Unlike setting values, the array containing the formulas replaces the values in and wipes out the whole target range.

My test data:

 | B  |   C    |  D  |
----------------------
2|   2|       5|    8|
----------------------
3|   3|       6|    9|
----------------------
4|   4|       7|   10|
----------------------
5|   9|kitty   |puppy|
----------------------
6|fawn|duckling|joey |

Here's my test code:

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

  function testSetFormulas() {
    var cell = testSheet.getRange("B5:D6");
    var formulas = cell.getFormulas();

    formulas[0][0] = "=SUM(B2:B4)";
    formulas[1][1] = "=AVERAGE(C2:C4)";
    cell.setFormulas(formulas);    

  }

If I put the string (not a formula) kitty in cell C5, this code blows that away.

I just want to set the formulas for B5 and C6 with no side effects.

I tried setting array member for C5 to the value "kitty". I also tried null and undefined and "". 

formulas[0][1] = "kitty";
formulas[0][1] = null;
formulas[0][1] = undefined;
formulas[0][1] = "";

Each approach still blows away the contents of C5 if C5 contains just a value, not a formula. (It does respect existing formulas)

Is there a way to write multiple formulas at once without replacing existing values in cells that don't need formulas that I want to leave alone?

Best Answer

I did find a solution to this. In my previous question Is there any way to set different values for multiple ranges all at once?, I found a bug in my own answer. I was able to fix it using the Google Sheets API.

As it turns out, this exact function can now also be used to set values and formulas interchangeably without blowing away existing values or formulas:

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

  // here's the Google Sheets API calls
  var valueRange = Sheets.newValueRange();
  valueRange.values = spanRangeValues;
  var result = Sheets.Spreadsheets.Values.update(
    valueRange, 
    spreadSheetId, 
    sheet.getName() + '!' + spanRange.getA1Notation(), 
    {
      valueInputOption: 'USER_ENTERED'
    }
  );
}
Related Topic