CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tonyneedshelp
Tonyneedshelp

Reputation: 31

google script for inserting a row and copying formulas down

I'm new here and struggling with the wonderful world of google script at the mo so could realy do with your help.

I have a google sheets doc and columns c to z all contain formulas that I have copied down the whole sheet,

the problem is I need to be able to insert a row and have the formula in the row above it copy down. I found a code online that will add a new last row and copy the formulas but I need one that I can select a row, click the script and it insert a new rowwith the formulas copied down,

can anyone give me help on the script I need to do this?

here is the script I have which does exactly what I want but for the last row not the selected row.

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

if someone can tel me how to fix this I would be very greatful

Thanks

Tony

Upvotes: 2

Views: 5104

Answers (2)

Excelsson
Excelsson

Reputation: 195

I've modified your script since it has a few variables without being properly declared.

function addaRow() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lRow = sh.getActiveCell().getRow(); 
  var lCol = sh.getLastColumn();
  var range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

Upvotes: 1

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

Code:

Instead of sh.getLastRow() use sh.getActiveCell().getRow()

Upvotes: 1

Related Questions