CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cantaff0rd
Cantaff0rd

Reputation: 723

Insert new row and copy the formulas from the row above at a selected location

I have a Google spreadsheet that contains 3 tables(let's say each are 5 rows by 5 columns).

When I need to add a new row to one of the tables I just copy the last row from that table below(so I can have the formulas in the new row as well) and then re-edit it with the new values.

I would like however to have an easier solution for doing this. I want to select a row and then run a script. The script will copy the selected row below and initialize all cells with empty values.

I have googled around but I found nothing that could help me :(. I have found some scripts that are supposed to copy the last row and carry the formulas such as this one:

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

I wanted to start from that one and then try to modify the script to do what I want. However, I was not able to do so. When I create a new script and paste that code the script becomes 'unrunnable' :(. I understand that onOpen() is something called a 'trigger' which is some sort of reserved function name(or something like that). Does that have something to do with my script being unrunnable? Please explain how can I get that copied script to run like a normal script and also give me any tips in achieving what I want(adding a row with the formulas from the row above at a specific location).

Upvotes: 0

Views: 1051

Answers (1)

Cooper
Cooper

Reputation: 64062

For now since you just want to update the functionality don't worry about the onOpen and the menu just run addRow().

I rewrote it a little bit like so:

function addRow() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var lr=sh.getLastRow();
  var lc=sh.getLastColumn();
  var rg=sh.getRange(lr,1,1,lc);
  sh.insertRowsAfter(lr, 1);
  rg.copyTo(sh.getRange(lr+1,1,1,lc), {contentsOnly:false});
}

So now you have everything you need to get addRow to work. From there we can discuss further what you would like to change.

This version uses the selected row instead of the last row.

function addRow() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var lr=sh.getActiveCell().getRow();
  var lc=sh.getLastColumn();
  var rg=sh.getRange(lr,1,1,lc);
  sh.insertRowsAfter(lr, 1);
  rg.copyTo(sh.getRange(lr+1,1,1,lc), {contentsOnly:false});
}

Upvotes: 2

Related Questions