How to I pull data from QB applications using an API URL?

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
  • (Edited)
Hi! I'd like to pull data from a QuickBase application into a GoogleSheet for the purpose of allowing coworkers to quickly view, edit, modify, and visualize data within their Google Drive. 

There are two ways to do this from what I'm aware: using "=importXML()" within GoogleSheets, or, ideally, create a Google Script that would allow me to automate the process.

In either case, I believe I need to have a functional API URL to access the data from an app. The challenge I have, however, is that the only URL that *doesn't* produce an error is one that logs into my "main" page (and doesn't provide the data I need). 

https://[companyname].quickbase.com/db/main?a=API_Authenticate&username=[xxxx]&password=[xxxx]&hours=24&API_DoQuery&fmt=structured


The repsponse I get from QuickBase: 
<qdbapi>
<action>API_Authenticate</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<ticket>
[XXX]
</ticket>

<userid>[XXX}</userid>
</qdbapi>
Awesome! No error. That's great. But I'm not getting data. So let's try logging into the database/app from which I need data.

https://[companyname].quickbase.com/db/bnxtq4n47?a=API_Authenticate&username=[XXXXX]&password=[XXXXX]&hours=24&API_DoQuery&fmt=structured

<qdbapi>
<action>API_Authenticate</action>
<errcode>5</errcode>
<errtext>No Such Operation</errtext>
<errdetail>
This operation is not defined for applications, however, the URL contained an application ID. Please check the URL.
</errdetail>
Hmm.... That's not great. Why doesn't API_Authenticate work for /main/ but not for the application/table from which I want to get data? 

Photo of Nick Matthew

Nick Matthew

  • 120 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Nick
Re-read your issue.  Fuller explanation.  Authenticate is performed against the db/main.  If you want to use a single url you will have to use a user token.  You can create a user token for yourself in a separate step, then just past the clause &usertoken=xxxxx into the do query.
So the url would look like 
https://[companyname].quickbase.com/db/bnxtq4n47?a=API_DoQuery&fmt=structured&usertoken=xxxx
Explanation of user tokens is here:
https://help.quickbase.com/user-assistance/create_user_tokens.html
Neil
Photo of Nick Matthew

Nick Matthew

  • 120 Points 100 badge 2x thumb
Neil! Someone replied! Amazing. 

Thank you for feedback. We seem to be getting closer, but now we have a new error prompt.

https://[companyname].quickbase.com/db/bnxtq4n47?a=API_DoQuery&fmt=structured&usertoken=[xxxx]

I used a usertoken from my QuickBase preferences, and the error I now get is:

<qdbapi>
<action>API_DoQuery</action>
<errcode>14</errcode>
<errtext>Missing DBID or DBID of wrong type</errtext>
<errdetail>
This operation is only supported on individual tables and not on the application itself. Please check the URL.
</errdetail>
</qdbapi>
Okay, so perhaps I am using my application DBID instead of my table DBID. So after doing some research, I find that this URL will pull a list of table IDs:

https://[comanyname].quickbase.com/db/bnxtq4n47?act=API_GetSchema&apptoken=[xxxx]
<table_id>bnxtq4n47</table_id>
<app_id>bnxtq4n47</app_id>
Cool! So it seems that the Table ID and App ID are the same... So how do I access data in my app/table if I get an error 14?


Nick,
App id's should be different from table id's.  The best way to check is go to the app, click on home icon.  The id in the url should be the app id.  Then click on the icon for the table.  The url should be the table id.  Alternatively click home icon - click settings - click App Management - click Show Support Information (under show app info).  You'll get a list of all of the tables with their DBID's + the DBID of the App.
Neil
Photo of Nick Matthew

Nick Matthew

  • 120 Points 100 badge 2x thumb
Fantastic. Thank you again Neil.  We're getting closer.

My URL now looks like this:
https://[companyname].quickbase.com/db/[tableID]?a=API_DoQuery&fmt=structured&usertoken=[XXXXX]&apptoken=[XXXX]

When I put it into Chrome, it works perfectly, and produces a beautiful XML output.

When I test this same URL in Chrome's incognito (or, alternatively, if I put it into "=importXML()" in GoogleSheets) I get this error:
<qdbapi>
<action>API_DoQuery</action>
<errcode>83</errcode>
<errtext>Invalid error code: 83</errtext>
<errdetail>User token is invalid</errdetail>
</qdbapi>
That' can't be.... When I return to my user preferences, my User Token is exactly the same as the URL (the same URL that works in Chrome, but not incognito). Nothing's changed... 
Nick,
When you use incognito the browser doesn't save cookies - which is where QB saves login information.  I suspect that QB needs something.  Do you have any issues if you use Firefox?
Neil
Photo of Nick Matthew

Nick Matthew

  • 120 Points 100 badge 2x thumb
Success! Thank you again Neil.

The last change I needed to make was to return to the admin settings, and ensure my user token ALSO gave me access to the new app I created to test API pulls.

https://[companyname].quickbase.com/db/[TableID]?a=API_DoQuery&fmt=structured&usertoken=[usertoken]&apptoken=[apptoken]


It's all in the details - congratulations..