Write static values and formulas with one call to a spreadsheet

Question

I have a spreadsheet with a huge range: A2-AJ1900. Some columns have static values and some have formulas and some have mixed, so sometimes a static value and sometimes a formula. I want to update every row with a script and to save time I want to build one array with all values and formulas and then I want to write that array to the spreadsheet with one call. Is that possible? Or must I split that array to static value and formula columns? I think that would not be a huge problem, but how should I handle the mixed columns then? It would be very complicated and the script would need way too long if I update every cell with one call. Isn't there a way for such cases to make it better?

Answer 1

One call to setValues is enough, because it also sets formulas if given a formula as a string, like "=A2+A3". Example:

var range = SpreadsheetApp.getActiveSheet().getRange("E2:E7");
range.setValues([[4], [5], ["=E2*E3"], ["=SUM(E2:E4)"], ["text"], ["=E6 & E5"]]);

Using separate calls, one for setValues and one for setFormulas, will be problematic because one will over-write the other. You would have to use smaller ranges (likely, multiple ranges) for the second call, fitting them in the gaps between the results of the previous call. This is cumbersome and inefficient.