I am currently working on a script for a google spreadsheet. This script compares two sheets together and applies changes based on the differences between these sheets. First, I take the data from the sheets and put them in an array then i compare them and apply the changes before pasting them on their original sheets.
My problem is that there are formulas that fill the some cells of my sheets. However, when I run my script, it only reads the data that appears in the cells and so the formulas are deleted once the script is finished. However I'd like to keep these formulas on my sheets ..
To solve the problem I "show formulas" (Ctrl + `) before launching the script, so the script reads the formulas instead of the cells' value.
I would like to call this function "Show Formulas" (Ctrl + `) from my google script, is it possible? If yes, how?
Thank you in advance,
Victor
Yes, it is possible.
For example, instead of getValues() you can use getFormulas(), and later on, when you paste the data into the destination sheet: setFormulas()
Another possibility is two copy-paste the data into a temporary sheet with copyTo(destination, copyPasteType, transposed) chosing the copyPasteType PASTE_FORMULA
You can also use the Advanced Sheets Service and retrieve and set values with the valueRenderOption FORMULA