One of the strengths of the CData JDBC Driver for Google Sheets is its cross-platform support, enabling integration with major BI tools. Follow the procedure below to access Google Sheets data in KNIME and to create a chart from Google Sheets data using the report designer.
Define a New JDBC Connection to Google Sheets Data
- If you have not already done so, install the Report Designer extension: Click File -> Install KNIME Extensions, and filter on "Report".
- In a new workflow, click File -> Preferences and expand the KNIME -> Database Driver nodes to add cdata.jdbc.googlesheets.jar. The driver JAR is located in the lib subfolder of the installation directory.
-
In the Node Repository view, expand the Database node and drag a Database Reader onto the workflow editor.
-
Double-click the Database Reader and set the following properties:
- Database Driver: In the menu, select the CData JDBC driver.
- Database URL: Enter the connection properties. The JDBC URL begins with jdbc:googlesheets: and is followed by a semicolon-separated list of connection properties.
Google Spreadsheets はOAuth 認証標準を利用しています。各ユーザーやドメイン内のユーザーの代わりに、本製品がGoogle API にアクセスすることを許可できます。 接続すると、本製品はデフォルトブラウザでOAuth エンドポイントを開きます。ログインして、アプリケーションにアクセス許可を与えます。本製品がOAuth プロセスを完了します。
詳細はドライバーのヘルプドキュメントを参照してください。
A typical JDBC URL is below.
jdbc:googlesheets:Spreadsheet=MySheet;
- User Name: The username used to authenticate.
- Password: The password used to authenticate.
- SQL Statement: Enter an SQL query in the SQL Statement box or double-click a table. This article uses the query below to create a chart:
SELECT Shipcountry, SUM(OrderPrice) FROM Orders GROUP BY Shipcountry
-
Test the connection by clicking Fetch Metadata.
-
Connect the Database Reader to a Data to Report node to supply the dataset to a range of data visualization controls. Click Execute and then click Edit Report at the top of the workflow to open the report designer perspective.
-
You can now generate reports based on live data. To create a chart, drag the chart control from the palette to the report designer. In the resulting wizard, you can use the filtering and aggregation controls available in KNIME.
