Setting up Google Sheets Webhooks Integration Simplified: 3 Easy Steps

on Data Integration, Google sheets, Tutorials, Webhook • October 18th, 2021 • Write for Hevo

Google Sheets is a Cloud-based Spreadsheet application that allows you to store and organize different types of information in the form of Spreadsheets. Google Sheets supports App Scripting that allows the users to automate their Spreadsheets in a variety of ways. Google Sheets offers the ability to deploy your script as a Web App as one of its features. When you deploy your script as a web app, you’ll have a URL to which you can make GET or POST requests to initiate your script. You can then receive POST requests using this Google Sheets feature without the need for a custom piece of infrastructure that makes it easier to process data from Webhooks

Upon a complete walkthrough of this article, you will gain a holistic understanding of Google Sheets and Webhooks. You will also learn about the steps involved in building Google Sheets Webhooks Integration in a seamless manner. Read along to learn more about Google Sheets Webhooks Integration!

Table of Contents

Prerequisites

  • Basic understanding of Spreadsheets.

Introduction to Google Sheets

Google Sheets Logo
Image Source

Google Sheets is a Spreadsheet Program that is part of Google’s free, web-based Google Docs Editors suite. It enables multiple users to simultaneously Edit Spreadsheets and Collaborate on different projects in real-time. Google Sheets is a free and fully functional Spreadsheet program that supports the most commonly used Spreadsheet formats. Since Google Sheets is a Cloud-based Software-as-a-Service (SaaS) application, you can access your files remotely from any device with an active Internet connection.

Key Features of Google Sheets

Some of the key features of Google Sheets are as follows:

  • All changes in Google Sheets are saved automatically as you type. You can view previous versions of the same Spreadsheet by using Revision History. It is sorted by the people who made the change, along with the date and time of the change.
  • Google Sheets also provides instant insights through its Explore panel. It provides an overview of data by allowing you to choose from a variety of Pre-Populated Charts and Informative Summaries. The Explore panel also displays a list of suggested graphs based on the data entered in the Spreadsheet. You have the option of using Pie Charts, Pivot Tables, Bar Charts and more.
  • Google Sheets allows multiple users to work on the same Spreadsheet at the same time. Google Sheets also has a Sidebar Chat Feature that allows you to discuss edits with other collaborators. You can make these changes in real-time and provide feedback on specific changes.

Introduction to Webhooks

Webhooks Logo
Image Source

A Webhook is a method of altering and modifying the behavior of web pages or web applications through the use of Custom Callbacks. It is essentially a user-defined HTTP Callback that is triggered by specific events. When the trigger event occurs on the source website, the Webhook Records it, Collects the Data, and Sends it in the form of an HTTP request to the specified URL in the form. A trigger event can be a Blog Comment, a Sign-up, Form Submission, etc.

A Webhook delivers data to other applications as it happens, which means you get data immediately, unlike traditional APIs, which require you to Poll Data frequently in order to get it in real-time. Webhooks are now much more efficient for both providers and consumers. Webhooks are commonly used to connect two distinct applications. You can even leverage Webhooks to set up an event on one website to trigger an action on another. Later in this article you will learn about Google Sheets Webhooks Integration.

Simplify Google Sheets & Webhooks ETL & Data Integration using Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ different sources (including 40+ Free Sources like Google Sheets) to a Data Warehouse or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using its in-built Webhooks Connector. Hevo loads the data onto the desired Data Warehouse, enriches the data, and transforms it into an analysis-ready form without writing a single line of code.

Its completely Automated Data Pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence (BI) tools as well.

Get Started with Hevo for free

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Connectors: Hevo supports 100+ data sources and integrations to SaaS platforms such as Google Sheets, etc, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake, and Firebolt Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-day Free Trial!

Steps to Set up Google Sheets Webhooks Integration

Building up Google Sheets Webhooks Integration is a three step process that enables the users to send data from Google Sheets on any event change. Follow the steps given below to set up Google Sheets Webhooks Integration:

Step 1: Create and Configure a New Document

The first step involved in setting up Google Sheets Webhooks Integration requires you to create a new script. To build Google Sheets Webhooks Integration, you must first create a new document. Follow the steps given below to create and configure a Google Spreadsheet file:

  • Navigate to your Google Sheets account and select the option Blank to create a new Spreadsheet.
Blank Spreadsheet
Source: Self
  •  Populate the fields in your Spreadsheet.
Populating Fields
Image Source
  • Now, select the option Tools from the Toolbar menu and then click on Script Editor.
Script Editor
Image Source
  • Once you click on the Script Editor option, a new text editor will open up where you can insert any type of script of your own.
  • Paste the following piece of code in the Script Editor. It will just return rows 2-10 as JSON.
function doGet(e) {
var ss = SpreadsheetApp.getActive();
var rng = ss.getActiveSheet().getRange(2, 1, 10, 3)
var vals = rng.getValues()
Logger.log(vals)
return ContentService.createTextOutput(JSON.stringify(vals)).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
return HtmlService.createHtmlOutput(“post request received”);
}
  • Don’t forget to save the document once you have pasted the above code successfully. The final result will look something like the following:
Custom Script
Source: Self

Step 2: Publish Your Script

Once you have created the script, the second step in setting up Google Sheets Webhooks Integration requires you to deploy your script as a web app. Follow the steps to do so:

  • Click on the Publish button in the top bar and select the option Deploy as Web App to publish your script.
  • It is critical that you select “Anyone, even anonymous” in the “Who has access to the app” field on the newly appeared screen to make your data public. 
  • Now click on the Deploy button to publish the script.
  • Once you click on the Deploy button, a new pop-up window will appear which will provide you with the web app link.
Web App URL for Google Sheets Webhooks Integration
Image Source
  • This link will redirect you to a page that will contain all the JSON data fetched by your script.
JSON data
Image Source

Step 3: Create a Webhook

The final step involved in setting up Google Sheets Webhooks Integration requires you to create a new Webhook. There are numerous third-party tools available on the Internet that can be used to create a new Webhook or you can create a Custom Function in Google Sheets to leverage its functionalities to create a new Webhook. Follow the steps given below to create a new Webhook:

  • Navigate back to the Script Editor and paste the code given below to create your own custom function.
function currentTime() {
var d = new Date();
var currentTime = d.toLocaleTimeString()
return currentTime;
}
function CELL_CHANGED(row) {
var options = {
'method' : 'post',
'payload’ : JSON.stringify(row)
};

UrlFetchApp.fetch('http://requestbin.net/r/1hqonni1', options);
return "UpdatedAt: "+ currentTime()
}
  • This Custom Function will get triggered whenever there is a change in the input data.
  • You can now modify the sheet to include a call to the function that returns the entire row.
Return Message
Image Source
  • If you see a successful return message from the function, it means that the Webhook has been sent with the row data as an array. If you bake validation into your script, you can return a useful error message to the user, explaining why some of the input data is invalid, preventing bad data from ever reaching your Webhook.

Once you follow all the above instructions in the correct sequence, you will be able to set up Google Sheets Webhooks Integration in no time!

Conclusion

This article provided you with a step-by-step guide on how to successfully set up Google Sheets Webhooks Integration. It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently.  If you want to integrate data from various data sources such as Google Sheets into your desired Database/destination for free and seamlessly visualize it in a BI tool of your choice, Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and destinations.

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources such as Google Sheets for Analysis. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using its in-built Webhooks Connector. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouse, Database, or a destination of your choice. It also provides you with a consistent and reliable solution to manage data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about Google Sheets Webhooks Integration! Tell us in the comments section below!

No-code Data Pipeline for Google Sheets