Google-sheets – How to force a Google Spreadsheets to have column in one tab have the same width as the same column in a different tab

google sheets

Is there any way to force a Google Spreadsheets to have column i in tab j of same width as column i in tab k?

In other words, can I configure Google Spreadsheets so that a column width change is always applied to all sheets in a Google Spreadsheets document?

Best Answer

This can be done with an Apps Script. To use, enter it in (Tools > Script editor), save, and add a trigger via (Resources > This project's triggers) with parameters "from spreadsheet" and "on change".

The change of column width is an event of type "OTHER". It doesn't seem possible to detect which column was changed; only what sheet (tab) it came from. So I record the width of all columns in that sheet, and set their counterparts accordingly.

An assumption made here is that the column whose length was modified is within the data range of the sheet: that is, either it contains some data, or some column to the right of it does. 

function syncWidths(e) {
  if (e.changeType === 'OTHER') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet(); 
    var widths = [];
    for (var i = 1; i < activeSheet.getLastColumn(); i++) {
      widths.push(activeSheet.getColumnWidth(i));
    }
    var sheets = ss.getSheets();
    for (var j = 0; j < sheets.length; j++) {
      if (sheets[j] == activeSheet) {
        continue;
      }
      for (var i = 0; i < widths.length; i++) {
        sheets[j].setColumnWidth(i + 1, widths[i]);
      }
    }
  }  
}
Related Topic