Google AppScript, Google Sheet and WebHooks | Toucan Toco

In this article, I’ll tell you about a very simple way to use Google Sheet+Google AppScript to collect data from a webhook. You can also dive into the code of this spreadsheet that I made just for you :*

Some context - Our support workflow

Recently I wanted to use Google Spreadsheet to log support ticket handling and resolution time.

We use Talkus, combined with Slack in our workflow:

1. Someone from the Toucan team needs technical help
2. They open a ticket with the command /talkus_new_ticket help pls and a temporary channel is created (usually #z-silly-name)
3. Our King or Queen of the week (=weekly anointed tech support team, see this talk done @Algolia’s tech lunch), can respond and handle the issue.
4. When the problem is solved, the temporary channel is closed

** Warning - already made tools are available **

It is worth noting that Talkus offers a reporting feature, but because all our Talkus users are from the same Toucan slack team, the handling times were wrong (the person asking for help was counted as the person responding).

Also, Zapier has a recipe just for webhook and Google Sheets (but where’s the fun in that :p)

Let’s do this

Ze plan

I figured I would need :

• a Google Web App that can receive POST requests with some data
• to write this data into a spreadsheet
• to configure Talkus so that for every new ticket/event, a POST is sent to my Google Web App
• Connect this spreadsheet to a Toucan Toco dataviz application (for this step, pls contact one of our sales rep ;) )

Fairly simple :) First, I need to check how each piece works, then I’ll put the bricks together.

Adding a row in Google Sheet

From the docs : https://developers.google.com/apps-script/guides/sheets#writing_data

1. Create a Google Spreadsheet
2. From the spreadsheet, Open the Scripts Editor : Tools > Script Editor...
3. ???
4. Profit!

In the script editor, I added a very simple function, to see if everything works as expected.

function testRun() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['some', 'fake', 'data']);
}

1. Save (cmd+S), and fill in your project name when prompted
2. In the script editor menus, go to Run > Run function... > testRun

Google Script will ask you to authorize the script to write on the spreadsheet, do so.

Tadaa… it worked, a row was added!

That was easy! Now let’s turn this script into a Web App so it can handle requests.

Handle POST requests

From the docs, we see Google Web Apps have the ability to answer to POST requests. RTFM :

• About Web apps
• About doGet and doPost

Add a doPost method

A Web app can handle GET and POST requests using a doPost and a doGet method: Let’s try this with very simple methods:

function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['post test']);
}
function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['get test']);
}

Publish your Web App

In the script editor, go to Publish > Deploy as Web App

I chose :

• to execute the app as “Me”
• Who has access to the app “Anyone, even anonymous”

Go to Troubleshooting below if you don’t see the “Anyone, even anonymous” option.

After clicking the deploy button, I was provided with a script url : https://script.google.com/macros/s/XXXXXX-YYYYYYY/exec

If you access it from your browser, your doGet function should be executed (try it!).

Going POSTal

Though here it seems quite simple, making this POST work was actually what took me the longest. In the Google Documentation, I kept reading about options unavailable to me. First I brushed it off as lost in translation (the doc was in English, my interface was in French). Turns out, I was using an enterprise Google App account, and was logged in to multiple accounts at the same time each causing their own set of problems :). It took me some time to figure out, so I’ve added a Troubleshooting section.

I want to see how my app reacts to POST. So I do a simple CURL post :

curl -X POST https://script.google.com/macros/s/xxx-YYY/exec

Let’s add data:

curl -d '{"test":"supertest", "test2":"supertest2"}' -H "Content-Type: application/json" -X POST https://script.google.com/macros/s/xxx-YYY/exec

To test this I modified my code:

function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([e.postData.contents]);
}

Then I re-published my app:

Be careful here to select the ‘New’ version option in the dropdown.

Note that using the /dev URL (found if you follow the latest code link) doesn’t seem to work for POSTs, so for every change you make to your post, you need to publish a new version.

Tadaa! The result is visible in the spreadsheet !

Troubleshooting…

• You do not see the “Anyone, even anonymous” option

At this point when I was exploring, I got stalled because the permissions of my script and spreadsheet where restricted. This is due to the fact I was doing all this from an enterprise GSuite account. You need to be able to publish documents publicly to anyone in the web for this to work. If you are a Google Admin for the domain, you can enable this. I personally chose to create the spreadsheet from my own consumer account, because I didn’t want to lower the security settings of all our Google Apps.

• Page not found when accessing the url // doGet not working

When trying to access the script from the browser, I was greeted with a page not found error… It turns out since I was logged with multiple google accounts, the URL given was faulty : https://script.google.com/macros/u/0/s/XXXXXX-YYYYYYY/exec instead of https://script.google.com/macros/s/XXXXXX-YYYYYYY/exec (cf. issue on StackOverflow)

To fix this, you need to take out the /u/x part of the URL to test the script, like this : https://script.google.com/macros/s/XXXXXX-YYYYYYY/exec

• Page not found when accessing a resource

This generally means the permissions aren’t set right for you to access the said resource.

Let’s collect data

With a Talkus WebHook

Here my goal is to use the Google spreadsheet as a database for talkus ticket data.

From the talkus app, in the Integration menu, I just copy-pasted my script url, and pressed the “Send a test message to your webhook” button.

Now I see that the postData content form is this :

The actual code

I did some tests by creating tickets in slack and figured I would only need to log end events, because they contained all relevant info. I would need :

• the end event date (createdAt field)
• to parse the messages and get
  • The first userName to talk in the channel + the message time > that’s the user asking for support
  • The second userName to talk in the channel + the message time > that’s the user responding

I also eliminated from my search the users with :

• userName == 'slackbot' > Usually automated response to expressions.
• userPicture == 'https://talkus.io/app/avatar-default.webp' > The Talkus Bot
• userName == 'Toucan Tech support' > Usually automated response from Talkus

Visualize your data

For this part I used our own Toucan application to easily connect to the spreadsheet and visualize the handling time. You can contact our sales rep team for more info, or use some of the free tools available online.

With the data as is, I added a little python / pandas script to compute time deltas, it’s very easy :) Here are the useful lines to convert time<>strings

import pandas as pd

# Dates from Talkus are of the form 2018-02-12T10:55:54.369Z df['userAskingJoinTime'] = pd.to_datetime(df['userAskingJoinTime'], format="%Y-%m-%dT%H:%M:%S", exact=False, errors='coerce')
df['firstResponseTime'] = pd.to_datetime(df['firstResponseTime'], format="%Y-%m-%dT%H:%M:%S", exact=False, errors='coerce')

# Compute difference and convert to seconds df['handlingTime'] = df['firstResponseTime'] - df['userAskingJoinTime']
df['handlingTime'] = df['handlingTime'].astype('timedelta64[s]')

Now for some ridiculous viz :

Why ridiculous? Because the script has been running for a week so there aren’t a lot of data points, hence 1 extra long ticket will break the score. Also tickets created out of business hours have longer handling times, maybe the next iteration of the script could account for that. I’ll do a followup once we have more data :)

What I found here already is that sometimes our tech support team joins a channel but does not write to indicate they are working on the problem: this greatly increases measured handling times. This is coherent with our qualitative analysis of Toucan support workflow which hinted at the need for early communication on an issue, even a simple acknowledgment of the problem. So it sounds like keeping an eye on this metric should lead to interesting results!

Hope this will help, have fun collecting data ;)