Part 2b: Using VBA with JSON: Wunderlist

Posted byErica L. Ingram

This Lesson’s Code on Github
Sample Database
Or skip down to explanations of how to do various things in Wunderlist:

Some of this content will be repeated from the previous post on how to interact with JSON. If you know how JSON works with VBA and you want to skip directly to the Wunderlist part, click here to go to “Explanation of Code“.

Today, we’re going to go over how to use VBA to interact with JSON around the web, so you’ll need one piece of software for now, Microsoft Office. When we get to PDFs later, you will need Acrobat (not Reader, but Acrobat). We’ll learn how to get information off the internet via JSON parsing in VBA into variables.

So you might already know what Wunderlist is. It’s a fairly popular task management website and they have an API you can interact with to automate things you want to do on Wunderlist. You can even include custom information in your automated task and list generation.

A possible application for this in the legal industry is, every time you put a new case into your workflow, a certain checklist is generated on Wunderlist without your having to manually enter anything. You could even write code to have different checklists based on different pieces of information, such as one checklist for a certain jurisdiction or state and another checklist for another state or jurisdiction. You could write code to calculate deadlines or a whole list of deadlines automatically and the code will automatically add the tasks and/or lists with the deadlines you wanted.

JSON

JSON stands for JavaScript Object Notation. It is a text string loaded with information that lots of places use to communicate over the internet. The important part is, it’s structured in a way that you can extract pieces of information out of it to use in coding. Many companies have an API that uses JSON that you probably work with every day such as Clio, CourtListener, PayPal, Wunderlist, OneNote, and many others. You can use it to communicate with these companies over the internet and automate the things you do in their software. We will be going over some in the coming weeks. For a more in-depth explanation, I recommend this site. I receive no remuneration for linking that (or anything here); I just like the explanation and think it is sufficient for this exercise.

References

We’ll be working in an empty database. I have provided an almost empty database for this article, which you can find here. It contains three modules and one empty table. Save the database so that you can hit Ctrl + S frequently and save all the time. Get into the habit of it if you can. If you use this database, you may skip down to the section “Security“. If you do not wish to download the sample, continue and we’ll go through setting up our database manually.

Open a new empty database in Access. Just open up Access and select “blank database”. Save it as any name. It’s just a sandbox or testing area, so don’t worry about what it’s called. Once you’ve saved your new empty database, go to Database Tools on the top row above the ribbon and hit ‘Visual Basic’. Now, you should see the following screen:

A module is the white screen you’re looking at in the VBA window. You can name your modules. You may have multiple modules within one file. This is where all your macros and code are stored. But yours will all be empty and we do not want that, so import the two modules from the following two links, VBA Dictionary & JSON Converter. Right click on the “Modules” folder, then import JSON Converter . Right click on “Class Modules”, then import VBA Dictionary. Next, you’ll want to create a third regular module, the one you’ll be editing code in. Mine is named ‘Wunderlist‘. Save again.

You will need to add the following references. References are like libraries that let you use the code we’re learning. These references are what allow ‘early binding’ to occur like we discuss in a little bit. But the more references you add, the bigger your application is and the longer it takes to load, so you only want to turn on the ones you are actually using. Here are the ones we will be using throughout the course of your journey here. The starred ones are the only ones we are actually using today and they are the only ones turned on in the sample database. So if you want to just turn those on, that’s sufficient for this post. But these unstarred ones, you’ll need in later weeks as we progress in the blog.

  • Visual Basic for Applications *
  • Microsoft Office 16.0 Access database engine Object Library *
  • OLE Automation *
  • Microsoft Scripting Runtime *
  • Microsoft Access 16.0 Object Library
  • Microsoft Word 16.0 Object Library
  • Microsoft Internet Controls
  • Microsoft Excel 16.0 Object Library
  • Microsoft XML, v6.0
  • Microsoft Office 16.0 Object Library
  • Microsoft Outlook 16.0 Object Library
  • Microsoft Publisher 16.0 Object Library
  • Microsoft Shell Controls and Automation
  • Microsoft HTML Object Library
  • Microsoft WinHTTP Services, version 5.1
  • Microsoft OneNote 15.0 Object Library

If you have Adobe Acrobat, you want to make sure these are checked on as well. We are not using any of these for this exercise, so you may leave them unchecked.

  • AFormAut 1.0 Type Library
  • Adobe Acrobat 9.0 Type Library
  • Acrobat Distiller
