Getting started with Google Sheets

Overview

This article describes the prerequisites for working with Google Sheets and explains how to write data to and consume data from Google Sheets.

Prerequisites

You need a Google Cloud Project in order for Semarchy xDI to connect to Google Sheets. Configuring such a project involves enabling the Google Sheets API, configuring the OAuth consent screen, and generating OAuth client credentials.

You will configure the JDBC URL with the OAuth2 token to perform operations on Google Sheets from Semarchy xDI.

Create a Google Cloud project

To create a Google Cloud Project:

Open your browser and log in to your Google account.
Navigate to the Google Cloud Console.
In the header, click the Select a project drop-down list.
In the top-right corner, click New project.
The Project name is generated automatically. Optionally click Edit to change it.
In the Organization field, select the organization in which you create the project.
In the Location field, click Browse and select the folder that will contain your project.
Click Create.

Your project is created.

Enable the Google Sheets API

The Google Sheets API allows Semarchy xDI to communicate with Google Sheets.

To enable the Google Sheets API:

Navigate to the Google Cloud Console, and select the project you created.
In the navigation drawer, select APIs & Services.
Click Enable APIs and Services in the page header. The API library opens.
Search for Google Sheets API.
Select Google Sheets API in the results page.
Click Enable.

The Google Sheets API is now enabled for your project.

Configure the OAuth consent screen

The OAuth consent screen allows you to add information about Semarchy xDI and specify Google Sheets as the application accessed by Semarchy xDI.

To configure the OAuth consent screen:

Navigate to the Google Cloud Console, and select the project you created.
In the navigation drawer, select APIs & Services.
In the left menu, click OAuth consent screen.
In the User Type section, select Internal.
Click Create. The Edit app registration dialog displays.
At the OAuth consent screen step, in the App information section, enter the name of the application and select the user support email.
Optionally add a logo.
In the Developer contact information section, type the email address of the developer who should be contacted for this project.
Click Save and continue.
At the Scopes step, click Add or remove scopes. The Update selected scopes panel opens.
Search for Google Sheets API and select the scope with the See, edit, create, and delete all your Google Sheets spreadsheets description.
Click Update at the bottom of the panel.
Click Save and continue.

The scope is added.

Generate OAuth client credentials

OAuth client credentials are required by Semarchy xDI to access the user data in Google Sheets.

To generate OAuth client credentials:

Navigate to the Google Cloud Console, and select the project you created.
In the navigation drawer, select APIs & Services.
In the left menu, click Credentials.
Click Create credentials > OAuth client ID in the page header
From the drop-down list, select Desktop app.
Enter a name that will be used to identify Semarchy xDI on the Google Cloud platform.
Click Create. A dialog containing the client ID and client secret displays.
In the dialog, click Download JSON.
Click OK.

The OAuth credentials are now created. You will need them to generate a JDBC URL.

Generate an OAuth2 token from Semarchy xDI

Follow these steps to generate an OAuth2 token:

In a command line, and go the directory containing the stambia.jdbc.udriver.gsheets.jar file.

This file is located in the Google Spreadsheet directory in the location of the modules, typically in the modules directory under the runtime installation directory.

Run the following command:

java -cp stambia.jdbc.udriver.gsheets.jar;../../lib/core/*;../core/com.semarchy.xdi.base.core.jar;. com.stambia.udriver.googlesheets.oauth.Oauth2Tool

At the following prompt, enter y and then press Enter:
```
Oauth2 (y/n):
```
Enter the client ID and client secret. This information can be found in the JSON file you downloaded at step 8 of Generate OAuth client credentials.

Enter 1 and then press Enter to specify the scope for the token:

What kind of Scope should be used for the token?
1 - spreadsheet
2 - custom (user specified)

A URL appears in the console and you are asked to enter a code. Copy and paste the URL into your browser.
The Google authentication screen displays. Select the account you used to create the Google Sheets project.
Click Allow to confirm access.
An error page appears, saying that the web page cannot be reached at a specific URL.
Locate the code in the page URL, copy and paste it into the console, and press Enter.

Code is the content between ?code= and &scope.
URLs with a refresh token and an access token display. Save these URLs to a file.

Create metadata

To create the metadata:

Right-click the project folder, then select New > Metadata.
At the New Metadata window, select Google Sheets and click Next.
Enter a name for the data model and click Next.
Select the Google Sheets module and click Finish.

Do not select the Google Sheets API V3 module. It is deprecated.

The Server Wizard appears to configure Google Sheets connectivity.
Clear the URL field and paste the URL with the refresh token that you generated at step 11 of Generate an OAuth2 token from Semarchy xDI.
Leave the User and Password fields empty and click Connect.
Click Next.
In Catalog Name, enter the code representing the Google Sheets document that you want to reverse engineer. This code can be found in the URL of the file between /d/ and /edit.

For example, in the following URL the highlighted part represents the code:

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0
Click Next.
Click the Refresh button next to the Object filter field. The sheets from the Google Sheets document are listed.
Select the sheet that you want to reverse engineer.
Click Finish.

The Google Sheets file has been reverse-engineered.

Create mapping to write to a Google Sheets file

The following example illustrates a mapping used to write data from a database table to a Google Sheets file.
getting started spreadsheet write mapping

Create mapping to read from a Google Sheets file

The following example illustrates a mapping used to read data from a Google Sheets file and write it to a database table.
getting started spreadsheet read mapping