I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats).

Is there a way to insert a column on one sheet, and have that column inserted across all sheets?

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.

You can have the header row of each spreadsheet reference the header row of the first worksheet. Anytime you make a change to the first worksheet, the change will be reflected in all the other worksheets.

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.