I work in Tech Support and Google Sheets helped me automate repetitive tasks!
This helped me push messages to various teams who use Google Chats, the Webhook option present in Google Chat is amazing.
The Goal was to track Incidents(Outages) and then push notifications to various teams which was manual before I found out about Google Sheets Simple Triggers.
Here are few links that might help you get started.
How to Build Apps using Google App Script
Read more about Google Sheets Triggers here
Read more about Google Chat Incoming webhooks
Now all we had to do was enter the data in a specific Cell and the onEdit() function would read it and post it to the necessary chat rooms.
You will need to add the trigger from
Google Scripts
Enough talk! Show me the code!
function myEditNew(e) {
// The e is an event Object
//https://developers.google.com/apps-script/guides/triggers/events
if (e.value)
{
Logger.log(e.range);
Logger.log("Data: "+e.value);
var val=e.value;
// This function is to get the current user that created the task its documented in the Gist at the end of the post
var cur_user=getCurrentUserEmail();
var text = Utilities.formatString('Incident created by *%s*: ```
%s
``` <users/all>',cur_user,val);
}
//1. Bot Test room
// [Args[Webhook URL, Space Name, Thread Name]]
// To get the Thread ID read this Stackoverflow answer
// https://webapps.stackexchange.com/questions/117392/get-link-to-specific-conversation-thread-and-or-message-in-a-chat-room-in-google
var urls = [
[
"https://chat.googleapis.com/v1/spaces/room_id/messages?key=Webhook_ID",
"spaces/room_id/messages/space_name.space_name",
"spaces/Thread_ID/threads/Thread_ID"
],
];
// Lots of Logging to see if Things are working properly.
Logger.log(e.range.getA1Notation());
Logger.log(e.range.getColumn());
Logger.log(val);
if (e.range.getA1Notation().startsWith('C') && val != "")
{
// Synchronously Post it to the Rooms
for(i=0;i<urls.length;i++)
{
var payload={
"name":urls[i][1],
"thread":{
"name":urls[i][2]
},
"text":text
};
var options = {
'method' : 'post',
'contentType': 'application/json',
// Convert the JavaScript object to a JSON string.
'payload' : JSON.stringify(payload)
};
Logger.log(options);
// UrlFetchApp Documentation
// https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
var response = UrlFetchApp.fetch(urls[i][0], options);
Logger.log(response);
}
}
}
Github Gist of the entire code
Thank you for reading, if you like this article feel free to share it as always I'm open to constructive feedback and criticism, feel free to leave a comment or message me.
Top comments (0)