I'm using google apps script to construct a custom spreadsheet. In order to improve rendering speed of the script I first build an array of output values and then use setValues()
to build the spreadsheet all at once.
When I try to add formulas to into the spreadsheet with setFormulas()
it overwrites all my values as well. So now I have a case where setValues()
overwrites my formulas and setFormulas()
overwrites my values.
Is there a way I can specify a masking value for specific elements in my output array so that those elements won't overwrite data already in the spreadsheet?
Ideally I would initialize every element of the output array to this masking value, then only the non-masked elements would show up in the spreadsheet.
For anyone that finds this through Google, using setValues() for formulas now seems to work in the new sheets.
More specifically, I'm using code like this to put formulas back in after doing work on the sheet:
function setValuesAndFormulas() {
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var formulas = range.getFormulas();
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
if (formulas[i][j].charAt(0) == "=") {
values[i][j] = formulas[i][j];
}
}
}
range.setValues(values);
}
Hope that helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With