Powershell script to export list data into excel at a scheduled interval

Posted on 2014-08-11
Last Modified: 2014-09-03

I am looking for a powershell script that will export SP list data into a spreadsheet at regular intervals. How do I write script for this and how do I schedule this job? Please note that I want all the fields from the list to be exported.

Any help is appreciated.
Question by:Techsavy
    LVL 9

    Expert Comment

    by:Mohit Nair
    LVL 31

    Expert Comment

    by:Jamie McAllister MVP
    I would use Task Scheduler on a Windows Server to schedule this. It's great in terms of being easy to use, robust, and has a credentials store for the script to run under.

    I don't have a script to hand, but the export part should be relatively easy. As for the spreadsheet formatting it's possible to write excel spreadsheets as XML (in the correct schema) which is the easiest option for powershell. I've done dynamic spreadsheet creation before in C#.

    I'll have a look in the community to see if there are scripts to get you started.

    One other possibility would be to use the same export to excel function you get from lists and libraries by somehow using one of the .iqy files in a scheduler. I'll have to look into this, as I speculate it might be possible.
    LVL 31

    Accepted Solution

    Task Scheduler can be made to work like this;
    Download excel from SP
    But you'd have to put in a bit more work to make it slick and get around the security prompts by turning them off. Then a Macro to save the file somewhere. Perhaps faster than trying to code something though...
    LVL 31

    Expert Comment

    by:Jamie McAllister MVP
    OK, here's some Powershell to get the data out of the list as CSV, which will be loadable by Excel;

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
    For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now