Here I walk through how to copy a range that contains both values and formulas and keep both working. This includes showing issues along the way and why different approaches don’t work.
Important notes:
• .getFormulas() gets the entire range with only the formulas populated in the output array, so there are blanks wherever a manual value.
• .getValues() will get the stored value at every instance, including a static value for every formula.
• .setValues() will output functional formulas wherever a formula is contained in the output array.
Sheet (to copy – go to File then “Make a copy”)
https://docs.google.com/spreadsheets/…
Final script:
function copyValsAndFormulas(){
const sh = SpreadsheetApp.getActive();
const ss = sh.getActiveSheet();
let r = ss.getRange(2,1,1,6);
let vToCopy = r.getValues();
let fToCopy = r.getFormulasR1C1();
let out = [[]];
for (let i in fToCopy[0]){
if(fToCopy[0][i] == “”)
out[0].push(vToCopy[0][i])
else
out[0].push(fToCopy[0][i])
}
ss.getRange(ss.getLastRow()+1,1,1,6).setValues(out);
}