How to pull data from a Google spreadsheet

In this guide, Svetlana Gizatulina explains how to pull data from Google Sheets and display it in your bot.
As an example, we'll show an order status. To get it, the user enters their order number in the bot.

Step 1 — Connect the bot to puzzlebot

Learn how to do it in the Knowledge base: https://help.puzzlebot.top/article?r=3&a=18

Step 2 — Set up the integration with your Google account

Learn how to do it in the Knowledge base: https://help.puzzlebot.top/article?r=17&a=159

Step 3 — Create and prepare a spreadsheet

In your Google account, create a spreadsheet, rename it for convenience and add column names, for example:
Column A - Order number
Column B - Order status

Step 4 — Create and set up commands

Go to the Constructor tab and delete the preset commands:

1. Hold shift+alt, select all the commands with the left mouse button and delete them.

2. Apply the changes.

Create the first command:
1. Click + (add command).
2. Select a regular command.
3. Enter the command name "Order number request".
4. Select the Input form block.

5. Add a message: "Enter your order number 👇".
6. Enter a name for statistics.
7. Specify a variable.
8. Select the input type: sending a message.

Step 5 — Create a variable

Create a variable:

1. Go to the Variables tab.
2. Click Add variable - Personal variable.
3. Enter the variable name.
4. Select Integrated.
5. Select Google Sheets.
6. Add a default message:
"Sorry, the order wasn't found 😔 You may have made a mistake when entering the order number. Try again using the "Check another order" button 👇".

Important!
If the order number isn't found in the spreadsheet, the bot will show the default value.

7. Paste the link to the spreadsheet you created earlier.
8. Select the Sheet you need. Search is selected by default; leave it as is.
9. Select the column to search in (the search uses the order number the customer enters).
10. For the value format, select Text, then add the variable to search by.

11. Select the column to pull data from.
Leave the value output set to from cell. If needed, you can output the whole row (if other columns hold more data, such as a date, the employee in charge, a contact number and so on). In the description, you can optionally add a comment to make it easier to remember later what the variable was created for.

When you're done with the settings, click Save.

Step 6 — Create the second command

Go back to the Constructor tab

Create the second command:
1. Click + (add command).
2. Select a regular command.
3. Enter the command name "Result".
4. Select the Text block and write a message that includes the integrated variable you created: "Your order status: StatusZakaz".

Step 7 — Link the commands

Link all the commands together:
1. In each command, open the Actions section one by one.
2. Select Send command or condition and enter the name of the command you need.
3. Apply all changes.