Is .setColumnWidth() broken in Excel scripts?

Question

Is .setColumnWidth() broken in Excel scripts?

Marco Couch 30

I have written a very simple code in Office Scripts which should simply set some column widths and the header rows (see below). However, when I run the script, the column widths are nowhere near the values I have input (I have added comments to show the column widths which get set when the script is run).

Is this a general issue with .setColumnWidth() or have I missed something?

function main(workbook: ExcelScript.Workbook) {

	let sheets = workbook.getWorksheets();

	sheets.forEach(sheet => {
		sheet.getRange("E:G").getFormat().setColumnWidth(14.00); //becomes 2.00 (19px)
		sheet.getRange("H:I").getFormat().autofitColumns; 
		sheet.getRange("J:J").getFormat().setColumnWidth(25.00); //becomes 4.00 (33px)
		sheet.getRange("L:L").getFormat().setColumnWidth(16.43); //becomes 2.43 (22px)
		sheet.getRange("S:S").getFormat().setColumnWidth(11.43); //becomes 1.43 (15px)
		sheet.getRange("U:U").getFormat().setColumnWidth(19.29); //becomes 3.00 (26px)
		sheet.getRange("1:1").getFormat().autofitRows();
	})
}

1 answer

Your answer

Answer 1

Michelle Ran 346 Microsoft Employee

Hi @Marco Couch - thanks for reaching out! I've confirmed that setColumnWidth() incorrectly sets the width and have flagged it for our team to investigate (internal tracking: #9333095). I'll follow up once I have more information to share. In the meantime, I believe the best workaround is to try a few different values in the API until you get the desired column width. Sorry that I don't have a better solution at the moment!

Marco Couch 30 Reputation points

2024-09-13T21:44:59.9433333+00:00

Thank you for confirming.

For anyone else who sees this and needs a fix in the meantime, you have to input your value in pixels rather than default units, and there's a linear relationship between the input and output:

y = 0.75x - 0.03

Where x = "desired width (in pixels)" and y = "value to enter in setColumnWidth()"

N.B. these values of m and c have both been rounded to 2dp but it should get you roughly where you need to be for now.
MosesR 0 Reputation points

2024-11-08T18:49:08.8833333+00:00

Thanks for the information!

I've tried entering the values directly as pixels, and the script returns a different width when it executes. As of now, it appears to be executing it as x/0.6, where x is the pixel value. Set your pixel value to 0.6x to compensate.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Arttu Pyykönen 0

There is also different behaviour when running the script from Excel online and from desktop. I got different values than OP.

sheet.getRange("J:J").getFormat().setColumnWidth(25.00); 	
// running from online: becomes 4.18 (53px)	
// running from desktop: becomes 3.91 (50px)
sheet.getRange("L:L").getFormat().setColumnWidth(16.43); 	
// running from online: becomes 2.55 (35px)	
// running from desktop: becomes 2.36 (33px)

Michael Waters 1 Reputation point

2024-12-04T23:26:20.2+00:00

Im having the same issues on the desktop. If you set multiple column ranges it also divides that by the number of columns as well, I think...
Michelle Ran 346 Reputation points Microsoft Employee

2025-02-24T23:46:31+00:00

Just to follow up - we've fixed the code generated by the recorder so that newly-recorded scripts will correctly set the column width. It is a little trickier for us to change the underlying behavior of the setColumnWidth() method, but hopefully this improves the experience of creating scripts. I believe this fix should've rolled out - however, if anyone is still encountering this issue, please send feedback through Help > Report a Problem in Excel. Thank you for your patience!
Marco Couch 30 Reputation points

2025-02-27T14:30:39.99+00:00

Thank you very much for the update.

For those of us who write our scripts manually rather than record them, is the best advice for now to keep trying different values?

Is #9333095 still tracking this issue for the underlying behaviour?
Michelle Ran 346 Reputation points Microsoft Employee

2025-02-27T23:13:31.2666667+00:00

Hi Marco - unfortunately, I think it's unlikely that the underlying behavior will be changed, as this method has been around for some time and changing its behavior could be disruptive to people already using it.

As for a workaround when manually writing a script - essentially, the problem is that when setting the column width through the UI on win32, it is done in default units. However, setColumnWidth() always uses points - hence, the need for conversion. I think the most reliable solution would be to use the recorder or "Insert action" to set the column width, and then copy that generated code into your script. I'm sorry we can't offer a better solution at this point - hopefully, that still helps?

Share via

Is .setColumnWidth() broken in Excel scripts?

1 answer

Your answer