Google Sheets
The connection and use for Google Sheets imitates that of regular databases.
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
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:
- 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/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.