I have a table with cells that have functions/formulas, like this one:

enter image description here

I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which create a new row but it doesn't copy functions/formulas. How could I implement this formatting copy task in Google Apps Script without having to manually select and copy?

  • if my previous row formula is =HTTPResponse(C4) how i can copy same formula but new cell number for new row ? Oct 5, 2015 at 19:28

4 Answers 4


Use the code below to copy also formula's as normal values. Add the code by selecting Tools from the spreadsheet menu. Then select script editor and add the code. Make sure to press the "bug" button and authenticate the script.


// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});


Setting the contentOnly to false will yield a standard copy. Setting it to true, will paste only values. The example script you found, does way more then pasting values.....


I've created an example file for you: Add Row With Formula's

  • 1
    With a little tweak, this is just what I needed for one of my spreadsheets. Thanks. But I didn't see a way to use it from my phone, which is really where I need it most :(
    – Victoria
    Jan 5, 2020 at 0:51

This ArrayFormula can do just the same without involving a script. Enter it in D4, and it will be carried over automatically in any amount of empty cells below it.


Notes: "B4:B" means, look all the cells starting from B4 until the end of column.

While, ArrayFormula takes care of copying itself into the cells below it. Just make sure that the cells below it are empty.


In case if you need to add a new row on top (first row) and copy formula from the first top row then you'll need to copy formulas across using getFormulas() and setFormulas() functions. You can change the value of firstRow to 2 if your spreadsheet has headers for example.

function addFirstRow() {
    var firstRow = 1;
    var sh = ss.getActiveSheet();
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 1, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(firstRow, 1, 1, lCol);
  • 1
    Hi Denis, Are you referring to the post I created? I just check the script and it is still working. Regards Jacob Jan
    – Jacob Jan
    Jun 11, 2016 at 9:46
  • Hi @JacobJanTuinstra, yes you are right. I was actually trying to add new row on top and retain the formula from the same row and it didn't work because script was trying to copy data from newly created empty row. I'll update my answer to reflect this. Thank you
    – dgpro
    Jun 13, 2016 at 8:51
  • Hi @DenGordo! Thanks for this. How would I modify this if I just have values to move, and not formulas too? Thanks!
    – Drewdavid
    Feb 24, 2017 at 17:05
  • Oh PS - Where do I reference which tab I am dealing with? Thanks again.
    – Drewdavid
    Feb 24, 2017 at 17:06

To solve this problem I have used the setFormula(range), for example:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");

Your formula will autoincrease accordingly to the row index.

Lastly you could add an onUpdate or onEdit trigger.

  • -1; How is the code evoked? You're adding a sum, but you need to add a row physically. The onUpdate trigger doesn't exist.
    – Jacob Jan
    Jun 4, 2014 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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