How to automatically insert a new row and retain functions/formulas from last row?

Question

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

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?

Answer 1

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.

Code

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

Remark

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.....

Example

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

Answer 2

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.

ArrayFormula(vlookup(B4:B:tax_table!$A$2:$G$8;3;true))

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.

Answer 3

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);
    newRange.setFormulas(formulas);
}

Answer 4

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");
 cell.setFormula("=SUM(B3:B4)");

Your formula will autoincrease accordingly to the row index.

Lastly you could add an onUpdate or onEdit trigger.