Copy relative formulas in Google Sheets (Drive / Docs) using scripts
google-apps-script, google-sheets
Solution
You may want to look at copyTo(), which will copy cells in the same way as if you were copying and pasting manually in the spreadsheet (absolute and relative referencing will be respected).
Problem
I'm trying to create a script that upon opening copies formulas down to the end of the data. Obviously the formulas themselves contain both relative and absolute addressing and that will need to be maintained. IE, one formula might look like: ``` =if($C2="no", "no", VLOOKUP(concatenate($A2,$B2,$C2),'v2'!$A$2:$CN,MATCH(H$1,'v2'!$B$1:$CN$1,0)+1,FALSE)) ``` The formulas stretch across many columns and the amount of columns might also change over time. So creating a custom action for each column is not possible. Here is what I got so far: ``` var target_book = SpreadsheetApp.getActiveSpreadsheet(); var source_sheet = target_book.getSheetByName("most_recent"); var rows = source_sheet.getLastRow(); var cols = source_sheet.getLastColumn(); var formulas = source_sheet.getRange("C2:CI2").getFormulas(); source_sheet.getRange(3,3,rows -3, cols-2).setFormulas(formulas); ``` I have tried variations of the getFormulas function. 1) If I use getFormulas or getFormulasR1C1 it copies ALL formulas. It then also pastes all the formulas into each and every destination cell. 2) IF I use getFormula or getFormulaR1C1 it ends up copying the first formula from the range and pastes that into every cell in the target range.`enter code here` I'm a bit at a loss, I just want to do the most basic copy and paste, updating the relative addressing. Is that possible somehow? Thanks, Chris