Select ‘Tools’, then ‘References’.
Select references in the list that look similar to the one shown here.

Security

You should never hard-code a token, password, username, client secret, secret I.D., or anything else you wish to keep safe right into your software or database, meaning you do NOT EVER write in your code something like this: 

sToken = "2389407sejklfasdjl23908"

If you do this and someone breaks into your database, they will also have your various secret codes/passwords/etc. At the very least, you should store the various secret phrases, tokens, etc we’ll be discussing in a local text file on a local hard drive, NOT shared, and only give access to it from the current user. Then in theory, after you’ve done that, you could call on it as a variable when you need it and clear it out of memory as soon as you don’t.

Restrict the text file’s access to only your current user allowed or at the very least do not ‘share’ it or put it on a network drive, OneDrive, or a public repository on GitHub (yes, that really happens) or any place like that.

Always use HTTPS for URLs. Do NOT send data over http unless you do not care if the entire world sees it, actors good AND bad, so to speak.

Early Versus Late Binding

Early binding is where you declare a “reference”. As we talked about earlier, you can look at these references by going to Tools –> References in the menu up at the top. With early binding, because the function knows what each object will be doing at start, Intellisense can help you pick out the correct piece of code. It makes auto-fill suggestions for your code.

In late binding, you just declare an object and tell it at the last minute what it should do. But because the object doesn’t know what to do until after the function’s started, Intellisense doesn’t work, among other things. As opposed to the picture below, simply nothing would pop up as you type.

Most of the time, we will be using early binding with references because it is easier to do as a new person, but this exercise will be one example of late binding. Importantly, you do not need to turn on references with late binding. For our purposes, early binding is usually preferred to late.

Intellisense in action with early binding.
In late binding, this will *never* pop up.

VBA and JSON, WunderList:

For now, we’re just going to learn about how to get the information from the internet, Wunderlist in our example, to your Visual Basic for Applications screen. We will go over how to add, delete, and update tasks and lists automatically. In a later post, we will discuss how to gather information you might put in your Wunderlist item.

Now, if you are using my sample database, you may go to the next headline, “Explanation of Code“. If you are using your own empty database, next you should get the entire code, all five functions, from below the headline “Final Code“, found towards the bottom of this post, paste it into your “third” module, save again, and then continue from here. The “third” module in my sample is named ‘‘Wunderlist‘. Save again. If you click the link “Final Code“, there will be another link to come back up here once you’ve copied the code.

Explanation of Code

Pro Tip: Put your insertion point on a line and click the tan area to the left of the line in your VBA window. See the red circle that comes up? Now, when you run your function, it will run the code to that specific line and stop. If you hit ‘run’ subsequently once, it will continue the code, but if you want it to stop entirely, you must hit the ‘stop’ button once more. You will find this extremely useful for debugging your code. Left click on the red circle to remove it.

For this exercise, place a break on the line “Debug.Print “——————————————–“.

Get a List of Wunderlist Lists

We’re going to cover a couple of functions in this post. Again, a lot of this is going to look similar to CourtListener code. In the first function, we will make a list of your Wunderlist lists print in the debug window. Go down to “Final Code” if you need it, but if you’ve been following along, you have it already.

First thing you want to do is get a token and your client ID. You will need a client ID and a token from Wunderlist to communicate with them. For that, go to this link, sign in, and fill out the fields with anything you like. For “App Url” and “Auth Callback URL” fields, enter “http://localhost/” without the quotes.

Again, my earlier warnings about security and regular disclaimer at the end of each article apply. When you hit “Create Access Token”, the window will spit a token back out at you. Copy your token (highlight it and hit Ctrl+C) and put it in a text file on your LOCAL hard drive. Save the text file and close it. Restrict its access as necessary or secure it as necessary. Note the path to the text file or the directory you saved it in. Do the same for the “Client ID”.

Here’s the opener of this code. You’ll see the info box I wrote and then some variable declarations. 

Function fWunderlistGetLists()
'===========================================================
' Name        : fWunderlistGetLists
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetLists()
' Description : gets all Wunderlist lists
'===========================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, vErrorIssue As String
Dim sToken As String, json1 As String
Dim vErrorName As String, vErrorMessage As String
Dim vErrorILink As String
Dim sFile1 As String, sFile3 As String, sLine1 As String
Dim Parsed As Dictionary

