How do I copy a row with both values and formulas

问题:

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?

回答1:

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