Your voice matters 🫵 Take part in our 2024 Employee Experience Survey

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

In this tutorial, learn how you can easily build a voting bot that automatically populates into a Google Sheet.
Written by

Look, we get it. How useful can a tool really be if it can’t connect with the platforms you use every day?

Look, we get it. How useful can a tool really be if it can’t connect with the platforms you use eye day? One of the most requested features of a bot on Workplace is the ability to integrate with Google Sheets. There are an estimated 160–180 million Google Sheets users, and for those of you who live and breathe by the cloud as we do, this tutorial is for you.

In this tutorial, learn how you can easily build a voting bot that automatically populates into a Google Sheet.

Knowledge Required

Intermediate. You need a good working knowledge of how to create a bot and basic Google Suite knowledge.

What you will need

A Bot Platform account
– A computer
– A Workplace from Facebook instance
– System Admin rights for your Workplace instance
– A Google spreadsheet
– A Zapier account

Step 1: Create an account on The Bot Platform

Before you do anything you need to create an account on The Bot Platform.
Once you have done this, click the create a new bot button and you are then given the option to link it to your Facebook account. You will need to make sure that you are logged in to Facebook with the admin account for your Facebook page. Please note, it is better to build your bot on a test page before you go live on your real page, so you may want to set up a new Facebook page purely for bot building.

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.

Browse more popular success stories using Microsoft Teams & Workplace

Whatever you need to build
build it with The Bot Platform