Now, we are going to edit the code you pasted. Locate the portion shown below in your pasted code. It should be the ‘next’ portion, immediately following the blurb I just explained. On the first and second lines of this portion, you specify the string of the path to your text file that you just made with the variable sFile1. You should edit the part between the quotes to reflect where your text file is and its name. The fourth line of code reads the file contents. The fifth line saves the file contents into the variable sToken. The final line closes it. We’re going to do this twice, once for the “Client ID” and once for the token.

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Dim sLine3 As String
Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3

Now, in the next couple lines, we’ll assign the URL we’re going to call on to a variable. In the CourtListener example, we constructed it because it had some dynamic aspects to it. This one is simpler and no construction is necessary. You may find other URLs for different tasks at the Wunderlist developer documentation here, but for now I suggest following along because I am going to cover several of these URLs. Then when you understand what’s going on, you can modify your code as you see fit.

Debug.Print  _
     "RESPONSETEXT-----------------------------------------"
    sURL = "https://a.wunderlist.com/api/v1/lists"

Next, we create the object which will call on the URL and establishes that, with the following lines until ‘End With’, do all the actions listed with the object we just made. This is where you put your red dot/break earlier.

With CreateObject("WinHttp.WinHttpRequest.5.1")
      .Open "GET", sURL, False
      .setRequestHeader "X-Access-Token", sToken
      .setRequestHeader "X-Client-ID", sUserName
      .setRequestHeader "Content-Type", "application/json"
      .send json1
      apiWaxLRS = .RESPONSETEXT
      sToken = ""
      .abort
      Debug.Print apiWaxLRS
      Debug.Print "-------------------------------------"
End With

The second line is the actual call on the URL string. There are several other options you can put here in place of “GET”, which you will see when you go try this on other JSON APIs, such as “HEAD”, “POST”, or “OPTIONS”. Use whatever the API tells you to use in the way it tells you to use it. In this case, we’re using “GET”. You do not edit this part now, but when you modify this for your own use, you might.

.Open "GET", sURL, False 'Alternatives to GET:  POST or DELETE

The next three lines are what are called headers. They tell the URL how to handle the request. Each API requires different values for these headers and, just like you did with GET, you should follow the API. The API will tell you how to handle this part. In this case, notice there are three headers. One of them is our token. Another one is our “Client ID”.

        .setRequestHeader "X-Access-Token", sToken
        .setRequestHeader "X-Client-ID", sUserName
        .setRequestHeader "Content-Type", "application/json"

Here’s the send request. 

.send

Next, we store the response from the URL we called into a variable, clear our token variable, and abort the connection.

        apiWaxLRS = .responseText
        sToken = ""
        .abort

Next, we’re going to print the response text in the debug/immediate window so you can see what you got back from Wunderlist. Hit Ctrl + G to pull up the debug/immediate window if it’s not open already.

        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------"
End With

You may run this code. After running this code, it will stop or break at the red break point you made and you will see a response from Wunderlist. It will not look like this exactly because you are getting your own lists, but it will have the same structure, only with your list info in it.

Now, we’re going to ‘parse’ that mess we got back from the Wunderlist URL, because it’s actually not a mess at all. It’s a text string with a ton of information organized very tightly in a structured way. We can break down that string into usable chunks. This is where VBA Dictionary & JSON Converter comes in. These are already in the sample database and/or you downloaded and added them earlier. VBA Dictionary & JSON Converter are additional modules that will break up the JSON string at appropriate points and allow us to stash pieces of that string or pieces of information in a variable. Each piece of information is called a ‘field’. Later in this post, we will discuss the fields Wunderlist has in their JSON strings so that you can get your desired pieces of information. You’ve already installed these two modules, so we can just move right along.

For now, note each list is a search result, with each search result having the same fields, but different information in each of the fields. The following picture is to make the big unformatted string easier to read and explain what’s going on here, for illustrative purposes only. You will not find it or be able to see it in the database or your code.

  • In the following code which parses the JSON response or extracts the needed information from the string and assigns it to variables, the first two lines remove the brackets, [], from the string. The third line parses the response.
  • In the fourth through sixth line, I assign the relevant fields to a variable. In this case, we’re just printing all this to the immediate/debug window.
    apiWaxLRS = Left(apiWaxLRS, Len(apiWaxLRS) - 1)
    apiWaxLRS = Right(apiWaxLRS, Len(apiWaxLRS) - 1)
    Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sID = Parsed("id") '("value") 'second level array
