Setting setFormulas() will clear the existing data

问题:

I want to keep the cell data as it is for those cells which have no formula applied.

The code snippet explains the function.

this solution too overwrites the values, setFormulas won't skip empty array elements, over-writes values

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var Formulas = range.getFormulas();


for (var i in Formulas) {
    for (var j in Formulas[i]) {

        if (Formulas[i][j] == "") //cells which dont have the formula
        {
            //dont apply any formula, but keep the existing cell data (as it is)
        } else {
            Formulas[i][j] = '=HYPERLINK("http://www.google.com","Google")'
        }
    }
}

range.setFormulas(Formulas);

回答1:

"copyTo()" can copy cells with values, formulas and format. https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)

Sample script is as follows.

Script :

var range = ss.getRange('a1:a8');
var values = range.getValues();
var formulas = range.getFormulas();

var destrange = ss.getRange('b1:b8');
range.copyTo(destrange);

var destvalues = destrange.getValues();
var destformulas = destrange.getFormulas();

values :

[[1.0], [5.0], [3.0], [9.0], [1.0], [2.0], [3.0], [24.0]]

formulas :

[[], [], [], [=sum(A1:A3)], [], [], [], [=sum(A1:A7)]]

destvalues :

[[1.0], [5.0], [3.0], [9.0], [1.0], [2.0], [3.0], [24.0]]

destformulas :

[[], [], [], [=sum(B1:B3)], [], [], [], [=sum(B1:B7)]]

If this will be helpful for you, I'm glad.