CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
StackUser
StackUser

Reputation: 5398

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

Upvotes: 4

Views: 3223

Answers (5)

lucasls
lucasls

Reputation: 1649

I tried the solutions here, and they didn't work for me, so here's a workaround:

for (let i = 1; i <= sheet.getLastColumn(); i++) {
    sheet.autoResizeColumn(i);
    sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 1.1);
}

It will auto-resize the column and then increase it 10%.

I hope Google solves the issue eventually, and this workaround and others are no longer needed.

Upvotes: 0

Heidi Rowan
Heidi Rowan

Reputation: 21

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);
    }
  }

Upvotes: 1

smac89
smac89

Reputation: 43234

This is a complementary answer to the currently accepted answer, to show how to use the builtin objects to build up the batch request. This may be useful if you actually have multiple resize requests you want to send in at once.

At the end I also explain what might be causing the autoresizing algorithm not to work:

const batchUpdateResize = Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateResize.requests = [];

// create a resize request
const resizeRequest = Sheets.newAutoResizeDimensionsRequest();
const dim = resizeRequest.dimensions = Sheets.newDimensionRange();
dim.sheetId = ss.getSheetByName("Sheet1").getSheetId();
dim.dimension = SpreadsheetApp.Dimension.COLUMNS;
dim.startIndex = 0;
dim.endIndex = 10;

// add the request to the list
batchUpdateResize.requests.push({ autoResizeDimensions: resizeRequest });

// ...finally send the request
Sheets.Spreadsheets.batchUpdate(batchUpdateResize, ss.getId());

Possible workaround for lack of resizing

From experimenting with the auto-resize of columns, I noticed that the initial width of the columns nominated for resizing, actually matters and determines whether or not the autoresize algorithm will resize them or not.

I discovered that when the columns have an initial width which is less than 8, auto resize will not resize them or with the case of an initial width of 7, it will not resize the last column. This behavior is the same for the AutoResizeDimensionsRequest rest API and the autoResizeColumns sheet API.

Using this discovery, I set the initial column widths of all the columns which need to be resized to 8, then use the auto resize algorithm to resize them, and this seems to produce the correct results.

Upvotes: 0

Rodrigo Cunha
Rodrigo Cunha

Reputation: 1

By now it's only possible to use autoResizeColumn. So an alternative code could be:

sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
for(let i=1; i <= 10; i++){
sheet.autoResizeColumn(i);
}

Upvotes: -2

Tanaike
Tanaike

Reputation: 201653

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:

Upvotes: 4

Related Questions