Google Apps Script (GAS) is a scripting platform that can be used to extend Google Applications. Tasks can be automated with a few lines of code with relative ease. It also allows us to manage these scripts programatically via API.
Script types & Web app
GAS allows us to create different types of scripts. In this Wiki, we will see how we can leverage GAS - Web apps. The Web App URL can be used to pull data from Google Sheets similar to a REST API. It can also be used to push data into the sheets. This opens a variety of use-cases and integrations. One of the popular use-cases involves using this URL as a webhook.
More info about web apps and triggers
Using Web App URLs as Webhooks
A third-party tool (such as Pipedrive) can post data to the Web App URL by registering/configuring it as a webhook. The incoming data is processed by the script and executes the appropriate business logic. It could be as simple as appending the data as a row in the sheet.
Let’s say we have a Google Sheet like this
We can write a simple app script to read and write data via a dedicated URL using doGet(e) and doPost(e) functions.
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) {
const params = JSON.parse(e.postData.contents);
const robot_name = params.data.name;
const robot_team = params.data.team;
SpreadsheetApp.getActive().getActiveSheet().appendRow([robot_name, robot_team])
}
You usually see an URL like this after deploying the app
Posting data to the sheet
By making a simple POST Request with the right payload, you can add data to the sheet
