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.


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.

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:

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:

  1. Create a Project in Google Cloud
  2. Create a Google Service Account
  3. Generate JSON Keyfile
  4. Enable Domain-wide Delegation
  5. Enable Google APis
  6. 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/spreadsheets

Enable 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.