Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. In our previous tutorial, we learned how to read and write CSV files automatically in KNIME. If your raw data lives on your local computer, that is the perfect automation setup.
But let’s be realistic about how modern businesses operate today. Your marketing team, your sales reps, and your clients are probably not sending you CSV files anymore. They are collaborating live in the cloud.
If you are still opening a shared Google Sheet, clicking “File > Download as Excel,” processing it, and then copying and pasting the new data back into the cloud, you are wasting precious time. In this Automation Hack, I will show you how to connect KNIME directly to Google Sheets via API.
And the best part? You do not need to know what an API is, and you will not write a single line of Python code.
Step 1: Install the Google Extension
Because Google connection is an advanced feature, it is not included in the default KNIME installation. We need to grab the right “Lego block” from the KNIME Hub.
If you have never done this before, please read my quick guide on how to install extensions in KNIME Analytics Platform. You need to search for and install the extension called KNIME Google Connectors. Once installed, restart your KNIME.
Step 2: The Google Authenticator Node
APIs (Application Programming Interfaces) usually require complex security tokens, secret keys, and coding to establish a connection. KNIME handles all of this automatically using the Google Authenticator node.
- Drag the Google Authenticator node onto your canvas.
- Double-click to open it. Under the “Authentication” tab, select Interactive Login.
- Click OK and execute the node (press F8).
- Your default web browser will instantly pop up, asking you to log into your Google Account. Click “Allow” to grant KNIME permission to view and edit your Google Drive.
That is it! The traffic light under your node will turn green. You have just established a secure API connection to Google’s servers without writing any code.
Step 3: Read and Write Live Data
Now that KNIME is connected to your Google account, you can read from or write to any spreadsheet you have access to.
Reading Live Data (Google Sheets Reader)
Search for the Google Sheets Reader node. Notice that its input port on the left is a solid blue square. Draw a line from the output port of your Google Authenticator directly into the Google Sheets Reader.
Double-click the Reader node. You will see a “Select” button. Click it, and KNIME will display a popup window showing all the files in your actual Google Drive! Select the spreadsheet you want, choose the specific sheet tab, and click OK. When you execute the node, your cloud data is instantly pulled into KNIME.
Updating Cloud Data (Google Sheets Updater)
Once you pass that data through your IF-THEN business logic (Rule Engine) or summarize it, you want to push the results back to the cloud.
Connect your final data and your Authenticator node to a Google Sheets Updater node. Double-click it, select the destination file in your Drive, and define the exact cell range (e.g., A1) where you want the new data to be pasted. Execute it, and watch your Google Sheet update live in your browser!
Conclusion: Your Next Steps
Connecting to Google Sheets via API is one of the most powerful “Aha!” moments for any DX manager. You have successfully bridged the gap between your local automation and the cloud. Your colleagues can now watch their dashboards update automatically, and you never have to manually download a file again.
But what if your team still relies heavily on local folders? What if every day, a new Excel file is dumped into a shared folder, and by the end of the month, you have 30 different files that need to be processed one by one?
You don’t need to build 30 different workflows. Join me in our next Automation Hacks tutorial, where we cover the ultimate time-saver: How to loop through multiple files in a folder using KNIME!
