autoResizeColumn works incorrectly not works as expected

Question

I am trying to resize the column1 to column 10 through google app scripts using the below code.

sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
sheet.autoResizeColumns(1, 10);

The columns are resized but not as expected.

I expected like this but actually I got this

Answer 1

Unfortunately, it seems that the method of "autoResizeColumns" in Class Sheet has the issue yet. So how about this workaround? When I had tried the auto resize using Sheets API, I noticed that the results are different between Spreadsheet service (SpreadsheetApp) and Sheets API. By using this, I use the method of "AutoResizeDimensionsRequest" in Sheets API as a current workaround.

When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Sample script:

When sheet.autoResizeColumns(1, 10) in your script is converted to the script using Sheets API, it becomes as follows.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var resource = {"requests": [{"autoResizeDimensions": {"dimensions": {
  "dimension": "COLUMNS",
  "sheetId": ss.getSheetByName("Sheet1").getSheetId(),
  "startIndex": 0,
  "endIndex": 10,
}}}]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

Note:

• In my environment, it seems that this workaround can avoid the issue of Spreadsheet service. But if this was not the result you want, I apologize.

Reference:

• AutoResizeDimensionsRequest

Answer 2

This is a super low tech answer as I am just starting out, but I set up my script to resize to 130% of autosizedWidth

for (var col = 1; col <= totalColumns; col++) {
    sheet.autoResizeColumn(col);
    var autosizedWidth = sheet.getColumnWidth(col);
    var adjustedWidth = autosizedWidth * 1.3;
    if (maxWidths[col]) {
      sheet.setColumnWidth(col, Math.min(adjustedWidth, maxWidths[col]));
    } else {
      sheet.setColumnWidth(col, adjustedWidth);
    }
  }