Synchronize Outlook Tasks folder with an Excel file.
GTD (Getting things done) and other time / task / personal productivity approaches underline the importance of a Single Todo List, not your email inbox, that is easy to add to and is accessible anywhere.
The tasks screen of Outlook allows you to do this very well, but is tied to your PC. The mobile version (Microsoft Todo app) syncs well, but does not bring all the information through - e.g. does not allow you to categorize your tasks.
Since this script synchonizes Outlook Tasks with an Excel file, it allows you to do the following things:
- Bulk update of tasks using Excel, easier to edit than the Outlook Interface.
- Allows you to backup Tasks if you accidentally delete them in Outlook.
- By saving to OneDrive, Google Drive or similar, it makes one tasklist available across multiple devices.
Given a typical Outlook Task folder like this:
The script synchronizes (2 way) with an Excel file like the one below. Of course a typical todo / task list could have a hundred (or more items).
By "2 Way" we mean that edits in Outlook or the Excel file get synchonized with each other, back and forward. But for safety first, we treat the Outlook Task list as the 'gold' copy.
- Script loops through Tasks in Outlook, checking the unique EntryID
- Script searches the Excel file (normally
task-data.xls) for any Tasks matching this ID - Script tries to update the Outlook task only if a matching task in Excel has Modified set to Y
- Script searches the Excel file (normally
- Script makes a backup copy of any previous Excel file. e.g. copy
task-data.xlsto1task-data.xls, etc - Script makes a template from the previous Excel sheet (
task-data.xls) - deletes out all data except the first row, keeping formatting, filters etc. - For all Outlook Tasks, the Script outputs selected fields to this Excel file.
- Following the 'Outlook is Gold' approach, this script
- Does not delete in Outlook any task deleted in Excel - since we could mistakenly delete a new Outlook task, created after the Excel List was exported.
- Does not add to Outlook, and task added to Excel - since we could mistakenly add back a task we decided to delete in Outlook
- Script only updates from Excel into Outlook if the Modified Column in the Excel spreadsheet has been set to Y.
- Make sure you have Outlook on your machine -(doh!)
- Install Python on your machine.
- Make sure you have the required libraries - typically this will be something like
pip install pandas openpyxl pywin32in a terminal. - Download the two files you need into a directory, listed at the top of the page
outlook.py- the actual sync scripttask-data.xlsx- the Excel file that syncs with outlook
- Run the script in a terminal using a command similar to
python outlook.py- By Default - the script will look for the template (task-data.xls) in the same directory as it is run. Log files and backups will also by placed in this directory.
- Outlook.bat is there for convenience.
The comments in the outlook.py script should make it pretty clear what is going on. The Excel file names, the log file names and backups are all set as constants at the top of the file (e.g. if you want change the Excel task file location).
If you want to extract / upload different properties from the Outlook tasks (e.g. percent complete), the pattern should be very familiar. The names used in code may differ in the Outlook object model from what you use in the Outlook Desktop interface. A link is given below to the Microsoft reference to help you.
The approach taken is to use the API provided by Outlook's COM model, rather than the newer Microsoft Graph API. The reason for this is that (currently) not all Task information is exposed by the Graph API - it appears to be limited to the few fields used in the Microsoft Todo app.
There is a lot of information on the Web describing PyWin32 - the library used to connect Python to Windows Applications like Outlook. There is less information on the Object Model within Outlook - and mostly it is intended for VBA and C# users (athough the method calls and params are very similar). Some good starting points used in creating this script: