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/d/1gMRQzaA-3RR6Y-BcQAvUT-zLcjntHsA4bhlIbVfkskI/edit#gid=0 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); } Connect with me: • [email protected] • spencerfarris.me • www.linkedin.com/in/spencer-farris/ • Twitter @FarrisSpencer • Google Product Expert support.google.com/docs/profile/12305