Skip to Content

Google Sheets using JWT authentication connection guide for Jitterbit App Builder

Overview

This guide describes the system requirements and instructions for connecting App Builder 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 App Builder CRUD business rules.

System requirements

  • Google Sheets Database Server

  • JWT

Client web browser

  • Chrome: 84+

  • Firefox: 73+

  • Safari: 13.0.1+

  • Edge: 84+

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 App Builder instance resides on

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

  3. Copy the JWT file and place it in the App Builder directory

  4. Once this has been done, restart IIS

Configure Google Sheets in App Builder

  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\App Builder\JWT_File_Name.json

  5. Click Save

Test the connection

Click the Test Connection button from Testing