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

1
votes

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":

  • loop through each cell in the sheet
  • read the formula inside of cell
  • write it to file using the coding structure below for easier later use

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.

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

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