The connection and use for Google Sheets imitates that of regular databases.

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

In the Google Sheet, click "Share" to the top-right, choose the access level and click "Copy link":

You can now add your sheet as a database in PushMetrics/d/d/connect.

The Connection name corresponds to the name of your "database" and the names of your sheets to "tables".

Private Sheet

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.