Qubot Google Sheets Setup

Connecting Google Sheets to a Bot

By connecting your Google spreadsheets to the bot, your bot experience will improve significantly. For example, your bot will be able to download data directly from your Google spreadsheet, as well as upload new data in real time. Using Google Sheets will make your work much easier, because you will have direct access to read and edit the saved data at any time without interfering with the bot code.

The instructions below will help you to share Google spreadsheets:

Step 1

Sign in or create an account at https://accounts.google.com

Step 2

Open your Google Sheets here https://docs.google.com/spreadsheets

You can also access your Google Sheets from the Google homepage(https://www.google.com/) by clicking the Google apps button.

Step 3

Share the Google Spreadsheet with your bot:

In your Google Sheets, open a spreadsheet and click the Share button.



Share the access in any of the possible ways. First way. Share with a specific account: copy the following account into the input field of the sharing window and send the invitation.

qudata-bot-service@qudata-services.iam.gserviceaccount.com



Second way. Get shareable link: in the Get link section change status Restricted to Anyone with the link, and click Done.



Please note that you can control the access: if the bot will only read data from Google spreadsheet, then the Viewer role is enough, and if the bot will also save data to Google spreadsheet, then the Editor role is required. You can change the role at any time in the sharing settings.

Step 4

Set up data loading to a spreadsheet in the bot editor. Additional information about this can be found in the bot editor templates (e.g. Trivia, Order Food templates) or in our help center.

How to set up Google Spreadsheet data in a bot

Choose the required bot and go to the Editor.

Loading data from a spreadsheet

1. Select a step in your bot to download data from the Google Sheets (for example, Main).



2. Select/create an element to have its action load data from the spreadsheet. It can be buttons, actions or just entering any text by the user (click to show actions when) and other elements.


3. In the Item properties field you can add a property:


4. Select the load property:



5. In the load property select Google Sheets from the list



6. In the Google Sheets table url field enter the url (link) of your Google Sheet




7. In the Slot name field enter the name of the variable that will store data from your spreadsheet as an array of objects:



Use this variable to work with your data.

Make sure you give access to view or edit your spreadsheet

Sending data to a spreadsheet

Go through steps 1-3 described above in "Loading data from a spreadsheet" and choose a step where to send data to the spreadsheet.

4. Select the save property:



5. In the save property select Google Sheets from the list



6. In the Google Sheets table url field enter the url (link) of your Google Sheet




7. In the Data value field enter the data that will be sent to the spreadsheet in the specified format as an object:

Make sure you give access to edit your spreadsheet

Working with filters

You can use filters to receive selective data or change existing ones from the bot.
Filters are a data dictionary with the following structure: { 'COLUMN': DATA, ... }
You can specify multiple columns in one filter.
All columns in one filter are considered as a logical "AND", that means the filter will look for those rows that have all the specified data in all the specified columns.

Selective data loading from the table

1. Set up your bot to load data according to the instructions above.
2. Specify a filter in the form of a dictionary in the Data filter field.

3. Only values from the columns, that correspond to the entered, will be recorded as data to the SLOT_DATA dictionary.

Editing data in the table

1. Set up your bot to save data according to the instructions above.
2. Specify a filter in the form of a dictionary in the Data filter field.

3. Data from the table that match the filter will be edited.
If the filter did not work, then the data will not be recorded.

Make sure you give access to edit your spreadsheet