sTitle = Parsed("title") '("value") 'second level array
sOwnerID = Parsed("owner_id") '("value") 'second level array

Debug.Print "List Name:  " & sTitle & "   |   List ID:  " & sID & " " & sOwnerID
Debug.Print "--------------------------------------------"

If you are confused, that’s okay. Let it run and visually match up what ‘prints’ in the debug window with the fields. A visual is sometimes easier. That link is to a nicely formatted but identical JSON string, a sample response from Wunderlist, and you can see other ‘field’ names you may use. These three fields I listed are probably the most important in this function because those fields are what you need to use other functions I’ll be explaining in a moment.

Get List of Wunderlist Folders

This next one is pretty similar, so I won’t explain it much. Similar to how the previous function got lists, this one gets folders instead. The only two real differences are the URL used to call and the field names. Click here to go to the bottom of this post and copy the appropriate Final Code. There will be a link there to come back to this spot.

Function fWunderlistGetFolders()
'============================================================================
' Name        : fWunderlistGetFolders
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetFolders()
' Description : gets list of Wunderlist folders or folder revisions
'============================================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, sRevision As String
Dim sToken As String, sCurrentList As String
Dim sFolderID As String, sTitle As String
Dim sFile1 As String, sFile3 As String, sLine1 As String
Dim Parsed As Dictionary
Dim vListIDs, rep

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Dim sLine3 As String
Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3

Debug.Print "RESPONSETEXT--------------------------------------------"

sURL = "https://a.wunderlist.com/api/v1/folders"
'sURL = "https://a.wunderlist.com/api/v1/folder_revisions"
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", sURL, False
    .setRequestHeader "X-Access-Token", sToken
    .setRequestHeader "X-Client-ID", sUserName
    .setRequestHeader "Content-Type", "application/json"
    .send
    apiWaxLRS = .RESPONSETEXT
    sToken = ""
    .abort
    Debug.Print apiWaxLRS
    Debug.Print "--------------------------------------------"
End With
    apiWaxLRS = Left(apiWaxLRS, Len(apiWaxLRS) - 1)
    apiWaxLRS = Right(apiWaxLRS, Len(apiWaxLRS) - 1)
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sFolderID = Parsed("id")
sTitle = Parsed("title")
Set vListIDs = Parsed("list_ids")
For Each rep In vListIDs
    sCurrentList = rep
    Debug.Print "List IDs in Folder:  "
    Debug.Print (sCurrentList)
Next
sRevision = Parsed("revision")
Debug.Print "--------------------------------------------"
Debug.Print "Folder ID:  " & sFolderID & "   |   " & "Folder Title:  " & sTitle
Debug.Print "Revision No.:  " & sRevision
Debug.Print "--------------------------------------------"
End Function

Get Tasks on a Wunderlist List

Click here to go to the bottom of this post and copy the appropriate Final Code. There will be a link there to come back to this spot.

Seeing a pattern yet? Similar to how the previous functions got lists and folders, this one gets tasks from a list instead. There are several differences between this function and the last two. Namely, you must have a list ID, acquired from either of the previous functions, to get a list of tasks. Once you have this list ID number, it’s a good idea to comment it into all your Wunderlist functions in the ‘info box’ at the beginning so you have it for future reference and don’t have to look it up constantly. The following code is largely the same as the other two functions we’ve gone over; info box followed by variable declaration followed by bringing the token value into the function.

Function fWunderlistGetTasksOnList()
'=========================================================
' Name        : fWunderlistGetTasksOnList
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetTasksOnList()
' Description : gets tasks on Wunderlist list
'               List Names/IDs here
'               Assignee Names/IDs here too
'=========================================================

Dim sURL As String, sUserName As String
Dim sID As String
Dim apiWaxLRS As String, sWLListID As String
Dim sToken As String, json1 As String
Dim sFile1 As String, sFile3 As String
Dim sLine1 As String, sLine3 As String
Dim lAssigneeID As String, bCompleted As String
Dim sDueDate As String, sTitle As String

