Google Sheets - how to insert row(s) into multiple tabs within same sheet?

Question

I have a Google Sheet with 20+ tabs.

Is it possible to perform the "Insert -> Row Above" command on multiple tabs?

I know it can be done in Excel (Group Tabs, Insert, Ungroup), but I don't see any way to group tabs in Sheets.

I can do it manually, i.e. Insert->Row Above, click next tab, Insert->Row Above, click next tab, etc - but that's tedious, annoying, and error-prone.

So, is there a command to insert row and replicate that action across all tabs in a sheet? Or is it possible to write a script for this?

Unknown Unknown · Accepted Answer · 2017-07-17T19:34:58

There is no "group tabs" in Google Sheets. The following script provides a function to insert a row above the current position in all sheets of the current spreadsheet.

function insertRow() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var row = SpreadsheetApp.getActiveRange().getRow();
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].insertRowBefore(row);
  }
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Insert row above everywhere",  functionName: "insertRow"}]);
}

Specifically, insertRow is the function that performs insertion, and onOpen is to add a corresponding menu item to the spreadsheet (invoked automatically when the spreadsheet is opened in a browser).

Google Sheets - how to insert row(s) into multiple tabs within same sheet?

1 Answers