Skip to content
AIV

Connecting Google Sheets

To integrate Google Sheets as a datasource in your AIV application, follow these steps. Ensure you have the necessary Google Sheets credentials.

Prerequisite

To access Google Sheets from your AIV app, you need a Google Sheets API credential file. Follow these steps to create it:

1. Enable Google Sheets API

  • Go to the Google Cloud Console.
  • Create a new project or select an existing one.
  • Navigate to APIs & Services > Library.
  • Search for “Google Sheets API” and enable it.
  • Search for “Google Drive API” and enable it.

Image

Image

  • Go to APIs & Services > OAuth consent screen.
  • Choose External if the app is for multiple users or Internal if only for G Suite users within your organization.
  • Click Create button.
  • Fill in the required details like App Name, User Support Email, and Developer Contact Information.
  • Click Save and Continue.
  • Click on ADD OR REMOVE SCOPES button to Add the following scopes for Google Sheets and Google Drive API access

Image

  • Search and select from list as shown in below image
    • /auth/spreadsheets.readonly
    • /auth/drive.readonly

Image

Image

Image

Image

  • Click Update.
  • Click Save and Continue until completion.

3. Create OAuth 2.0 Credentials

  • Navigate to APIs & Services > Credentials in the Google Cloud Console.

  • Click on Create Credentials and select OAuth client ID.

  • Under Application type, choose Web application.

  • For Authorized redirect URIs, add the following URL:

    https://<host>:<port>/aiv/v5/allow/callback

    example1: https://aivhub:8080/aiv/v5/allow/callback

    example2: https://aivhub/aiv/v5/allow/callback (if port 443 is configured)

    Note: Replace only <host> and <port> with the actual host and port of your AIV application.

  • Click Create. This will generate the credentials.json or client_secret_XXXXXXXX.json file; download and save it securely.

Important: Ensure the credentials.json or client_secret_XXXXXXXX.json file is kept secure as it contains sensitive information needed for authentication.

Congratulations! You’ve successfully completed the Google API configuration. Now, please return to AIV and follow the steps to complete the final steps.

Steps to Connect Google Sheets in AIV Datasource

  1. Navigate to Datasource Screen

    • Open your AIV application.
    • Go to the Datasource under Master Data section from the main menu.This is where you can manage all your data sources in AIV.
    • In the Datasource section, locate the Create button at the bottom of the screen, or alternatively, right-click on any blank area and select Create Datasource from the context menu.

  2. Select Google Sheets

    • In the datasource options, select Google Sheets as the type.

Image

Image

  1. Fill out necessary details Upload Credentials File

    • Provide Display name to be identified by you.
    • Type a Email (This email should be email from which you have created credential.json or client_secret_XXXXX.json file)
    • Click on upload icon next to File Name text box as shown in below image. You’ll be prompted to upload the credentials.json file.
    • Browse to the location of the downloaded credentials.json and upload it.

    Image

  2. Test Connection

    • Test the connection to verify successful integration.

  3. Authenticate and Authorize

    • Once you click on Test Connection, AIV will prompt you to authenticate your Google account.
    • Allow necessary permissions for the app to access your Google Sheets.

  4. Save Connection

    • Save the configuration by clicking on Save button.

You are now connected to Google Sheets via AIV Datasource!