Dim rep, vDetails As Object
Dim Parsed As Dictionary

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3

The next part is very important. The variable sWLListID is your list ID number that we talked about earlier and the string json1 is a string we are constructing to pass to Wunderlist when we call on the URL in a moment. Now, this number will not be zero or the same as the one in the sample. With the variable json1, we are constructing a very short json string to send with the URL. Notice the list ID number is contained within it. The last two lines print it in the debug window.

sWLListID = 999999999
json1 = "{" & Chr(34) & "list_id" & Chr(34) &  _
     ": " & sWLListID & "}"
Debug.Print "JSON1--------------------------------------"
Debug.Print json1

I’m going to bundle this one up a little more than the previous two functions because you guys probably can read and understand what’s going on in this portion. We are constructing the URL, calling on it with the token and username, sending the json string we just constructed to the URL, receiving the response and status in variables, and printing them to the debug window.

Debug.Print "RESPONSETEXT-------------------------------"
sURL = "https://a.wunderlist.com/api/v1/tasks?list_id="  _
   & sWLListID
With CreateObject("WinHttp.WinHttpRequest.5.1")
     '.Visible = True
     'alternatives to GET:  POST, PATCH, DELETE
    .Open "GET", sURL, False 
    .setRequestHeader "X-Access-Token", sToken
    .setRequestHeader "X-Client-ID", sUserName
    .setRequestHeader "Content-Type", "application/json"
    .send json1
    apiWaxLRS = .RESPONSETEXT
    sToken = ""
    Debug.Print apiWaxLRS
    Debug.Print "-------------------------------------"
    Debug.Print .Status & " " & .StatusText
    .abort
End With

Next, we parse the response we get from Wunderlist, which is our list of tasks. This portion of the code will loop through each task and print that info in the debug window.

The first line needs to be constructed in this fashion so that you may parse correctly. The third line assigns the result to a variable. The fourth line assigns the parsed result to a new variable. The next line begins a loop to loop through each parsed result. Lines six through 10 assign the parsed result’s values to a variable. Lines 11 through 14 print them in the debug window. Line 15 goes to the next result and the last line ends the function.

apiWaxLRS =  _
    "{" & Chr(34) & "List" & Chr(34) & ":" & apiWaxLRS & "}"
'Debug.Print apiWaxLRS
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("List") 
For Each rep In vDetails 
    bCompleted = rep("completed")
    sID = rep("id")
    sDueDate = rep("due_date")
    sTitle = rep("title")
    lAssigneeID = rep("assignee_id")
    Debug.Print "----------------------------------------"
    Debug.Print "Task Title:  " & sTitle &  _
     "   |   List ID:  " & sWLListID & " " & lAssigneeID
    Debug.Print "Completed:  " & bCompleted &  _
     "   |   Due Date:  " & sDueDate
    Debug.Print "----------------------------------------"
Next
End Function

Add Tasks to a Wunderlist List

Click here to go to the bottom of this post and copy the appropriate Final Code. There will be a link there to come back to this spot.

Last thing we’re going to go over is how to add a task to a Wunderlist list. The first difference you will notice between this function and the previous functions is that you must pass values to it or you are unable to run it. Here is how you do that. The first function is going to call on the function that actually adds the task and will supply “test” without the quotes as the value for sTitle and “2019-04-29” without the quotes as the value for sDueDate.

Function test()
Call fWLAdd("test", "2019-04-29")
End Function
Function fWLAdd(sTitle As String, sDueDate As String)
'==============================================================
' Name        : fWunderlistAdd
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistAdd()
' Description : adds task to Wunderlist
'=============================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, sURL As String
Dim sToken As String, sJSON As String
Dim lAssigneeID As Long, bStarred As String
Dim bCompleted As String, sWLListID As String

Dim rep, vDetails As Object
Dim Parsed As Dictionary
Dim sFile1 As String, sFile3 As String
Dim sLine1 As String, sLine3 As String

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Now, the next portion are the variables you might want to change per task sWLListID is the list ID number you want to add this task to. lAssigneeID is the ID number of the person you want this task assigned to (optional value; you may leave it out), and bStarred/bCompleted are strings for the word “false” in lowercase. You cannot use the boolean variable in VBA for this because the first letter of a boolean version is capitalized. These two can also be set to “true” without the quotes.

