I want to add a row to my spreadsheet but keep the formulas – and only the formulas – of the previous row. The #1 solution provided here doesn't help me because it also copies non-formula information, so I tried using .getFormulas() and .setFormulas(), as suggested in solution #2. However, if the last row used, say, some formula containing A36, the new row would still use A36 while it should be using A37. So, how can I keep the formulas from the previous row without this issue? My current code is:

var ss = SpreadsheetApp.getActiveSheet();
var lRow = ss.getLastRow();
var lCol = ss.getLastColumn();
var formulas = ss.getRange(lRow, 1, 1, lCol).getFormulas();
ss.getRange(lRow + 1, 1, 1, lCol).setFormulas(formulas);
  • any reason why not use arrayformula? – user0 Mar 15 '19 at 20:20
  • it was my old approach, but I'd prefer something that doesn't use arrayformula because I can't sort my sheet if I use arrayformula (otherwise the whole thing breaks). – Lucca Mar 15 '19 at 20:34
  • @Lucca If that's truly the only reason for dumping your old approach, and you're actually okay with arrayformula otherwise, consider using the arrayformula sheet as your data source, and in another area, tab, or file, put your analytical stuff—like sort results. This tiny hassle has helped me for various projects: simplifying formula logic, adding transparency, serving as a one-off 'backup' while I'm wanting to mess about, streamlining access control, and providing clarity in process and presentation. Just a thought. – Joel Reid Mar 16 '19 at 1:08

Use getFormulaR1C1 / getFormulasR1C1 and setFormulaR1C1 / setFormulasR1C1

The get methods

Returns the formula (R1C1 notation) for a given cell, or null if none

The set methods

Updates the formula for this range. The given formula must be in R1C1 notation.

Note: On the Google Sheets UI the formulas always are displayed using the A1 Notation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.