Export a SharePoint list to Excel automatically each morning

23 October 2026

Tags: Relational databasesReportingDesktopCommand line

The request arrives every Monday: “can you send me the list as a spreadsheet?” So every Monday somebody opens the list, picks Export, saves the file, attaches it to an email, and by Wednesday the spreadsheet is out of date. So you try to automate it. A Power Automate recipe from a forum works on a test list of fifty items, then on the real list it stops at 5,000 rows, takes most of the morning, or locks the workbook while people are trying to read it.

This post covers the three routes Microsoft gives you, in the order most people try them, with the published limit that decides where each one stops. Then it covers the route that holds up on a large list: a database copy of the list that Excel refreshes from on its own. The figures come from Microsoft Learn and Microsoft Support, linked each time.

How do you export a SharePoint list to Excel?

From the list’s command bar, pick Export, then Export to Excel or Export to CSV. Microsoft’s Export to Excel page says both export the items shown in the current view, so the view decides which rows and columns you get. The options only appear when no items are selected.

Export to Excel downloads a small file called query.iqy. Excel opens it, asks you to enable the connection if you trust the site, and builds a table with a one-way data connection to the list. Edits made in Excel never go back to SharePoint. Folders do not appear as folders, though Item Type and Path columns are added so you can filter by them.

Export to CSV writes a plain file with no connection behind it, and Microsoft caps it at 30,000 rows. On a Mac, CSV is the only export on offer, so a Mac user cannot get the live .iqy workbook at all.

Because both exports start from a view, they inherit the view’s limits. A view that hits the 5,000-item list view threshold blocks the export with it, so exporting more than 5,000 items means a view filtered on an indexed column, exported in slices. Our post on what breaks past 5,000 items covers building those views.

Does Export to Excel refresh automatically?

Partly. The .iqy workbook keeps its connection, so Data, Refresh All fetches the list again. Excel’s connection properties add two options on the Usage tab: refresh data when opening the file, and refresh every so many minutes. With both ticked, whoever opens the workbook at 9am sees the list as it was at 9am.

The catch is that both options run inside Excel on somebody’s machine. A closed workbook in a SharePoint library does not refresh itself overnight, and every refresh asks SharePoint for the whole view again. On a list of a few hundred rows that is quick. On a list of tens of thousands with several lookup columns, the refresh is slow enough that people stop ticking the box.

Power Automate and the Excel Online connector’s limits

The search results for “export sharepoint list to excel power automate” mostly show the same recipe: a Recurrence trigger at 6am, Get items on the list, an Apply to each loop, and the Excel Online (Business) connector’s Add a row into a table inside the loop. It works, and it is easy to build. The limits that matter are all on the Excel side.

The Excel Online (Business) connector reference sets a throttling limit of 100 API calls per connection every 60 seconds. Add a row writes one row per call, so a list of 10,000 items needs at least 100 minutes of calls before the workbook is complete, and more when the connector retries. The same page caps the file at 25 MB, warns that a row written by a flow can take up to 30 seconds to appear, and says a file may stay locked for up to 6 minutes after the connector last touched it. It also says writing to one file from a flow and from Excel at the same time is not supported, which is exactly what happens when someone opens the report while the flow is still writing.

Then there is the flow’s own allowance. Every action counts as a request, the loop’s actions included. Microsoft’s request limits page gives a user with Microsoft 365 apps 6,000 requests in 24 hours, and 40,000 with a paid Power Automate licence. A 10,000-row loop does not fit inside 6,000.

A flow that writes CSV instead

A flow that skips the Excel connector avoids most of that. After Get items, a Select action picks the columns and turns lookup and person fields into plain text, and the Create CSV table action turns the whole array into CSV in one step. A Create file action on the SharePoint or OneDrive connector then saves it, and Excel opens it like any other CSV. That is a handful of actions a day instead of one per row, and no workbook stays locked.

The 5,000-item ceiling on Get items

The Get items step is where a flow meets the list’s size. Microsoft’s flow limits page puts flows owned by a Microsoft 365 plan on the Low performance profile, where pagination returns at most 5,000 items and an Apply to each loop processes at most 5,000. Our post on Power Automate Get items past 5,000 and 100,000 covers the default page size, the pagination setting and how the flow owner’s licence sets the cap, so it is not repeated here. The short version is that a flow owned by a Microsoft 365 licence reads at most 5,000 items, and a premium licence moves that line to 100,000.

Can Power Query in Excel read a SharePoint list directly?

Yes. In Excel, Data, Get Data, From Online Services, SharePoint Online List asks for the site address and lists every list on the site. The SharePoint Online list connector is the same one Power BI uses, and Learn lists Excel among its products. Power Query lets you drop columns, expand a lookup into its display value and load a clean table, which is more than Export to Excel gives you. The same connection properties then refresh it on open or on a timer.

