0

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?

  • You don't explain how to identify the cells that are to remain, but let's assume that you have a method for that. My off-the-cuff idea would be to capture the value, row and column of each of those target cells, and write the details to temporary array#1; then run the setformulas script; then getValues for the data range and assign to a temporary array#2; then use the parameters from the temporary array#1 to update the relevant values in the temporary array#2; then setValues using the updated temporary array#2. – Tedinoz Nov 14 '19 at 11:07
  • @Tedinoz yeah I think I understand. Basically back up the values first, then restore them. Thanks! – toddmo Nov 14 '19 at 22:41
  • Well, that's the theory. Should be easy enough to test. Do you need a hand? – Tedinoz Nov 14 '19 at 22:53
  • @Tedinoz, I have added an answer for a direct single step write that leaves existing values / formulas alone. Check it out! – toddmo Nov 24 '19 at 1:07
0

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'
    }
  );
}
  • Congrats and I'm 100% in favour of providing an answer to your own question. In this case, it's a code-only answer (which have their own limitations) that relates to your project. Future users have only your question for context. I suggest that you might develop a smaller, more focused answer that addresses the issues raised in your question (and about which there was some ambiguity, such as "You don't explain how to identify the cells that are to remain") i.e. spell out exactly what you are trying to achieve and how the code resolves the problem). – Tedinoz Nov 24 '19 at 2:21

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.