I am trying to create a Google Apps Script that will copy the formulas and values from a source sheet and paste them onto a destination sheet.
The method I'm using to get the formula and values is to just use both getFormulas() and getValues(), and then just merge the arrays to take the formula if one exists, or use the value if there is no formula.
This works for the most part, however, it breaks with ArrayFormulas, since it copies the formula for the cell itself, but will also copy the values from the ArrayFormula spill, which then prevents the ArrayFormula from expanding as it is blocked by the static values.
Is there a way to use getValues or something similar, to only get values which have been entered, and are not the result of an array elsewhere?
I tried looking if the Advanced Sheets Service had anything I could use but couldn't find anything that helps. I really want to avoid having to parse the formulas and find whether or not it's and ArrayFormula, extract the ranges that it spills into, and delete those spill values from the array created from getFormulas() and getValues() as this would slow down the script to the point it would likely be unusable.
Use ValueRenderOption as FORMULA in Advanced Sheets service to get a mix of formulas and values. This option excludes values that are spilled from array formulas, but provides values, which are manually filled.