Create a service account
To connect Mode to your BigQuery project, you will need to create a service account for Mode to use.
Navigate to the Google Developers Console. Before proceeding, click on the drop down to the left of the search bar and select the project to which you want to connect.
NOTE: If you do not see the project you would like to connect to listed in the drop down, click on the account switcher in the upper right corner of the window and ensure you are logged in to a Google account that is a member of the project.
Click on the hamburger menu in the upper left and select IAM & Admin and then Service accounts. Click the Create service account button.
Give your new service account a name. Under Project Role, add only the BigQuery Data Viewer and BigQuery Job User roles. Check Furnish a new private key and select P12 as the key type. Finally, click Save to create your service account and download your private key file. Save the private key file to a secure place where you can easily retrieve it as you will need it in a subsequent step.
IMPORTANT: We suggest adding only the BigQuery Data Viewer and BigQuery Job User roles to your service account. Adding other roles to your service account may prevent Mode from being able to connect.
A dialogue box will display the password for your newly generated private key. Store this password in a secure place. You will not need this password to connect Mode to BigQuery. Click Close to dismiss the dialogue box. Note the email address listed for your newly created service account as you will need it in a subsequent step.
Return to Mode, and begin the process to connect a BigQuery database. When you see the form to enter your BigQuery credentials, first enter a name for the new database connection.
In the Project ID field, enter the BigQuery project ID. It can be found in the URL of your Google Developers Console. The URL should be structured like this:
In the Service Account Email Address field, add your service account’s email address that you noted in step 4.
Under the Key field, click Choose File and select the private key file you downloaded in step 3.
Click Connect to complete the connection.
Standard vs. legacy SQL
When connecting to BigQuery, you will choose either legacy SQL or standard SQL as the default SQL dialect for that connection. Most users choose standard SQL, which is similar to industry standard SQL dialects used by other databases.
Regardless of your choice as the connection’s default, users can specify which dialect they would like to use on a query-by-query basis by including either
#legacySQL in the Query Editor.
An organization admin can change the default SQL preference a BigQuery connection at any time by following these steps:
- Navigate to the Mode home page
- Click on your name in the upper left corner of the window.
- Click Organization Settings.
- Under the Data section, click Connections.
- Click on the BigQuery connection you want to update.
- In the upper right corner of the page click on Settings
- Toggle standard SQL on or off.
- Before your settings are updated, you will need to re-upload your service account’s P12 key. Learn more about BigQuery service accounts and P12 keys.
- Click Save Changes.
IMPORTANT: Take care when changing the default SQL dialect for an existing connection, as any queries written using that connection that are written in the previous default SQL dialect may no longer work.
Use BigQuery to query Google Sheets
You can use Mode to query Google Sheets in BigQuery. The first step, enabling your Google Drive API, only needs to be done once.
Enable Google Drive API
Navigate to the Google Developer Console and select your project from the project dropdown menu. From the left menu, click on Library. Under Google Apps API, click on Drive API to enable the API from that page.
Create a table in BigQuery from your Google Sheet
Go to the BigQuery Web UI. In the navigation, hover over a schema name and click the down arrow icon next to the name. Choose Create new table.
Select Google Drive for the new table’s location and paste the Google Sheet URL (not the shared link but the actual URL you use to view the Sheet) into this field. Choose Google Sheets as the Format. Then add all the column names and types from your Google Sheet under Schema. Click Create Table when ready.
Enable Google Sheets for Mode Use
- For Mode to query your new table, share your Google Sheet with the service account email address you used to connect Mode to BigQuery. You can also share folders in your drive with this email address, which will allow Mode to query every sheet in that folder.
- To get your service account email address, go to your Google Cloud Console, switch to your Project, and click on Service Accounts. Copy the service account email address you used to connect Mode to BigQuery.
- To share a sheet or folder, click Share on the Google Sheet or in the folder, and paste the service account email address into the Share email field.
- Note that BigQuery schemas don’t refresh automatically in Mode. To see this table appear in your schema browser, go to Organization Settings and click Connections under the Data section. Choose your Big Query connection, and click the green Refresh button in the upper right corner to update the schema browser in Mode.
Last updated August 21, 2020