How to Set Column Width Across Multiple Google Sheets

google sheets

The user below posted this solution on the Google Docs forum for changing the column widths across multiple Google Sheets.

The topic is locked there and can't reply, I was wondering if anyone would be able to help me amend the code as it is not working correctly.

Essentially the issue is that the code works for the first sheet beside the "master" sheet that the column widths are being copied for, however, if you create another sheet and run the script it states the following error:

"Those columns are out of bounds. (line 16, file "t4")"

I can only assume that the code is not properly picking up the next sheet in the sequence, I am only learning JavaScript so don't really know what to edit to make it work correctly. I am not worried about the menu item as I am going to setup a trigger to run and apply the formatting.

    function copyColumnWidthsFromFirstSheetToTheOtherSheets() {
  // this function takes note of the column widths on the leftmost sheet and
  // sets the column widths on all the other sheets to those values
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var firstSheet = allSheets[0];
  var currentSheet, columnNumber, columnWidth;

  // iterate through all the sheets but the first one in the spreadsheet
  for (var sheetNumber = 1; sheetNumber < allSheets.length; sheetNumber++) {
    currentSheet = allSheets[sheetNumber];

    // iterate through the columns
    for (columnNumber = 1; columnNumber <= currentSheet.getMaxColumns();columnNumber++) {
      // get the width of the column on the first sheet
      columnWidth = firstSheet.getColumnWidth(columnNumber);
      // set the column width on the current sheet to the width of the same    column on the first sheet
      currentSheet.setColumnWidth(columnNumber, columnWidth);
    }
  }
}


function onOpen() {
  // add a custom menu and menu item
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuItem = "Copy column widths from the first sheet to all the other sheets";
  var entries = [
    {
      name : menuItem,
      functionName : "copyColumnWidthsFromFirstSheetToTheOtherSheets"
    }
  ];
  ss.addMenu("Set column widths", entries);
}

Credit to:
–Hyde
https://productforums.google.com/forum/#!topic/docs/7XR9ozdi9R0

Best Answer

The cause of the error is that the firstSheet doesn't have the same number of columns than the currentSheet.

You could avoid this error by checking if firstSheet.getMaxColumns() >= currentSheet.getMaxColumns()