Another API – this time for Quickbase

A few months ago I developed a program that worked with Dropbox’s API to gather data about Dropbox’’s folders. But since we will be transitioning from Dropbox to Sharepoint, I did not develop it much further than obtaining data. However, it was a good learning experience and practice because it got me acquainted with the API concept.

If you want to skip directly to the code, it can be found at the bottom of this post.

Why Am I Even Concerned About API?

Backstory

We’re transitioning into a new set of tools in our company.

Now I’m working with Quickbase, Power BI and eventually SharePoint ( which I used to use a long time ago), and the concept of API will come in handy. Currently our managers use an excel file with macros to pull in data from a vendor and to spit out a report upon closing the file. I then use another macro to collect all of the reports for consolidation reporting. Now, we want to see if we can develop something more interesting and useful for our customers; hence my current forays into Power BI.

Right now I’m thinking of using Quickbase as our database and Power Bi as our visualization tool. The other part of our business uses Quickbase and Tableau, and I want to piggy back on what they have done. A couple of times, I’ve tried to use that group’s vendor source data to pull into our side’s consolidation reporting because we have some properties that use that vendor source of data, different from what most units on our side use. The guy who ran the Quickbase and reporting always said that he could easily develop the report in his Quickbase, and but then never follow up and it would just die. I would never get examples of the source data to use and the whole thing would never get anywhere. He has bigger issues to do with but I thought, “If he didn’t have time, just own up to it and let someone else do it.”

Now, we’re going to try to get both sides of the business using the same tools: Quickbase and Power BI. I guess Tableau was determined to be prohibitively expensive – yes, it’s expensive and I was shocked our company was using it. Anyway, with some push from my manager, I was given some access to Quickbase – a tiny piece of it – and total access to Power BI. So I’ll be focusing on those two tools and it’s quite likely some of my posts will center on those two.

The Pain Point

Right now I’m trying to determine what each tool (excel, excel vba, Quickbase, Power BI Desktop, Power BI Service, and later Sharepoint) can and cannot do. I’m also considering the fact that our field managers are apprehensive of using new tools since their success depends on their ability to use those tools. I’ve already heard that they know changes are coming and two have expressed concerns. One said, “I don’t like change.” With that in mind, I need to make sure the process remains relatively simple without too many touchpoints – preferably only one touchpoint and with something they are familiar – excel. These field managers are not comfortable with computers and definitely don’t know excel other than what they are doing now with my program.

They already know how to pull data from their vendor source and already know how to use the excel file with the macro. Now I’m trying to see how I can push the data from that excel file into Quickbase and thence into Power BI and eventually into Sharepoint without them being aware what is happening.

Hence the interest in APIs.

My Problem Solving Approach

To develop the program using Quickbase’s APIs, I started from the program that used Dropbox’s APIs. In Dropbox’s case, I was pulling information from Dropbox, whereas in Quickbase, I want to push data into a table. The advantage with Dropbox was that they had an API Explorer where you could “explore” how the API codes should be written. I didn’t have anything similar in Quickbase and the code examples did not look similar to Dropbox. Quickbase uses either xml or the URL approach; I vaguely remember Dropbox being accessible to many languages but showed only 4 languages as examples. But despite the differences, I started from the Dropbox version I had developed last December and worked from there. I started out trying to pull data from Quickbase and I very quickly had success, once I figured out how to use the URL approach.

From what I can tell, the URL approach scrunches all of the information into one (gigantic) line of code whereas the other versions break up the instructions into multiple lines.

Gathering data from Quickbase was easier than pushing data into Quickbase. My hold up was the syntax for the fields. I played with various versions until I figured out where I was going wrong. It looks like basically 4 or 5 lines (outside of the dimensioning of variables and variables set up) do all of the work.

There is one final puzzle that I need to figure out and that is the user token (a kind of user id and user password). Can I just use one user token or do I have to create a user token for each manager? That remains to be seen.

Sub DataFromDropbox()
Dim xmlhttp As   Object             
Dim myurl As String  
Dim objRange As Range              
Dim lastcol As Integer  
Dim empname As String  
Dim empemail As String  
       
Application.DisplayAlerts = False  
Application.ScreenUpdating = False  
Application.EnableEvents = False  
       
     'STEP 1: Insert temporary sheet to gather data from Dropbox  
     Sheets("List of emails").Activate  
     Sheets.Add(After:=ActiveSheet).name ="Temp"  
       
     STEP 2: Open up API and pull information on shared folder members  
     Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")  
     myurl = "https://api.dropboxapi.com/2/sharing/list_folder_members"  
           
     argumentString = "{ " & Chr(34) &   "shared_folder_id" & Chr(34) & ": " & Chr(34)   & "????????????" & Chr(34) & " }"  
               
     xmlhttp.Open "POST", myurl, False         
     xmlhttp.SetRequestHeader "Authorization", "Bearer   ??????????????????????????????????????????????????????????????"  
     xmlhttp.SetRequestHeader "Content-Type", "application/json"  
           
     'xmlhttp.Send           'when you don't have data to send  
      xmlhttp.Send argumentString  
           
      'MsgBox (xmlhttp.ResponseText)  
           
      'List the results  
      Sheets("Temp").Range("A3") = xmlhttp.ResponseText  
       
      STEP 3: Delete temporary sheet  
      Sheets("Temp").Delete  
               
Application.DisplayAlerts = True  
Application.ScreenUpdating = True  
Application.EnableEvents = True  
           
MsgBox ("Program done")  
       
End Sub   

Sub DataFromQuickbase()  
Dim xmlhttp As Object                 
Dim myurl As String  
Dim objRange As Range                   
Dim lastcol As Integer  
Dim PropName As String  
'Dim DateFd As Date  
Dim DelRent As Currency  
Dim DelUtility As Currency  
Dim TotalDel As Currency  
Dim Collect As Currency  
           
Application.DisplayAlerts = False  
Application.ScreenUpdating = False  
Application.EnableEvents = False  
       
     'STEP 1: Gather the data into variables  
     PropName =   Sheets("Data").Range("A2")  
     DateFd =   Sheets("Data").Range("B2")  
     DelRent =   Sheets("Data").Range("C2")  
     DelUtility =   Sheets("Data").Range("D2")  
     TotalDel =   Sheets("Data").Range("E2")  
     Collect =   Sheets("Data").Range("F2")  
           
     'STEP 2: Open up API and create URL with data info  
     Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")  
     myurl = "https://domain.quickbase.com/db/xxxxxxxxx?a=API_AddRecord&usertoken=???????????????&_fid_6=" & PropName & "&_fid_7=02-03-2019&_fid_8=" & DelRent & "&_fid_9=" & DelUtility &   "&_fid_10=" & TotalDel & "&_fid_11=" & Collect  
           
     xmlhttp.Open "GET", myurl, False       
           
     xmlhttp.Send           'when you don't have data to send  
     'xmlhttp.Send argumentString  
           
     MsgBox (xmlhttp.ResponseText)  
           
Application.DisplayAlerts = True  
Application.ScreenUpdating = True  
Application.EnableEvents = True  
           
'MsgBox ("Program done")  
       
End Sub