Forum Discussion

NickMatthew's avatar
NickMatthew
Qrew Trainee
6 years ago

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

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? 

APIs and custom code

8 Replies

Sort By
  • NeilServices's avatar
    NeilServices
    Qrew Member
    6 years ago
    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
  • NickMatthew's avatar
    NickMatthew
    Qrew Trainee
    6 years ago
    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?


  • NeilServices's avatar
    NeilServices
    Qrew Member
    6 years ago
    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
  • NickMatthew's avatar
    NickMatthew
    Qrew Trainee
    6 years ago
    Fantastic. Thank you again Neil.  We're getting closer.

    My URL now looks like this:
    https://[companyname].quickbase.com/db/?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... 
  • NeilServices's avatar
    NeilServices
    Qrew Member
    6 years ago
    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
  • NickMatthew's avatar
    NickMatthew
    Qrew Trainee
    6 years ago
    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/

    ?a=API_DoQuery&fmt=structured&usertoken=[usertoken]&apptoken=[apptoken]