sWLListID = 999999999
lAssigneeID = 99999999
sUserName = sLine1
sToken = sLine3
bStarred = "false"
bCompleted = "false"

In the next portion, we use what we just assigned to variables to construct a json string to send when we call the URL, followed by some familiar-by-now code. We call on the URL, use “POST” instead of “GET”, use our token, send the JSON string we constructed with it, store the response in a variable, clear the token variable, print out the response to the window, and abort the connection. 

    sJSON = "{" & Chr(34) & _
        "list_id" & Chr(34) & ": " & sWLListID & "," & Chr(34) & _
        "title" & Chr(34) & ": " & Chr(34) & sTitle & Chr(34) & "," & Chr(34) & _
        "assignee_id" & Chr(34) & ": " & lAssigneeID & "," & Chr(34) & _
        "completed" & Chr(34) & ": " & bCompleted & "," & Chr(34) & _
        "due_date" & Chr(34) & ": " & Chr(34) & sDueDate & Chr(34) & "," & Chr(34) & _
        "starred" & Chr(34) & ": " & bStarred & _
        "}"
        
Debug.Print "sJSON--------------------------------------------"
Debug.Print sJSON
Debug.Print "RESPONSETEXT--------------------------------------------"
    sURL = "https://a.wunderlist.com/api/v1/tasks"
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", sURL, False
        .setRequestHeader "X-Access-Token", sToken
        .setRequestHeader "X-Client-ID", sUserName
        .setRequestHeader "Content-Type", "application/json"
        .send sJSON
        apiWaxLRS = .RESPONSETEXT
        sToken = ""
        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------------"
        Debug.Print "Status:  " & .Status
        Debug.Print "--------------------------------------------"
        Debug.Print "StatusText:  " & .StatusText
        Debug.Print "--------------------------------------------"
        Debug.Print "ResponseBody:  " & .responseBody
        Debug.Print "--------------------------------------------"
        .abort
    End With
Debug.Print "--------------------------------------------"
Debug.Print "Task Title:  " & sTitle & "   |   List ID:  " & sWLListID & " " & lAssigneeID
Debug.Print "Completed:  " & bCompleted & "   |   Due Date:  " & sDueDate
Debug.Print "--------------------------------------------"

End Function

Bonus content: If you check out the last function in my sample database, called “fWunderlistAddNewJob”, that is the actual function I use in my company’s database to create a list within a certain folder and then static tasks with calculated due dates within it for each job. I provide that to show you what’s possible in applications with this knowledge. You can also delete tasks with “DELETE and update current tasks with “PATCH” instead of “GET”. You will want to follow the API exactly because doing these different functions requires you to supply different information to Wunderlist before it will work. For example, you need to supply a revision ID when deleting or updating a task.

Resources

Github
JSON Placeholder
JSON Parser
Database File
Wunderlist API docs
VBA Dictionary & JSON Converter

Epilogue

So, now you can get JSON information from the internet into your debug/immediate window. Soon, we’ll learn how to put it in other places. For now, you’re able to automate your Wunderlist task and list management.

If you made it this far, congratulations and thanks for reading. I hope your mind is spinning with all the things you could do with this newfound information! For the next few posts, we’ll be covering how to use JSON with other popular APIs geared towards the legal profession. I have received no remuneration of any kind for this post or any other on this website.

Final Code

Double-click anywhere in the following code area and press Ctrl + C to copy the entire code.

Get Lists from Wunderlist

Click here after you’ve pasted your final code to pick up where you left off with “Get Lists“.

Function fWunderlistGetLists()
'============================================================================
' Name        : fWunderlistGetLists
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetLists()
' Description : gets all Wunderlist lists
'============================================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, vErrorIssue As String
Dim sToken As String
Dim sLine1 As String, sLine1 As String
Dim sFile3 As String, sLine3 As String
Dim Parsed As Dictionary


sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3
    
Debug.Print "RESPONSETEXT--------------------------------------------"
    sURL = "https://a.wunderlist.com/api/v1/lists"
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", sURL, False
        .setRequestHeader "X-Access-Token", sToken
        .setRequestHeader "X-Client-ID", sUserName
        .setRequestHeader "Content-Type", "application/json"
        .send
        apiWaxLRS = .RESPONSETEXT
        sToken = ""
        .abort
        Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
    End With
    apiWaxLRS = Left(apiWaxLRS, Len(apiWaxLRS) - 1)
    apiWaxLRS = Right(apiWaxLRS, Len(apiWaxLRS) - 1)
    Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sID = Parsed("id") '("value") 'second level array
