CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
skidalgo
skidalgo

Reputation: 19

How do I copy a row with both values and formulas to an array?

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?

Upvotes: 1

Views: 1751

Answers (1)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17772

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

Upvotes: 5

Related Questions