To connect your BigQuery account to Chartio, you’ll need to set up a Service Account and upload the generated key to Chartio.
Create Service Account
Log in to Google Cloud Platform and navigate to the project you want to use in Chartio.
In the sidebar, select IAM & admin and choose Service accounts.
Click Create Service Account.
Enter a name—you may want to name it Chartio so you can remember its purpose later—and under Role check BigQuery Data Viewer and BigQuery User in the BigQuery menu option. Ensure both roles are selected; if either are missing, Chartio will be unable to connect to your BigQuery data.1
Check the Furnish a new private key checkbox, and ensure JSON is selected under Key type. Click Create.
Save the JSON file to your computer.
- It is possible to use only the BigQuery User role but that service account would need to be added to each dataset individually.
Upload key to Chartio
In Chartio, select Data Sources > Add a Data Source > Google BigQuery, and upload the JSON file you downloaded in the previous step. Note: it can take a few minutes for Google to accept the JSON key. If you get an error uploading the JSON file to Chartio, wait a few minutes and try again.
Access Multiple Projects
To access additional BigQuery projects with a single Service Account, you’ll need to add the client ID to the additional projects from the Google Cloud Platform console.
From Google Cloud Platform, select IAM from the sidebar. Find the service account, and copy the member name.
Switch to another project you want to connect to Chartio, and click Add at the top of the page. In the Members field, paste the member name you copied earlier. Under Role, select BigQuery User and BigQuery Data Viewer.
Once this is finished, you can use the same JSON file you downloaded earlier to connect your second BigQuery project to Chartio.
Google Sheets via BigQuery
If you’ve added Google Sheets tables to your BigQuery project, you can query them in Chartio from your BigQuery connection after a few settings updates. Your BigQuery account must use a service account connection to enable this feature.
Enable Google Drive API in the project
Log in to your Google Cloud Console. Select your project from the top dropdown. From the left navigation, choose APIs & Services, then choose Dashboard. At the top of the page, click Enable APIs and Services. Use the searchbar to search for Google Drive API, select it, and click Enable.
Add service account client ID to the Google Sheet
From your Google Cloud Console, select IAM & admin from the left sidebar, then select Service accounts. Copy the value in the Service account ID column. If you don’t have a service account yet, follow the instructions above for connecting a BigQuery account.
Open your Google Sheet, and click on the Share button. Click Advanced at the bottom, and in the Invite people text input, enter the Service account ID value you copied earlier.