Google Sheets Integration

Knowi enables you to connect, extract, and transform data from Google Sheets with ease. You can visualize and automate reporting using the data from your Google Sheets, either by directly connecting to the sheet or using transformations with Cloud9QL.

Connecting

Follow these steps to connect to your Google Sheets data:

  1. Log in to Knowi and select Queries from the left sidebar.

  2. Click on the New Datasource button and select Google Sheets from the list of datasources.

connecting to google sheets

  1. You will be prompted with the following fields:

    • Datasource Name: Provide a unique name to identify your datasource (e.g., "My Google Sheet").

    • Refresh Token: This is used to refresh the authentication and reconnect your Google account. You will need to authenticate your Google account, and this token will be automatically generated.

  2. Authenticate the Google Account: A modal will pop up allowing you to log into your Google account. Log into the account that is tied to your data in Google Sheets.

  3. After authorization, click Save. The connection will now be established, and you can proceed to create queries. The connection only needs to be made once for your account.

Querying Your Google Sheets Data

Once connected, follow these steps to extract data from your Google Sheets:

  1. Click on New Query and choose your Google Sheet Datasource.

connecting to google sheets

  1. Fill out the required inputs:

    • Sheet ID: The Sheet ID can be found in the URL of your Google Sheet. For example, if the URL is https://docs.google.com/spreadsheets/d/19FgD8QcypGXpITNxeO48e0K7Ikl7haiePf6o7VmB0CE/edit#gid=0, the Sheet ID is 19FgD8QcypGXpITNxeO48e0K7Ikl7haiePf6o7VmB0CE.

    connecting to google sheets

    • Cell Range: You can define the range of cells to extract data from. For example, A1:D5 will pull data from the first four columns and the first five rows.

    connecting to google sheets

    • Sheet Name: Enter the name of the worksheet you want to query from, e.g., Sheet1.

    connecting to google sheets

Cloud9QL Transformations

Once your data is extracted, you can perform various transformations using Cloud9QL to manipulate the data further.

You can visualize the extracted data using Knowi?s built-in visualizations or create custom queries for more advanced analysis.

Data Execution Strategy

When running queries, you can define the execution strategy:

Once you have set up your query, click on Preview to review the results and then Create & Run to execute it.

This setup enables seamless access to Google Sheets, with automated data refreshes and real-time reporting, allowing you to fully leverage your Google Sheets data in your Knowi environment.