Do you constantly look for easy ways to automate your work? Great, even I do. This is how I discovered: Google Sheets Webhooks integration. With this detail read you’ll be guided through:

  1. How to enable your Apps Script API with just 2 steps
  2. How to add a new container-bound Apps Script project to your Google Sheets
  3. How to update your script with open-source (pre-written) code and quickly deploy it as a web-app
  4. Authorise the newly added script through a custom menu and leverage it to receive & log the data

In this blog, you will learn about how Google Sheets Webhooks integration works, and what are some use cases for this Google Workspace app. You will also learn how a no-code platform can help you to quickly create a webhook and accept/log your incoming data to a Google Sheet, without needing any code or a new platform.

What is Webhooks for Sheets and How Does it Work?

Webhooks are background connections to a web server. They can be used to implement functionalities such as updating graph/numbers on their own or certain parts of a webpage updating themselves depending on an event and with the change in server-side values.

The “user-defined HTTP callbacks” are triggered by some events. That make an HTTP POST request to a webserver for latest updates. The webserver can be same or different from the original enclosing webpage. You can use webhooks for events on a third-party website depending on changes on a backend server. 

Real-time Google Sheets Integration using Hevo

Hevo streamlines Google Sheets integration by providing seamless data syncing and automation between your sheets and various data sources. This ensures real-time updates and accurate data flow, enhancing your workflow efficiency and data analysis capabilities.

Get Started with Hevo for Free

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.
Google Sheets Webhooks - Blank Spreadsheet
Source: Self
  •  Populate the fields in your Spreadsheet.
Google Sheets Webhooks - Populating Fields
Populating Fields
  • Now, select the option Tools from the Toolbar menu and then click on Script Editor.
Google Sheets Webhooks - Script Editor
Script Editor
  • 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:
Google Sheets Webhooks - Custom Script
Custom Script
Download the Whitepaper on Automating Data Integration
Download the Whitepaper on Automating Data Integration
Download the Whitepaper on Automating Data Integration
Learn the key benefits of automating Data Integration

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.
Google Sheets Webhooks - Granting Access
Granting Access
  • 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.
Google Sheets Webhooks - 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.
Google Sheets Webhooks - 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 Sheet Webhooks Integration in no time!

Integrate data from Webhooks to BigQuery
Integrate data from Webhooks to Snowflake
Integrate data from Google Sheets to Redshift

Additional Resources on Google sheets Webhooks

Use Cases of Google Sheets Webhooks

  1. To get real-time data from google sheets: When you want to incorporate any real time update in your google sheets data without the need to constantly poll for changes.
  2. To automate a task that needs gathering data from different sources: Webhooks helps you to connect Google Sheets and other multiple sources to a destination through its ‘hook’. This centralization of data will help you further for effective analysis.

Conclusion

  • This article provided you with a step-by-step guide on how to set up Google Sheets Webhooks Integration successfully.
  • 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.

FAQ on Google Sheets Webhook Integration

Does Google Sheets have a webhook?

Yes, Google Sheets allows you to integrate your spreadsheets to other sources using Webhooks.

What is a webhook?

A webhook is a mechanism used in web development and APIs to allow one application to send real-time data or trigger actions in another application.

What scripting does Google Sheets use?

Google Sheets primarily uses Google Apps Script as its scripting language. Google Apps Script is a JavaScript-based scripting language.

Can I create my own webhook?

Yes, you can create your own webhook.

How do I join strings in Google Sheets?

In Google Sheets, you can join two strings using the CONCATENATE function, the syntax of which is CONCATENATE(string1,string2).

mm
Former Research Analyst, Hevo Data

Rakesh is a research analyst at Hevo Data with more than three years of experience in the field. He specializes in technologies, including API integration and machine learning. The combination of technical skills and a flair for writing brought him to the field of writing on highly complex topics. He has written numerous articles on a variety of data engineering topics, such as data integration, data analytics, and data management. He enjoys simplifying difficult subjects to help data practitioners with their doubts related to data engineering.

No-code Data Pipeline for Google Sheets