Skip to content

Instantly share code, notes, and snippets.

@develmaycare
Last active July 20, 2024 15:10
Show Gist options
  • Save develmaycare/4e72e72ae0be85a8efe7a525267afbc3 to your computer and use it in GitHub Desktop.
Save develmaycare/4e72e72ae0be85a8efe7a525267afbc3 to your computer and use it in GitHub Desktop.
Duplicate a row in Google Sheets.
/* To use this in a Google Sheet:
1. Go to Tools > Script Editor.
2. Save the script.
3. Paste this script and click on the bug symbol.
4. Authorize the script.
5. Refresh the sheet.
*/
// global
var ss = SpreadsheetApp.getActive();
function onOpen() {
var menu = [{name:"Duplicate Row", functionName:"duplicateRow"}];
ss.addMenu("Extras", menu);
}
/* Google Sheets does not copy formulas when inserting a row. Bogus.
While not exactly the same, this function duplicates the row, including the
formulas, and clears the cell in column 1 of the new row.
*/
function duplicateRow() {
// Get the current sheet, row, and column.
var sheet = ss.getActiveSheet();
var row = ss.getActiveCell().getRow();
var column = sheet.getLastColumn();
// The range represents the current row from the first to last column.
var range = sheet.getRange(row, 1, 1, column);
//Browser.msgBox("Row: " + row + " Column: " + column);
// Insert a blank row after the current one.
// insertRowsBefore was producing strange results. The exact same code with insertRowsAfter seems to work as expected.
sheet.insertRowsAfter(row, 1);
// Copy the current row to the row we just added. When contentsOnly is true, a "paste values" occurs. We want the functions, so it's set to false.
range.copyTo(sheet.getRange(row + 1, 1, 1, column), {contentsOnly:false});
// Move to column 1 of the new row and clear the contents.
new_range = sheet.getRange(row + 1, 1, 1, 1);
ss.setActiveRange(new_range);
ss.getActiveCell().clear();
}
@develmaycare
Copy link
Author

@zartyblartfast That is odd. It's been a while since I've used this. I suppose one my experiment with getRange(), but I'm glad you found a workaround.

@marko090591
Copy link

@zartyblartfast That is odd. It's been a while since I've used this. I suppose one my experiment with getRange(), but I'm glad you found a workaround.
Hello. Does the script works now in 2023. Could you write a formula for onOpen and duplicateRow function. As shown bellow function duplicateROW doesn work anymore.
13 09 22 09 2023

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment