Using Google App Scripts to send a webhook for new or updated rows in Google Sheets

Showcase
1

Hey Guys and Gals,
Since the Slack workspace is now officially dead, its about time I start getting involved in this amazing community that was built by @Michaela, Hopefully this fits into the nerd zone.

Most of us are using the Google Sheets make application in one way or another but I find it a little limited. We are able to get a webhook in real time but this requires installing a chrome extension and still will waste some tasks since we are not able to control the exact condition of when the webhook is triggered.
This means we have to concede wasting a few tasks and controlling the execution via filters.

Since this is the nerd zone I thought it would be the perfect place to share my Medium story - Sending a Webhook for New or Updated Rows in Google Sheets | by Eyal Gershon | Medium

If you have any questions regarding customizing or using this script, by all means let me know and I will be happy to help.

9 Likes
4

this is really neat @EyalGershon !!
It also has the added advantage of being possible to set this up to trigger even when changes are made via API. Since the Make plugin works as a webapp, it doesn’t pick those up, only manual changes made from a browser.

4 Likes
5

Thanks for your guidance! I also have a Google Form which automatically updates a Google Sheets file and I wanted this update to then auto trigger my Make.com automation. I tried using the Make.com integration built into Google Sheets but it didn’t work.

I managed to find some code that works and sends the last row added to the Make.com workflow:

Step 1: Click Extensions, then Scripts, Add this to the Script window and save (change the webhook name to your webhook):

function onFormSubmit(e) {
// Get the sheet where form responses are stored
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get the last row of data
var lastRow = sheet.getLastRow();

// Get the data from the last row (the new submission)
var data = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];

// Get the headers (column names)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

// Create an object to store the form data
var formData = {};

// Pair each header with its corresponding value
for (var i = 0; i < headers.length; i++) {
formData[headers[i]] = data[i];
}

// Convert the data to JSON
var payload = JSON.stringify(formData);

// Your Make.com webhook URL
var webhookUrl = “YOUR_MAKE_WEBHOOK_URL_HERE”;

// Set up the options for the HTTP request
var options = {
“method”: “post”,
“contentType”: “application/json”,
“payload”: payload
};

// Send the data to Make.com
try {
UrlFetchApp.fetch(webhookUrl, options);
Logger.log(“Data sent successfully to Make.com”);
} catch (error) {
Logger.log("Error sending data to Make.com: " + error);
}
}

Step 2: Add a trigger (on the left hand side click on Triggers)
To set up the trigger:

  • Click on the clock icon (Triggers) in the left sidebar.
  • Click the “+ Add Trigger” button at the bottom right.
  • Set the following options:
    • Choose which function to run: onFormSubmit
    • Choose which deployment should run: Head
    • Select event source: From spreadsheet
    • Select event type: On form submit
  • Click Save.

Hope this helps others!! Was pulling my hair out for this one!

1 Like
6

Nice one, I have another Medium post just about that :slight_smile: Sending a Webhook for each Google Forms Submission | by Eyal Gershon | Medium