sTitle = Parsed("title") '("value") 'second level array
sOwnerID = Parsed("owner_id") '("value") 'second level array

Debug.Print "List Name:  " & sTitle & "   |   List ID:  " & sID & " " & sOwnerID
Debug.Print "--------------------------------------------"

End Function
Click here after you’ve copied+pasted your final code to pick up where you left off with “Get Lists“.

Get Folders from Wunderlist

Click here after you’ve copied+pasted your final code to pick up where you left off with “Get Folders“.

Double-click anywhere in the following code area and press Ctrl + C to copy the entire code.

Function fWunderlistGetFolders()
'============================================================================
' Name        : fWunderlistGetFolders
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetFolders()
' Description : gets list of Wunderlist folders or folder revisions
'============================================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, sRevision As String
Dim sToken As String, sCurrentList As String
Dim sFolderID As String, sTitle As String
Dim sFile1 As String, sFile3 As String, sLine1 As String
Dim Parsed As Dictionary
Dim vListIDs, rep

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Dim sLine3 As String
Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3

Debug.Print "RESPONSETEXT--------------------------------------------"

sURL = "https://a.wunderlist.com/api/v1/folders"
'sURL = "https://a.wunderlist.com/api/v1/folder_revisions"
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", sURL, False
    .setRequestHeader "X-Access-Token", sToken
    .setRequestHeader "X-Client-ID", sUserName
    .setRequestHeader "Content-Type", "application/json"
    .send
    apiWaxLRS = .RESPONSETEXT
    sToken = ""
    .abort
    Debug.Print apiWaxLRS
    Debug.Print "--------------------------------------------"
End With
    apiWaxLRS = Left(apiWaxLRS, Len(apiWaxLRS) - 1)
    apiWaxLRS = Right(apiWaxLRS, Len(apiWaxLRS) - 1)
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sFolderID = Parsed("id")
sTitle = Parsed("title")
Set vListIDs = Parsed("list_ids")
For Each rep In vListIDs
    sCurrentList = rep
    Debug.Print "List IDs in Folder:  "
    Debug.Print (sCurrentList)
Next
sRevision = Parsed("revision")
Debug.Print "--------------------------------------------"
Debug.Print "Folder ID:  " & sFolderID & "   |   " & "Folder Title:  " & sTitle
Debug.Print "Revision No.:  " & sRevision
Debug.Print "--------------------------------------------"
End Function
Click here after you’ve copied+pasted your final code to pick up where you left off with “Get Folders“.

Get Tasks from a Wunderlist List

Click here after you’ve pasted your final code to pick up where you left off with “Get Tasks“.

Double-click anywhere in the following code area and press Ctrl + C to copy the entire code.

Function fWunderlistGetTasksOnList()
'============================================================================
' Name        : fWunderlistGetTasksOnList
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistGetTasksOnList()
' Description : gets tasks on Wunderlist list
'============================================================================

Dim sURL As String, sUserName As String, sID As String
Dim apiWaxLRS As String, sWLListID As String
Dim sToken As String, json1 As String, sTitle As String
Dim sFile1 As String, sFile3 As String
Dim sLine1 As String, sLine3 As String
Dim lAssigneeID As String, bCompleted As String
Dim sDueDate As String

Dim rep, vDetails As Object
Dim Parsed As Dictionary

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sUserName = sLine1
sToken = sLine3

sWLListID = 999999999

json1 = "{" & Chr(34) & "list_id" & Chr(34) & ": " & sWLListID & "}"

Debug.Print "JSON1--------------------------------------------"
Debug.Print json1
Debug.Print "RESPONSETEXT--------------------------------------------"
    sURL = "https://a.wunderlist.com/api/v1/tasks?list_id=" & sWLListID
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        '.Visible = True
        .Open "GET", sURL, False
        .setRequestHeader "X-Access-Token", sToken
        .setRequestHeader "X-Client-ID", sUserName
        .setRequestHeader "Content-Type", "application/json"
        .send json1
        apiWaxLRS = .RESPONSETEXT
        sToken = ""
        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------------"
        Debug.Print .Status & " " & .StatusText
        .abort
    End With
    
