22

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 ? – user1788736 Oct 5 '15 at 19:28
19

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

3

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.

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);
}
  • 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 Tuinstra Jun 11 '16 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 '16 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 '17 at 17:05
  • Oh PS - Where do I reference which tab I am dealing with? Thanks again. – Drewdavid Feb 24 '17 at 17:06
-2

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.

  • -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 Tuinstra Jun 4 '14 at 4:31

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.