One day came, and I have faced the challenge of how to post a new message on Slack when someone submits a Google Form. Well, not a challenge really.
In the beginning, I thought to just plug in Zapier or any other tool like that.
But then I asked myself "I am a developer, am I not? Could it be done by using glue, duct tape and a hammer, like in old times?" And in fact, it could be.
Step 1: Create a form
So yeah, here goes my form.
I know, I know, it is better to have a fully-featured bug tracker here, but I wanted to keep it simple for now.
So the idea would be: whenever someone submits the form, I want to receive a message in Slack right away.
Step 2: A little bit of engineering
In the form editor there is a link called Script editor:
If I press it, a simple IDE will appear.
Meet Google Script!
GS is sort of like VBA (if you know what it is 🤪), but with ECMAScript syntax. I got the impression that GS was initially built to automate Google Sheets, but for other types of documents it also can do a trick or two.
Replace the content of the tab with the following listing:
function onFormSumbit(event) {
Logger.log("authMode=%s, source.getId()=%s", event.authMode, event.source.getId());
var items = event.response.getItemResponses();
for (var i in items) {
const item = items[i];
Logger.log("%s = %s", item.getItem().getTitle(), item.getResponse());
}
}
~~~{% endraw %}
and press the {% raw %}`Save`{% endraw %} button.
For now, this function is just a function. I can try to run the script by pressing the {% raw %}`Run`{% endraw %} button, but I will only get an error message saying that {% raw %}`event`{% endraw %} is undefined.
I need to bind the function to the form submission event.
To do that, I go to {% raw %}`Edit`{% endraw %} ➡️ {% raw %}`Current project's triggers`{% endraw %} menu item. In the list of triggers I hit {% raw %}`+ Add trigger`{% endraw %} button located at the bottom.
A form will pop up then. I check if the function name is right, and also change {% raw %}`Select event type`{% endraw %} to {% raw %}`On form submit`{% endraw %}.
After I hit {% raw %}`Save`{% endraw %}, I will be asked to grant permissions. Usual deal. In the list of triggers I can now see my trigger that was never executed before.
I go back to my form and fill it up, then submit:
In the list of triggers I can see that it was just executed a few moments ago:
If I proceed to {% raw %}`My executions`{% endraw %} section, I can get the output of the script:
Wow, it works! What a twist!
## Step 3: Wire up Slack
Before I go further, I need to set {% raw %}`Slack`{% endraw %} up a bit. So, {% raw %}`Slack`{% endraw %} allows posting a message to a channel, by providing a secret URL ({% raw %}`Webhook`{% endraw %}). To create such {% raw %}`webhook`{% endraw %} I need to create a {% raw %}`Slack`{% endraw %} application first.
So I go to [Slack API panel](https://api.slack.com/apps/) and hit {% raw %}`Create an App`. Here I need to specify the app name and the workspace.
When the app is ready, I go to {% raw %}`Add features and functionality`{% endraw %} and then click on {% raw %}`Incoming Webhooks`{% endraw %}.
If {% raw %}`Incoming Webhooks`{% endraw %} were not activated before, I activate them now. Then I scroll down to the list of hooks and hit the {% raw %}`Add new Webhook to Workspace`{% endraw %} button.
I need to specify the channel I am going to post the messages on. When all of this is done, I can see my hook in the list, and copy it's URL from there.
The URL should have a format of `https://hooks.slack.com/services/XXXXXXXX/YYYYYYY/ZZZZZZZZZZZZZZZZ`.
## Step 4: The code
Allright!! Time to write some cool stuff here.
I go back to my `GS` code editor and change the function like this:
~~~js
function onFormSumbit(event) {
var items = event.response.getItemResponses();
var summary = '';
var severity = '';
for (var i in items) {
var item = items[i];
var title = item.getItem().getTitle();
var value = item.getResponse();
if (title === 'Tell us what happened') {
summary = value;
} else if (title === 'How severe the problem is?') {
severity = value;
}
}
if (summary) {
postMessage(summary, severity);
}
}
function postMessage(summary, severity) {
var message = {
"channel" : "react-calendar",
"text" : "Brace for impact! Another ticket is there: "+severity,
"attachments": [{
"text": ":fire: "+summary,
"footer": "<https://docs.google.com/spreadsheets/d/XXXXXXXX/edit|See all issues>",
"mrkdwn_in": ["text"]
}]
}
var options = {
"method" : "post",
"contentType" : "application/json",
"payload" : JSON.stringify(message)
};
return UrlFetchApp.fetch("https://hooks.slack.com/services/XXXXXXXX/YYYYYYY/ZZZZZZZZZZZZZZZZ", options);
}
~~~
⚠️⚠️⚠️ Please replace my stub URL with your own one! There is also a link to a spreadsheet with all the submissions, and its URL is stubbed as well.
Well, it is not a very brilliant code though, and it most definitely can be improved. Later. For now, I save the code in the editor and submit the form once again.
Aaaaaand nothing works. In the output of the execution, there is an error `Exception: You do not have permission to call UrlFetchApp.fetch.`
Well, this happens because I created a trigger when there was no `UrlFetchApp.fetch()` call in the code. Since it needs elevated permission, my trigger does not work anymore.
I need:
1. to revoke [permissions](https://security.google.com/settings/security/permissions) of the script in my Google account,
2. to kill the trigger and re-create it again (Google may get a bit paranoid and warn me that I should not trust the code I wrote a couple of minutes ago. And heck, Google is wise in that one).
As soon as this is done, I try to re-submit the form one more time.
Oh. My. Glob. It worked! Now I am not gonna miss another bug report ever!
---
## Conclusion
Sometimes instead of rushing into a third-party overkilling solution, it may be preferable to utilize your engineering skills. Of course, this is always a trade-off, but if you have time, then why not dare?
---
Cover image by Cesar Carlevarino Aragon at [Unsplash](https://unsplash.com/@carlevarino)
Top comments (0)