can we make it "export to spreadsheet" in sharepoint list automated?

Question

my client wants it automated so that the exported csv file will be saved periodically into a location and he can do his necessary action. rather than coming into the site and exporting it each time. any idea, wokflow might be an option , but how ???

Answer 1

You can export a list to Excel and the current view will display in an Excel table. This table will have a connection to the SharePoint list and you can refresh the table from the Data ribbon > Refresh All. The connection details are defined in a connection file that lives in the current user's temp folder.

The Excel file can be saved anywhere, re-opened at any time and the data can be refreshed. Excel settings can be applied to refresh the data automatically without prompting the user when the file is opened.

If only one user wants to see the Excel file, that's all there is to it.

If several users need to see this Excel file, then the Connection file in the temp folders is not so useful. Rather, open the data connections dialog in Excel, save the data connection as a file. In SharePoint, create a Data Connection library on the site and upload the data connection file to that library. You may want to check that the file is published, since Data Connection libraries require publishing a major version by default.

Back in Excel, change the data connection to the SharePoint data connection file and discard the data connection that points to the user's local profile.

This Excel file is now linked to a SharePoint list with a connection file that is stored on a SharePoint site. So any user with read access to both the connection file and the Excel file will be able to open the Excel file and refresh the data displayed in Excel.

I've used this technique many times to create rich Excel user interfaces with pivot tables and dashboards that are fed by data from SharePoint lists.

You can even hide the Excel sheet with the raw data from SharePoint, so the user only sees the dashboard or interface with the simple click-and-gasp functionality.