Get, Modify, Set Values without breaking formulas in Google Sheet with Scripts

4
votes

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

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.

1
google-apps-scriptgoogle-sheets

1 Answers

5
votes

How about this modification?

Modification points:

  • 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.

Modified script:

Please modify as follows.

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

Reference:

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