Excel File from Databricks Not Auto-Adjusting Columns in Power Automate Email Attachment

DataP1
New Contributor

‎06-01-2025 03:42 PM

Hi community,

I've built an automation workflow using Databricks and Power Automate. The process runs a query in Databricks, exports the result to Excel, auto-adjusts the columns based on the header/content, and then Power Automate picks up the file and sends it via email.

Everything is working as expected except that the column auto-adjust in the Excel file is no longer applying correctly — the attachment comes through with unformatted (narrow) columns.

Has anyone faced a similar issue or found a reliable way to auto-fit Excel columns either within Databricks (Python) or as part of the Power Automate process?

Open to both Python-based solutions (e.g., using openpyxl or xlsxwriter) or Power Automate options like Office Scripts.

Thanks in advance!

0 Kudos

niteshm
New Contributor III

‎06-07-2025 12:56 AM

You can try, Office Scripts, "sheet.getUsedRange().getFormat().autofitColumns()" but it requires Microsoft 365 Business Standard, E3, E5, or Education license. Most enterprises would have this, please contact your IT dept to enable Office Scripts in the Microsoft 365 Admin Center.

Orelse, you can handle column auto-width, using openpyxl, entirely within Databricks before the file gets passed to Power Automate, no Office Scripts needed, no Excel Online dependency.

  1. Convert Spark DataFrame to Pandas
  2. Write headers and data to an Excel worksheet
  3. Calculate max content length per column
  4. Apply dynamic column widths using openpyxl.utils.get_column_letter
  5. Save the file to DBFS or preferred location for PowerAutomate to pikup.
0 Kudos

Kayla
Valued Contributor II

‎06-07-2025 07:24 AM

I was actually playing with this almost this exact issue last week in Databricks/Python.
I found xlsxwriter was really easy to set up - the simplest way was to just convert it to a table. The code in their example Pandas table is very simple to update: https://xlsxwriter.readthedocs.io/example_pandas_table.html

Not sure if you need the full table or can get away with 

worksheet.autofit()

If you want to be very fancy, you can also specify column widths for each column. Since we're sending out Excel files for people to print, we did that so everything would be inside a standard page width.
0 Kudos

mark_ott
Databricks Employee
Databricks Employee

‎10-29-2025 01:17 PM

Yes, this is a common challenge when automating Excel file generation—the default export (especially from pandas or Databricks) does not auto-fit column widths, resulting in cramped columns when viewed or emailed. Auto-fitting columns typically requires an explicit command or script in your workflow. Reliable solutions exist in both Python and Power Automate/Office Scripts contexts.

Python-Based Solutions: openpyxl / xlsxwriter

When exporting to Excel from Databricks using pandas’ to_excel, you can post-process the output file with openpyxl or xlsxwriter to auto-size columns:

  • openpyxl: Load the file, then iterate through columns to set the width based on max content length.

  • xlsxwriter: When writing the file, use worksheet.set_column() to fit columns.

Example using openpyxl:

python
from openpyxl import load_workbook def autofit_columns(filename): wb = load_workbook(filename) ws = wb.active for col in ws.columns: max_length = 0 col_letter = col[0].column_letter for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = max_length + 2 ws.column_dimensions[col_letter].width = adjusted_width wb.save(filename) # After pandas to_excel: autofit_columns('your_file.xlsx')

This reliably fixes the column width issue regardless of export source.

Power Automate / Office Scripts

If your Excel file is generated and stored in OneDrive/SharePoint, you can use an Office Script in Power Automate to auto-fit columns before the email is sent.

Example Office Script:

typescript
function main(workbook: ExcelScript.Workbook) { let ws = workbook.getActiveWorksheet(); ws.getUsedRange().getFormat().autofitColumns(); }

You can run this script in Power Automate using the "Run Script" action after the file is created and before the file is emailed.

Best Practice Workflow

  • Python method: Generate Excel → run autofit post-process → upload/send.

  • Power Automate method: Generate Excel → upload to cloud storage → run Office Script to autofit → email file.

Community and Reliability

Many users in forums (Microsoft, Stack Overflow, Databricks) confirm both methods are reliable fixes. Office Scripts are especially robust for cloud-based workflows; Python works well for on-prem or hybrid flows.

In summary:
Auto-fit is not applied by default; use openpyxl or xlsxwriter in Python or Office Scripts in Power Automate to reliably fix column widths in Excel exports before sending.