Google Sheets

Google Sheets is a cloud-based spreadsheet application that allows you to store and query data using a web interface. Evidence supports connecting to Google Sheets as a data source, allowing you to query Google Sheets using SQL.

Add a New Google Sheets Data Source

1. Start the Evidence dev server: npm run dev or Start Evidence using the VSCode extension.
2. Navigate to the settings page, also accessible via the ... menu in the top right.
3. Click the New Source button.
4. Select Google Sheets as the data source type.
5. Enter the configuration options for the connection.
6. Click the Test button to confirm the connection is successful.
7. Click the Save button to save the connection.
8. Your configuration options are saved in two files:
   • /sources/[source_name]/connections.yaml: Non-sensitive values, source controlled.
   • /sources/[source_name]/connection.options.yaml: Sensitive values, not source controlled, base-64 encoded.

Configuration

Adding data from Google Sheets requires a a service account.

To create a service account, see the BigQuery instructions.

1. Create a service account, and download the JSON key file
2. Give the service account access to your Google Sheet by sharing the sheet with the service account's email address.
3. Add the JSON key file to your Evidence app via the Settings page
4. In the connections.yaml file, add the sheet id (which can be found in the URL of the Google Sheet, after https://docs.google.com/spreadsheets/d/).

name: [your_source_name]
type: gsheets
options: {}
sheets:
   [your_workbook_name]: [your_sheet_id]

Query the sheet using the following syntax:

select * from [your_source_name].[your_workbook_name]_[your_tab_name]

Where [your_tab_name] is the name of the tab in your Google Sheet, with spaces replaced by underscores.