Skip to Content

Google Sheets using JWT Authentication Connection Guide

Overview

This guide describes the system requirements and instructions for connecting Vinyl to a Google Sheets database server using JWT authentication. The JWT authentication method bypasses the user consent screen.

This connection type supports bidirectional access to data, including Vinyl CRUD business rules.

To connect your servers, you must have Vinyl 3.2 installed.

System Requirements

  • Google Sheets Database Server

  • JWT

Client Web Browser

  • Chrome: 84+

  • Firefox: 73+

  • Safari: 13.0.1+

  • Edge: 84+

Limitations and Supported Features

To use this connection, we recommend having Vinyl version 3.2.32441+ installed.

Connection Instructions

Configure Google Sheets from Google Console

  1. Navigate to Google Console: https://console.cloud.google.com/apis/dashboard

  2. Enable Google Sheets API by clicking on Enable APIs and Services and searching Google Sheets

  3. From the dashboard, setup OAuth under Credentials

    attachment

  4. Click Create Credentials at the top of the screen and choose Service Account

  5. Give the service account a name, and click the Create and Continue button

    attachment

  6. Assign the role of Owner, then click the Done button on the bottom of the page

    attachment

  7. Now that the service account has been created, we need to generate a JWT. Staying on this Service Accounts page, click on the service account we just created. In this example it's "Google Sheets".

    A screenshot of a computer Description automatically generated with low confidence

  8. On the service account page, navigate to the Keys tab on the top of the page. Then click on Add Key and then Create New Key.

    attachment

  9. You will see a pop up asking what format you want the key to be in, select JSON. The JSON key will download automatically.

    attachment

  10. Open the Google Sheet you are attempting to connect to, and share access with it to the service account just created

    attachment

Configure JWT File on App Server

  1. After downloading the JWT file, navigate to the app server your Vinyl instance resides on

  2. We need to place the JWT file in the Vinyl directory or folder, in this example it resides in C:\inetpub\wwwroot\Vinyl\, this may differ depending on how your instance is set up.

  3. Copy the JWT file and place it in the Vinyl directory

  4. Once this has been done, restart IIS

Configure Google Sheets in Vinyl

  1. Create a Google Sheets Data Server from IDE > Data Servers > + Server

    1. Server Name: Provide a name

    2. Type: Google Sheets

    3. Filename: UUID from Google Sheet URL Example: https://docs.google.com/spreadsheets/d/Spreadsheet_UUID_Here

    4. Security Provider: Leave this empty or blank

  2. Click Save

  3. Click the Advanced Settings button

    attachment

  4. This is where you will configure the authentication scheme to use JWT and point to the JWT file location. For example:

    AuthScheme=OAuthJWT;InitiateOAuth=GetAndRefresh;OAuthJWTCertType=GoogleJSON;OAuthJWTCert=C:\inetpub\wwwroot\Vinyl\JWT_File_Name.json

  5. Click Save

Test the Connection

Click the Test Connection button from Testing