Firstly, I apologise if I am asking a silly question as I am a javascript novice and fairly new to understanding and handling arrays.
I need a bit of help figuring out how to get and set formulas in App Script while converting them to text strings in-between. For example:
I have 3 sheets, Sheet 1 contains source formulas, Sheet 2 that is supposed to store the source formulas as strings (acting as a database), Sheet 3 where I’m supposed to set formulas that I get from Sheet 2 .
Sheet1!A1 contains formula “=B1*C1”
Sheet1!A2 contains formula “=B2*C2”
etc.
Sheet2!A1:A is blank.
Sheet3!A1:A is blank.
What I need to achieve:
Task A. I would like to get all formulas from Sheet1!A1:A with the getFormulas() method and then store them as a text string in Sheet2!A1:A with setValues() method but I have no idea how to convert the array generated by getFormulas() into strings that I can then store with setValues(). Something like this:
var formulas = s.getRange('Sheet1!A1:A').getFormulas();
s.getRange('Sheet2!A1:A').setValues(formulas.toString());
Problem is of course that I get a method signature mismatch if I try to use toString().
Task B. After storing the formula strings in Sheet2!A1:A, I would like to get them with getValues() then set them as formulas in Sheet3!A1:A with setFormulas().
var text_formulas = s.getRange('Sheet2!A1:A').getValues();
s.getRange('Sheet3!A1:A').setFormulas(text_formulas);
Any idea how I could go about achieving this in an efficient manner? Thank you in advance for helping out a novice!