Articles in this section

See more

How-to: Use the Advanced Queries API with Google Sheets

  • Updated

With the Advanced Queries API, your team can automatically pull in traffic data to your other data analysis tools like Google Sheets, Excel, or Tableau. In this article, we'll cover how to create a very basic integration between Google Sheets and the Advanced Queries API to automatically populate your Chartbeat query results into your Google Sheet file instead of downloading the query CSV from your email each day to view in Excel. This is useful if you want more open access to the results of a report, or wish to streamline the process of importing your daily query data into a central repository, perhaps from multiple unique recurring queries.

 

Part A: Setting up your recurring query

1. In the Advanced Queries builder tool, select a query type of Recurring, along with the frequency of how often you'd like the report delivered to you. The selected frequency will also determine the query time range (e.g. a weekly delivered report will show data for the previous week).

Screen_Shot_2019-10-07_at_12.53.01_PM.png

2. Fill out the builder to meet your desired query and select Save Query at the bottom of the page

3. On the left-hand navigational menu, click Saved queries, then open the Recurring tab

4. On the right side of your query, open the 3-dot menu and click "Copy query ID". Make sure to paste this value somewhere accessible, as we'll need it for a later step.

Screen_Shot_2019-10-08_at_12.20.25_PM.png

 

Part B: Configuring the API call in your Google Sheet file

1. Navigate to Google Sheets and create a blank spreadsheet

2. In cell A1 (top-left), paste in the following URL and hit enter:

=IMPORTDATA("https://api.chartbeat.com/query/v2/recurring/fetch/?host={host}&apikey={apikey}&query_id={query_id}")

The /query/v2/recurring/fetch endpoint is where the results of a recurring query are generated. The IMPORTDATA() function in Google Sheets returns the data from an API call directly into your spreadsheet. 

3. You'll likely see an "#N/A" error in your cell, so let's correct that by replacing the bracketed values from the URL above (don't forget to remove the brackets too!) :

  • {host} - the domain name of the site you're querying. This should match the domain listed on the Saved queries page, under the recurring tab (e.g. ?host=faq.chartbeat.com)
  • {apikey} - your Chartbeat API key, which you can generate and retrieve here. (e.g. &apikey=1234abcd)
  • {query_id} - the Query ID retrieved from the Saved queries page (e.g. &query_id=e888a222-ac11-11ea-bce4-0a27e7a999be)

4. Once you've made these edits, click enter and you should see your data populate! 

icon_warning.pngNote
If you get an error message of "query scheduled, not yet run" simply wait up to 2 hours after your query has been created to repeat step 4 above. 

To refresh the daily/weekly/monthly data from your recurring query, simply click into the cell with your =IMPORTDATA() function (in our example, cell A1) and click enter, or refresh the browser tab of your Google Sheet. To learn more about setting up your sheet to automatically update, consult the Google documentation on Google Apps Scripts Editor (to set up both an update script and a trigger). 

For more information on the Advanced Queries API, please consult our API endpoint documentation. 

Related articles