CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
R_life_R
R_life_R

Reputation: 816

How to extract Google sheet formulas in sheet with a script?

I import data into sheets from a MySQL database with a script. During this import, the other sheets with heavy formulas (vlookup, sumifs, filter) are constantly recalculating it is therefore taking forever. I want once the MySQL data is fully imported, to insert the formulas by script.

To make this process easier, I would like to extract to a file all formulas on the sheet (it is a very long sheet) and to get the extracted data to be formatted this way: Example: extract data from sheet "Summary":

For example:

cell A1 has formula "=SUM(B3:B4)"
cell A2 has formula "=C3+C5-C8"

output to file:

var sheet = SpreadsheetApp.getActive().getSheetByName('Summary');
var cell = sheet.getRange("A1");
cell.setFormula("=SUM(B3:B4)");
var cell = sheet.getRange("A2");
cell.setFormula("=C3+C5-C8)");

Thanks in advance for your suggestions.

Upvotes: 1

Views: 678

Answers (1)

alfiethecoder
alfiethecoder

Reputation: 333

You could try getting all of the formulas into an array, clearing the range, doing your processing and then reinserting the formulas:

var rng = sheet.getRange("A1:B123");
var arr = rng.getFormulas();
rng.clear({contentsOnly: true});

...Do your processing...

rng.setFormulas(arr);

Upvotes: 2

Related Questions