I am attempting to script a button, that when pressed, will create a new row below the currently active cell and copy all of the formats and formulas from the row of the cell that is selected.

I was able to find a version of this question that uses getLastRow. This results in a row being added to the end of my spreadsheet instead of below my active row.

I have attempted the following which does not use the getLastRow function. Hopefully this makes my intention clear, I'm just not sure how to make it work properly.

function addRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = ss.getActiveRange();
var R = range.getRowIndex();

sheet.insertRowsAfter( R, 1 );
sheet.copyTo(sheet.insertRowsAfter( R, 1 ), {contentsOnly:false});
}

Here is the other code I found that only adds to the last row:

var ss = SpreadsheetApp.getActive();

function onOpen() {      
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("AM FUNCTIONS", 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);
  var formulas = range.getFormulas();
  var newRange = sh.getRange(lRow+1,1,1,lCol);
  newRange.setFormulas(formulas);
}
  • It sounds like you are trying to use a script where use of array formulas would actually work better. The only way to know is if you share a link to your sheet or a copy of it here (File > Share), being sure to select "Anyone with the link can Edit" when you generate the link. – Erik Tyler Nov 29 at 17:10
  • Unfortunately, I can't share the link or a copy of it due to my organization's sharing policy. If it's helpful, I have formulas and formatting on row 5 and 9. I would like a button, which if I have one of the cells in row 5 selected, it adds a row after 5 and copies the formulas and format from row 5 into the new row 6. Again, I would like to be able to do the same for the formulas/format on row 9. If that is still not helpful, I may be able to make an example on my personal google account to share. – Caleb Nov 29 at 20:22
  • Yes, make a sample sheet and share the link Caleb. I know it all makes sense when you write it, because you know what you're looking at. But from "out here," it really won't make sense without our getting to see what you see and know what you know. – Erik Tyler Nov 30 at 3:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.