使用脚本获取，修改，设置值而不会破坏Google表格中的公式 [英] Get, Modify, Set Values without breaking formulas in Google Sheet with Scripts

问题描述

我喜欢使用脚本读取Google表格中的一系列数据，然后修改数据，然后将其写回.

I like to read a range of data in Google sheet using scripts, modify the data and then write it back.

var range = sheet.getDataRange();
var values = range.getValues();
// modify the values
range.setValues(values);

到目前为止，还不错，但是我的工作表中还包含一些公式，这些公式现在已被固定值取代，因此修改了我的代码:

So far so good, but my sheet also contains formulas which now are replaced with fixed values so modified my code:

var range = sheet.getDataRange();
var values = range.getValues();
var formulas = range.getFormulas();
// modify the values
range.setValues(values);
range.setFormulas(formulas);

但是现在我所有的数据都在setFormulas()上清除了，我正在努力很好地解决此问题.我采用这种方法的主要原因是要使脚本快速运行，因为工作表中有很多数据.

But now all my data gets cleared on setFormulas() and I'm struggling to solve this problem nicely. The main reason for my approach is to have the script run fast, as there is lots of data in the sheet.

即我只修改了一些数据，但希望保留公式.

I.e. I only modify some data, but wish to keep the formulas.

推荐答案

此修改如何?

• 根据您的情况，首先运行range.setValues(values).将值放入单元格中.然后，当运行range.setFormulas(formulas)时，formulas数组中的任何值也不会放入单元格中.这样，将清除具有值的单元格.
  • 为了避免这种情况，作为一种方法，它创建了一个组合了values和formulas的数组.然后将数组放入单元格.
  • In your situation, at first, range.setValues(values) is run. The values are put in the cells. Then, when range.setFormulas(formulas) is run, no values in the array of formulas are also put in the cells. By this, the cells with values are cleared.
    • In order to avoid this, as a method, it creates an array which combined values and formulas. And it puts the array to the cells.

    当以上几点反映到您的脚本中时，它如下所示.我认为针对您的情况有几种解决方案.因此，请仅考虑其中之一.

    When above points are reflected to your script, it become as follows. I think that there are several solutions for your situation. So please think of this as just one of them.

    请进行如下修改.

    range.setValues(values);
    range.setFormulas(formulas);
    

    到:

    var data = values.map(function(row, i) {
      return row.map(function(col, j) {
        return formulas[i][j] || col;
      });
    });
    range.setValues(data);
    

    参考:

    • map()

    Reference:

    • map()

    如果我误解了您的问题，请告诉我.我想修改它.

    If I misunderstood your question, please tell me. I would like to modify it.

    这篇关于使用脚本获取，修改，设置值而不会破坏Google表格中的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！