1

Is there an API method that does the equivalent of what one might expect a method called sheet.getRange().getValuesOrFormulas() to do? That is (psuedo-code): 

for each cell in the range
  if(cell.getFormula() != '')
    cell.getFormula();
  else cell.getValue()

If not, does anyone have a custom function they'd be willing to share?

Rubén
  • 34,714
  • 9
  • 70
  • 166
skidalgo
  • 25
  • 6

1 Answers1

5

No, there's no method for that.

I don't know what you'll do with this and don't see how can it be useful (since there's no method that can set that at once). Anyway, here is the code to populate an array with formulas and values of a range.

function valuesAndFormulas() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var formulas = range.getFormulas();
  var values = range.getValues();
  var merge = new Array(formulas.length);
  for( var i in formulas ) {
    merge[i] = new Array(formulas[i].length);
    for( var j in formulas[i] )
      merge[i][j] = formulas[i][j] !== '' ? formulas[i][j] : values[i][j];
  }
  //now do something with it
  Logger.log(merge);
}
Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Passing a formula to `setValues()` sets the formula as well when I tried it. – Tharkon Jun 20 '14 at 12:36
  • 1
    It does not work flawlessly, there's multiple reports here on SO confirming that only `setFormula` works correctly. – Henrique G. Abreu Jun 21 '14 at 10:45
  • So it's unstable. But it might work so I wouldn't blatantly dismiss it, just evalutate on a case by case basis. I used your code (thank you) to collect the data and then `setValues()` to use it, which gave the desired results. – Tharkon Jun 22 '14 at 18:24
  • Well, ok. I'm glad it helped you. But a more "stable" solution would be to convert the values to formulas and use `setFormulas` instead (it's just slightly more complicated for dates). – Henrique G. Abreu Jun 23 '14 at 01:37