The connector comes in two versions, and each has its own catch. Version 2.0 is subject to a maximum of 12 join operations per query, and every lookup, person or managed metadata column is a join, so a wide list fails until you point the query at a default view with fewer lookups. Our post on the 12 lookup column threshold covers that error in full. Version 1.0 does not have the join limit, but Learn notes it returns dates and times in UTC without converting them, so a Created column can disagree with the browser.

Excel for the web can refresh a SharePoint Online List query with your organisational account, according to Microsoft’s Power Query data sources in Excel versions page. Like the .iqy export, every refresh reads the list through SharePoint again, page by page, so a large list is slow on every open.

Which native route fits which job

If the list has a few thousand rows and the workbook is opened a few times a day, Power Query or the .iqy workbook with refresh on open is the simplest answer, and nothing else is needed. The routes start to strain as the list grows, gains lookup columns, or gains readers who each refresh it.

Keeping Excel in sync with a SharePoint list through a database

SPSync does not write Excel or CSV files. It copies each list into a table in SQL Server, Postgres or another database, and keeps that table current by reading only what changed since the last run. Excel then connects to the table with its own database connector and refreshes from it. The workbook ends up as current as the copy, and the copy is a few minutes behind the list.

Learn lists Excel as a product for both the SQL Server connector and the PostgreSQL connector, and the refresh options are the same ones as before: refresh on open and refresh every so many minutes. The difference is what a refresh costs. A query against an indexed table returns the rows in the time a database takes to answer, with no paging through SharePoint and no throttling, however many people open the workbook at 9am.

Two details matter before you choose a database:

• Excel for the web can refresh SQL Server Database and PostgreSQL Database queries with a user name and password, according to the data sources page, but not a source that needs an on-premises data gateway. A workbook shared in Teams or SharePoint therefore wants a database it can reach over the internet, such as Azure SQL or a hosted Postgres.
• The same page lists SQL Server for Excel on the Mac and does not list PostgreSQL, so pick SQL Server or Azure SQL if Mac users open the workbook. On Windows, the PostgreSQL connector relies on the Npgsql provider, which Learn’s connector page describes.

What the table looks like in Excel

Each list becomes a table with its own columns plus a stable key. A lookup arrives as two columns, {col}_id and {col}_value, so the display text is already on the row and a formula that read the lookup’s text now reads the _value column. Multi-value columns get a child table each, and deleted items keep their row with sp_deleted_at set.

If you turn on reporting views, SPSync creates one per list, named v_{prefix}_{list}, that resolves person columns to display names and leaves out deleted rows. That view is usually the right thing to load into a workbook. The Excel reports use case shows the same setup for Access linked tables as well as Excel.

The copy is one way. SharePoint stays the system of record, people keep editing the list, and the copy follows. It does not carry SharePoint item permissions, so grant access to the database with the same care as the list.

Setting it up

1. In the desktop app, connect the site, tick the list and the lists its lookups point at, and choose SQL Server or Postgres as the destination. Turn on reporting views.

2. Or, from the command line, write the same replication to a file and run it:

   spsync export-config --output excel.yaml --pipeline-name excel \
     --prefix sp --destination-kind mssql --connection-string-env REPORTING_DB_URL \
     --dataset-name reporting --client-id $SPSYNC_CLIENT_ID \
     --site-url https://contoso.sharepoint.com/sites/sales \
     --lists-by-name Opportunities,Accounts --create-reporting-views
   spsync run excel.yaml

3. Run the first sync, then leave the desktop app’s schedule running, or call spsync run from cron or a scheduled task on a server, so the copy is current before anyone opens the workbook.

4. In Excel, Data, Get Data, From Database, choose SQL Server or PostgreSQL, sign in, and load the reporting view for the list.

5. Open the connection’s properties and tick refresh data when opening the file. Add a refresh every 30 minutes if the workbook stays open all day.

6. Save the workbook to SharePoint or OneDrive and share it in place of the Monday email.

The SQL Server steps are set out in more detail in seven steps from a SharePoint list to a SQL Server table, and the SQL Server and PostgreSQL pages cover each destination. If the same list also feeds a Power BI report, why Power BI refreshes on SharePoint lists slow down covers moving that report to the same table.

Is the workbook current at 7am?

It is as current as the last sync when somebody opens it, which on a short schedule is a few minutes. That is close enough for a morning report, though it is not real time. Excel only refreshes a workbook that is open, so the shared workbook replaces the Monday email rather than recreating it. If a reader truly needs a file attached to a message, that is a separate job for a flow, and the flow limits above still apply to it.

SPSync writes to more destinations than SQL Server and Postgres, including warehouses, lakes, search indexes and vector stores, and the same lists can go to several of them with one replication each. If the destination you need is missing, let us know.