Google Sheets
The connection and use for Google Sheets imitates that of regular databases. PushMetrics allows you to connect to Google Sheets using SQL queries. The setup process depends on whether your sheet is public or private.
This connection will allow you to run SQL Lite commands against a google sheet.
A connection to a private sheet is similar to the connection process in the BigQuery connection and needs a Google Service Account with according permissions.
The Writeback Block will only work with a private Google Sheets connection.
Connecting to a public sheet needs no further permission can be done by simply adding the Google Sheets public url to the connector.
Public Sheet
What You Need
- A publicly accessible Google Sheet
Setup Steps
1. Share your Google Sheet publicly:
- Open your Google Sheet
- Click Share (top-right corner)
- Choose "Anyone with the link" and set permission to "Viewer"
- Click Copy link
2. Add to PushMetrics:
- Go to PushMetrics/d/d/connect
- Click Add Database
- Select Google Sheets
- Paste your public sheet URL
- Give your connection a name (this becomes your "database" name)
- Click Save
3. Query your data:
- Sheet names become "table" names
- Use SQL to query: `SELECT * FROM "name_of_sheet"`
Note: Public sheets are read-only and cannot be written to from PushMetrics.
Private Sheet
For private Google Sheets, you need to set up authentication using a Google Service Account. There are two approaches:
Approach 1: Service Account with Sheet Sharing (Recommended)
Best for: Most organizations, individual users, and teams who want simple, secure access.
How it works: You create a service account (which gets an email address), then share your Google Sheets with that email address—just like sharing with a colleague.
Pros:
- ✅ More secure - Only sheets you explicitly share are accessible
- ✅ Easier IT approval - Doesn't require broad permissions
- ✅ Better audit trail - Clear record of which sheets are shared
- ✅ No admin rights needed - Anyone can share sheets
- ✅ Works with personal Google accounts - Doesn't require Google Workspace
Cons:
- ❌ Requires manually sharing each sheet
- ❌ Users must know the service account email address
To allow access to a private spreadsheet, a number of steps are necessary
In Summary:
- Create a Project in Google Cloud
- Create a Google Service Account
- Generate JSON Keyfile
- Enable Domain-wide Delegation
- Enable Google APis
- Use the Keyfile to connect to PushMetrics
Creating a Google Cloud Project
Navigate to the projects view of the Google Cloud Console, and create a project:
- Click "Create Project" to the top right or use an existing one.
The name of your project is used an identifier.
Organization and Location should be selected automatically, if your account belongs on to an organization.
Create a Google Service Account
If you are not redirected to your project, click here.
- Click "Create Service Account"
- Choose a Name for the Account and the ID will be generated.
- Grant access to the new Account by choosing "Basic" and "Owner"
- Click "Done" to finish the setup of your Service Account.
Generate JSON Keyfile
The content of the JSON KeyFile is what we need to paste into the connection form to authorize the connection.
- Click on the link to your Service Account
- Take note of the Unique ID here as you will need it later.
- Choose the "KEYS" tab
- Click on "Create new"
- Choose JSON and click CREATE
You now have the KeyFile and will need to give some further permissions to enable access to your Google Sheets.
Domain-wide Delegation
For this step you have to be Google Admin.
Head to https://admin.google.com/ac/owl/domainwidedelegation?hl=en_GB
- Click "Add new"
- Add your Client ID from above
- Add the followin scopes
- https://www.googleapis.com/auth/drive.readonly- https://www.googleapis.com/auth/spreadsheetsEnable Google APIs
Finally you will have to enable the APis for Google Sheets and Google Drive.
Head to the following two links and click "Enable".
https://console.cloud.google.com/apis/api/sheets.googleapis.com/overview
https://console.cloud.google.com/apis/library/drive.googleapis.com
Connect to PushMetrics
You can now paste the contents of your JSON KeyFile into the Service Account field in the connection form.