setFormulas won't skip empty array elements, o

问题:

Consider the code below: it sets values then sets functions across a range, however it overwrites the values that were set.

function test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("A1:B2");
  var values = [["First","row"],["Second","row"]];
  var formulas = [["=42",""],["","=77"]];
  range.setValues(values);
  range.setFormulas(formulas);
}

Why is it that setting an empty formula will overwrite any set value and vice versa? Wondering if there is a way to skip setting an individual array element. I have tried 'undefined' and 'null with no luck.

Having to get the ranges of specific areas that require formulas for values to prevent overwriting of data adds an extra layer complexity to the script I'm writing.

Thanks for reading!

回答1:

I think the problem can be largely mitigated by the fact that setValues() can be used to set formulae.

  var range = ss.getRange("A1:B2");
  var values = range.getValues();

  // process the array, eg values[0][0] = "=42";

  range.setValues();

回答2:

This certainly explains why setting values overwrites formulas, but wouldn't it be nicer if there were a way to getValuesandFormulas together into an array?

If (like me) you were distracted into getValues and getFormulas, realise that you can use copyTo between ranges to copy the overall contents!