Import Quaderno Data to Google Sheets

In this guide, we’ll walk through how to pull data from the Quaderno API directly into Google Sheets, using the API Connector add-on for Sheets. Quaderno is a tool for automatically calculating sales tax in different jurisdictions. We’ll first get an API key from Quaderno, and then set up a request to pull in invoice data to your spreadsheet.

CONTENTS

• Before Your Begin
• Step 1: Get your Quaderno API Key
• Step 2: Create your API Request URL
• Step 3: Pull Quaderno API Data into Sheets
• Step 4: More Example API URLs
• Step 5: Handle Pagination

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

STEP 1: GET YOUR QUADERNO API KEY

1. While logged into your Quaderno account, click your Profile icon > API keys from the header bar.
   
2. Copy down your Private Key. That is the only value we need here.
   
3. One last step: Because the Quaderno API requires Basic Authentication, we need to encode our authentication info to base 64. You can do this by entering your your credentials in the format {Private key}:x into this form (i.e. your private API key, then a colon, and then an ‘x’). Your encoded credentials will appear underneath.

   The encoding script runs in your browser, and none of your credentials are seen or stored by this site.

   Base64 Encode

    

    

   (If you have any problems with the above, please check this post for some alternate methods of encoding your credentials).

STEP 2: CREATE YOUR QUADERNO API REQUEST URL

We’ll follow the documentation to access a list of invoices.

• API root: https://YOURSITE.quadernoapp.com/api
• Endpoint: /invoices.json

Putting it together, we get the full API Request URL:

 https://YOURSITE.quadernoapp.com/api/invoices.json

Just enter in your own subdomain.

STEP 3: PULL QUADERNO API DATA INTO SHEETS

We can now enter our values into API Connector and import Quaderno API data into Google Sheets.

1. Open up Google Sheets and click Add-ons > API Connector > Open.
2. In the Create screen, enter the Request URL we just created
   
3. Under Headers, enter Authorization as your Key, and Basic {base64-encoded string} as the value:
   

   Authorization

   Basic BASE64_ENCODED_STRING

   Replace BASE64_ENCODED_STRING with the base64-encoded string you created in part 1.
   

4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see a list of your Quaderno invoices in your Google Sheet:

STEP 4: MORE EXAMPLE API URLS

You can check the documentation for the full list of available API requests,  but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):
https://YOURSITE.quadernoapp.com/api/contacts.json
https://YOURSITE.quadernoapp.com/api/receipts.json
https://YOURSITE.quadernoapp.com/api/credits.json
https://YOURSITE.quadernoapp.com/api/expenses.json
https://YOURSITE.quadernoapp.com/api/expenses.json
https://YOURSITE.quadernoapp.com/api/estimates.json
https://YOURSITE.quadernoapp.com/api/recurring.json
https://YOURSITE.quadernoapp.com/api/items.json
https://YOURSITE.quadernoapp.com/api/payments.json

STEP 5: HANDLE PAGINATION

Quaderno limits the number of records returned in each request. By default, only 25 records will be returned unless you use the ‘limit’ and ‘page’ parameters as described in their documentation.

 To access more than 25 records, add the ‘limit’ and ‘page’ parameters like this:

page 1: https://YOURSITE.quadernoapp.com/api/contacts.json?limit=100&page=1

page 2: https://YOURSITE.quadernoapp.com/api/contacts.json?limit=100&page=2

With API Connector, you can either set these up as separate requests or loop through them automatically with pagination (paid feature) for added efficiency, like this: