Copy a Range with Values and Formulas

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);
}

Leave a Reply

Your email address will not be published. Required fields are marked *