Skip to main content

How to AutoFit Columns with an Office Script

· 4 min read
Jagdish Kumawat
Founder @ Dewiride

After a script writes long text, the column usually stays at its old width and the text is cut off by the next cell. autofitColumns() does what double-clicking a column's edge does: it makes each column as wide as its longest value.

Syntax​

General form
<range>.getFormat().autofitColumns();

From Microsoft's ExcelScript.RangeFormat reference:

  • <range>: the columns to fit, such as sheet.getRange("A:A") for column A or sheet.getRange("A:C") for three.
  • getFormat(): the range's formatting.
  • autofitColumns(): changes the width of the columns of the current range to achieve the best fit, based on the current data in the columns. Note the last words: the data in the columns, not only in the range.

The Example​

A1 holds Strawberries and cream and B1 the price 4.25. Column A is too narrow, so the text stops at Strawberri:

Row 1 with Strawberri cut off in A1 and 4.25 in B1

The script Autofit Column A fits column A. Create it as in How to Create and Run an Office Script in Excel:

Autofit Column A
function main(workbook: ExcelScript.Workbook) {
// Make column A as wide as its longest value.
const sheet = workbook.getActiveWorksheet();
sheet.getRange("A:A").getFormat().autofitColumns();
}

The Code Editor with the script Autofit Colum… and its code: getActiveWorksheet and getRange("A").getFormat().autofitColumns()

The Result​

After Run, column A is as wide as Strawberries and cream:

Column A widened so Strawberries and cream fits, with 4.25 in B1

It Measures the Whole Column​

autofitColumns() looks at every value in the column, not only the cells in the range you call it on. I found this out in another workbook: autofitting a table's range, getTable("Entries").getRange(), made column A as wide as a long title in A1, above the table. The fix was to run the autofit first and write the title after it. A title written last, with empty cells to its right, still shows in full, because text spills over empty cells.

Common Mistakes​

MistakeWhat happensDo this instead
Autofitting before writing the dataThe width fits the old valuesAutofit after the last value is written
A long title in the column when the autofit runsThe column fits the title, even if you fit only the data's rangeWrite the title after the autofit
autofitRows() to make text fit acrossThat changes row heightsautofitColumns() for widths

Troubleshooting​

SymptomLikely causeFix
The column is much wider than the dataA long value somewhere else in the columnWrite that value after the autofit, or set the width with setColumnWidth
Dates show ######## after the scriptThe script ran before the dates were written or formattedRun the autofit last
Nothing changesThe column was already the right widthNothing to fix

FAQ​

How do I fit every column that has data?​

sheet.getUsedRange().getFormat().autofitColumns() fits all the columns in the part of the sheet that's in use.

Can I fit a table's columns?​

Yes: workbook.getTable("<name>").getRange().getFormat().autofitColumns(). The columns still fit everything in them, including values above or below the table.

Is there a way to set an exact width?​

Yes: getFormat().setColumnWidth(<width>) sets a width in points instead of fitting.

Conclusion​

<range>.getFormat().autofitColumns() makes each column in the range as wide as the longest value in the column. Run it after the data is written, and write long titles after it.

Additional Resources​