Welcome to this article where we will discuss how to connect to a private Google Sheet shared with restrictions, with a focus on sharing a Google Sheet at the organizational level. To learn how to connect Preset to a public Google Sheet without restrictions, please refer to Google Sheets (Public).
Setting up Google (Steps 1 to 4)
The first step is to select an existing Google project or create a new one. To do this, go to the Google Cloud Console platform.
If you are visiting the Console for the first time, please select your country and accept the terms of use.
The Console will display all existing projects. You can select an existing project if you wish.
To create a new project, select “Create a project”.
In the “Project name” field, enter a name for your project, which will become your project ID.
If the Google account you are logged in with belongs to an organization (i.e., not gmail.com), an “Organization” field will appear and your organization’s domain will appear by default.
If necessary, select a location in the “Location” field. Once done, select “Create”.
After creation, the Service Accounts screen appears.
Creating and Configuring a Google Service Account
In this step, we will create and configure a new Google service account.
Let’s start by selecting “+ Create a service account”.
In the “Service account name” field, enter a memorable name for the service account. The “Service account ID” field will automatically populate based on the set account name.
In the “Service account description” field, enter a brief description of the service account.
Once done, select “Create and continue”.
In this step, you need to grant access to the service account.
In the “Select a role” field, please select one of the following options:
- “Editor”: Modify access to all service account resources.
- “Owner”: Full access to all service account resources.
- “Viewer”: Read access to all service account resources. If you select “Viewer”, please note that you will not be able to download CSV files.
Once ready, select “Done”.
The last step, which is granting access to users, is optional. You can skip it by selecting “Done”.
Creating and Saving a JSON Key
A table displaying your new Google service account, which we named “Preset Connections,” will appear.
Our next step is to create a private key in JSON format that will be downloaded to your local system. This key will be used later when we configure the connection to the database in Preset.
In the table, under the “Actions” column header, select the vertical “ellipsis” icon, then choose “Manage keys”.
The service account details screen appears with the “Keys” tab selected. In the “Add key” dropdown menu, select “Create new key”.
The “Create private key” window appears. By default, the JSON key type is selected. Make sure this is the case, then select “Create”.
The key, in JSON file format, will be downloaded to your local system. Be sure to save this file.
Great! We have created a project, a service account, and now we have a JSON key! In the next step, we will grant domain-wide authority to the service account.
Delegating Authority at the Domain Level
In this step, we will delegate authority at the domain level (i.e., automatically granting the service account access to user data on the Google Workspace domain) and configure Open Authorization (OAuth) in terms of access scopes to Google services.
To enable domain-wide delegation, open a new tab in the browser and go to this link to manage domain-wide delegation on your Google tenant. Click on “Add new item” next to API clients.
In the “Client ID” field, paste the “Unique ID” of your “Service Account”.
Add the following OAuth scopes to allow access to these Google services:
https://www.googleapis.com/auth/spreadsheets.readonlyhttps://www.googleapis.com/auth/drive.readonlyhttps://spreadsheets.google.com/feeds
Click on “Authorize”.
Activating APIs
Great news, we’re almost done with our Google configurations! The final step is to activate the APIs for Google Sheets and Google Drive.
First, make sure you are logged in with the correct Google account.
Go to the Google Sheets API screen in the Google Cloud Console platform, then select “Enable”.
Next, go to the Google Drive API and select “Enable”.
Now it’s time to focus on configuring the database and dataset on Preset.
Configuring the Database Connection in Preset
To get started, we need to connect a database to Preset.
In the toolbar, hover over the “+” icon, then “Data”, and select “Connect a Google Sheet”.
The “Connect a database” window appears.
In the “Display name” field, enter a meaningful name for the new connection to your private Google Sheet. By default, the field is filled with the name “Google Sheets” (for this example, we used the display name “Spatial Data”).
In the “Allowed Google Sheet types” field, select “Publicly shared and private sheets”.
Next, we will provide Preset with the JSON authentication information you downloaded earlier in “Creating and saving a JSON key” in step 2.
Option 1: Download JSON File
In the “How would you like to enter the service account credentials?” field, select “Download JSON file”, then select “Choose a file”.
Navigate to the JSON file you downloaded earlier, then select it to upload it to Preset. The JSON file name will appear, and you can select the trash bin icon to delete the file if needed.
Option 2: Copy and Paste JSON Code
Alternatively, you can directly paste your JSON code.
In the “How would you like to enter the service account credentials?” field, select “Copy and paste JSON credentials”. The “Service account” field will appear.
Copy your JSON code in its entirety, then paste its content into the “Service account” field.
Take a moment to open a new tab and access your private Google Sheet. Copy the link to your restricted Google Sheet to your clipboard.
Connecting the Google Sheet as a Table
Our next step is to connect the Google Sheet as a table to the database.
In the “Google Sheet name” field, enter a name for a table – in this example, we named our table “T4”.
In the “URL” field, paste the link to the Google Sheet from your clipboard.
To create additional tables, select “+ Add sheet” and repeat the process.
When you’re done, select “Connect”.
The 3 out of 3 step will appear. At this stage, you can optionally configure “Advanced” options if needed. To learn more, please refer to the optional settings.
To finalize the connection, select “Finish”. Great job!
Adding a Dataset to Preset
Our final step is to add the table/dataset to Preset. This will allow you to create new charts from your Google Sheet data.
Start by going to “Datasets”. From the Data screen, simply select the “Datasets” tab.
Next, select “+ Dataset”.
The “Add dataset” panel appears.
In the “Database” field, select the name of the database you defined earlier in the “Display name” field (e.g., “Spatial Data”).
In the “Schema” field, select a schema (if applicable).
In the “View table schema” field, select a table you previously created (e.g., “T4”).
Select “Add”.
Great, you’re now ready to start creating charts from your Google Sheet data!
Conclusion
Great job! You have now connected Preset to a private Google Sheet.
Ready to create a chart? Check out the “Create a chart” section to learn more about selecting a chart.
Once you’ve selected your preferred visualization, we recommend taking a look at chart configuration and exploration interface to learn more.
