How to build a bot that adds data to a Google Sheet

Written by

Step 2: Create your bot

You can pick a template, but for this example, we are starting with a blank bot so click skip template.

Load up your new bot and put in your get started message. This message is displayed to users when they start up your messenger bot and appears above the get started button.

Step 3: Start building

Now you have created your bot, it is time to start building. The next screen will take you to the welcome message section.

Click the text icon under the add message option. In this box, you explain the vote and ask users to pick their favorite option. E.g. in the below example we are asking about whether they prefer crisps or chocolate.

Step 4: Build your “voting” option

Now we need to add in the voting options. Select quick reply from the add message part menu. Add two new quick replies as below.

Step 5: Set up the user attributes

We now need to set a user attribute depending on which option the user votes for. Click on the cog icon next to chocolate. In the first drop-down, select the option, Save user attribute and send a message.

In the next box that appears, you need to create a new user attribute called Food. You then need to set a tag, which in this case is chocolate. In the final box, you then tell the bot which message it needs to display, once the user has voted. In this case, we are going to create a new message called end poll. Once you have done this, hit save.

Step 6: Repeat step five for “crisps“ voting option

We need to repeat the above for the crisps quick reply button.

Click on the cog and select Save user attribute and send a message. Set the attribute to be the food attribute you created when setting up chocolate. Now make sure you tag this value with the value crisps. Finally, set the message to end the poll as above. Once you have done this, hit save. Then on the bot window, hit save at the bottom again.

Step 7: Edit the “end poll“ message

You will now be taken to the messages page, where you can see end poll has been created. Click edit next to this message. You can now put in a message that your end user can see once they have voted by adding a text type message.

Now for the google sheets integration. Click to add a webhook to your bot. Then click on Click to configure. You will see that a URL field comes up. Now we need to switch over to Zapier to set up our integration.

Step 8: Set up the webhook

Login to Zapier and click on add a zap. Under choose a trigger app, select webhook.
Under Select webhook trigger, select catch hook. You will then be given a URL by Zapier. Copy and paste that URL into the URL field on your bot page

Step 9: Push changes live

Save your bot and select the push changes live button at the very top of the dashboard page. This pushes all changes to Workplace.

Now we need to test our bot to push some data through and test our zap is set up. Click the test button next to your welcome message item and test out your bot. Once you have tested it, switch back to Zapier and click the ok I did this button.

You should now see the below. This shows that it has pulled through the sample data from your test.

Click on the continue button.

Step 10: Add an action step

Now we need to set up our Zapier action. Select google sheets from the action app list. In the select Google Sheets action, select create spreadsheet row and continue.

You will need to link your google sheets account in the next step. Once you have done this, you will then be taken to the following window.

Step 11: Set up your Google Sheet

You will need to set up a google spreadsheet ready to take your results. You will also need to set up the columns to store the data in e.g. The food preference and the Facebook ID.
Now we can populate the above step in Zapier. In the spreadsheet, select your new spreadsheet. Select the worksheet in that spreadsheet that contains your new column headings. Under food preferred, select the user attribute ‘food’ that you set up in your bot. Under Workplace ID, select the Workplace ID that is pulled through by the webhook.

Click continue and your google sheets integration will be tested. You should now see the data from your test appearing in your spreadsheet. Go back to the bot builder and test your bot again and you will see more data appear in your spreadsheet.

Congratulations, you have built your first bot that can connect to a Google Sheet! You’re awesome!

Optimize your Workplace instance and make your staff more productive at work

Using custom integrations like the one in this tutorial will help extend the functionality of your Workplace instance.

With Google integrations like the one above, your enterprise software can integrate with bots on Workplace Chat to do things like collecting voting data for employee of the month, polls for food at the next office party, or saving ideas from your workforce about an upcoming project.

To learn more about custom integrations and more bot “how to’s,” head over to our help center for more information or drop us an email below.

How employers can help with the vaccine rollout

A look at the progress of the global vaccine rollout and the steps companies can take in helping their staff stay safe and informed.

5 Employee Experience opportunities you should be looking at in 2021

Amplify your employee experience in 2021 Written on 14th January 2021 In this article we…

Can a bot make you coffee?

While in lockdown, we are all spending more time in our own homes. Learn how to create a bot that automatically makes you coffee for you.