Using Google App Scripts for automation / integration

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

image

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
final_61bdb326330713010a7190d8_529736