apiWaxLRS = "{" & Chr(34) & "List" & Chr(34) & ":" & apiWaxLRS & "}"
'Debug.Print apiWaxLRS
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("List")
For Each rep In vDetails
    bCompleted = rep("completed")
    sID = rep("id")
    sDueDate = rep("due_date")
    sTitle = rep("title")
    lAssigneeID = rep("assignee_id")
    Debug.Print "--------------------------------------------"
    Debug.Print "Task Title:  " & sTitle & "   |   List ID:  " & sWLListID & " " & lAssigneeID
    Debug.Print "Completed:  " & bCompleted & "   |   Due Date:  " & sDueDate
    Debug.Print "--------------------------------------------"
Next

End Function

Click here after you’ve pasted your final code to pick up where you left off with “Get Tasks“.

Add Tasks to Wunderlist List

Click here after you’ve pasted your final code to pick up where you left off with “Add Tasks“.

Double-click anywhere in the following code area and press Ctrl + C to copy the entire code.

Function test()
Call fWunderlistAdd("test", "2019-04-29")
End Function


Function fWunderlistAdd(sTitle As String, sDueDate As String)
'============================================================================
' Name        : fWunderlistAdd
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fWunderlistAdd()
' Description : adds task to Wunderlist
'============================================================================

Dim sURL As String, sUserName As String
Dim apiWaxLRS As String, sURL As String
Dim sToken As String, sJSON As String
Dim lAssigneeID As Long, bStarred As String
Dim bCompleted As String, sWLListID As String

Dim rep, vDetails As Object
Dim Parsed As Dictionary
Dim sFile1 As String, sFile3 As String, sLine1 As String, sLine3 As String

sFile1 = "C:\other\3.txt"
sFile3 = "C:\other\5.txt"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

sWLListID = 999999999
lAssigneeID = 99999999
sUserName = sLine1
sToken = sLine3
bStarred = "false"
bCompleted = "false"

    sJSON = "{" & Chr(34) & _
        "list_id" & Chr(34) & ": " & sWLListID & "," & Chr(34) & _
        "title" & Chr(34) & ": " & Chr(34) & sTitle & Chr(34) & "," & Chr(34) & _
        "assignee_id" & Chr(34) & ": " & lAssigneeID & "," & Chr(34) & _
        "completed" & Chr(34) & ": " & bCompleted & "," & Chr(34) & _
        "due_date" & Chr(34) & ": " & Chr(34) & sDueDate & Chr(34) & "," & Chr(34) & _
        "starred" & Chr(34) & ": " & bStarred & _
        "}"
        
Debug.Print "sJSON--------------------------------------------"
Debug.Print sJSON
Debug.Print "RESPONSETEXT--------------------------------------------"
    sURL = "https://a.wunderlist.com/api/v1/tasks"
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", sURL, False
        .setRequestHeader "X-Access-Token", sToken
        .setRequestHeader "X-Client-ID", sUserName
        .setRequestHeader "Content-Type", "application/json"
        .send sJSON
        apiWaxLRS = .RESPONSETEXT
        sToken = ""
        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------------"
        Debug.Print "Status:  " & .Status
        Debug.Print "--------------------------------------------"
        Debug.Print "StatusText:  " & .StatusText
        Debug.Print "--------------------------------------------"
        Debug.Print "ResponseBody:  " & .responseBody
        Debug.Print "--------------------------------------------"
        .abort
    End With
Debug.Print "--------------------------------------------"
Debug.Print "Task Title:  " & sTitle & "   |   List ID:  " & sWLListID & " " & lAssigneeID
Debug.Print "Completed:  " & bCompleted & "   |   Due Date:  " & sDueDate
Debug.Print "--------------------------------------------"

End Function

Click here after you’ve pasted your final code to pick up where you left off with “Add Tasks“.

Disclaimer

**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.

Published by Erica L. Ingram

Having produced court transcripts for a decade, I understand how complex practitioners' needs are when it comes to crafting legal documents. If you're a solo practitioner or professional services provider, your time is sliced thin and I can help you alleviate that. To accomplish that, I'll be posting helpful hints and how-tos here on how to use VBA in Office to really stand out at work and do more with less (effort).