I have the problem of the lost references after a copy in Google Spreadsheet that has been solved in that post with the "Find and Replace" of "=". But is there anyway to do that trick in recalculation into Google Apps Script instead of Google Sheet? Indeed it is the very same issue but the thing is I do the file copy in a google apps script that is played a lot of times, so I would like to do the recalculation at the end of that same script.

I already tried two things in GAS: I simply did a getFormulas() and setFormulas() on my whole range for the concerned sheet but for the cells with no formula, only text, getFormula() returns an empty string and setFormula() overwrites it using the empty string so I lose my text.

After that I made a simple double loop which browses over all my cells and where I manually perform a test on the getFormula() return, and I do the setFormula() only when the string is not empty. That works fine but since I have to perform a getRange() on each single cell this is very, very, very long to perform (I have thousands of cells, it needs several minutes). When I see how the manual "Find and Replace" is fast to perform, that is frustrating.

I also tried to record a macro doing manually the "Find and Replace" to see what code will be recorded but the macro remains empty.

Do you know how I could do that "Find in Replace" in Google Apps Script (or any other workaround)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.