google-apps-scriptgoogle-sheetsgoogle-sheets-macros

Google apps script: autoResizeColumn works incorrectly not works as expected


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 enter image description here but actually I got this enter image description here


Solution

  • 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:

    Reference: