Connecting
Follow these steps to connect to your Google Sheets data:
-
Log in to Knowi and select Queries from the left sidebar.
-
Click on the "New Datasource" button and select Google Sheets from the list of datasources.
-
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.
-
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.
-
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:
Step 1: Click on New Query and choose your Google Sheet Datasource.
Step 2: Fill out the required inputs. Sheet ID, Cell Range, and Sheet Name.
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.
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.
Sheet Name: Enter the name of the worksheet you want to query from, e.g., Sheet1.
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:
- Direct Execution: This will directly execute the query on the Google Sheet, fetching live data.
- Non-Direct Execution: The results of the query will be stored in Knowi’s Elastic Store, allowing you to schedule queries or reduce load on the original data source.
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.