如何将包含值和公式的行复制到数组中？ [英] How do I copy a row with both values and formulas to an array?

问题描述

是否有一个API方法可以等同于可能期望使用 sheet.getRange（）。getValuesOrFormulas（）方法执行的方法？这就是（psuedo-code）：

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()

如果没有，有没有人有自定义函数， d是否愿意共享？

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

推荐答案

不，没有办法。

我不知道你会用这个做什么，也不知道它有什么用处（因为没有办法可以一次设置）。无论如何，这里是用公式和值范围填充数组的代码。

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

这篇关于如何将包含值和公式的行复制到数组中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！