How to pass data from a specific Google Sheets cell to a chatbot
You can integrate SendPulse chatbots with other services to request information via Make. In this article, we'll look at how to view data (numeric or text) from a specific cell in Google Sheets.
With the help of such a scenario, you can check the inventory of goods in your warehouse, the number of available apartments, prices based on the exchange rate, or a promotional code, and provide the user with up-to-date information.
This article provides an example of how to integrate Google Sheets with SendPulse through Make. You can choose any other service available in Make instead of Google Sheets. Note that the UI design may differ from the screenshots shown.
SendPulse also offers a direct integration with Google Sheets. Learn more.
How to get started
Log into your Make account, and click the + Create a new scenario button to start creating a scenario.
You can choose which services to connect — in this case, we want Webhooks and Google Sheets — using the search or find and add them directly in the constructor. Click the Continue button in the upper right corner.
How to create a webhook
Click on the element with the question mark at the center of your screen and select the Webhooks element — this element starts the flow.
You need to create a webhook that sends data from your chatbot flow. Select the Custom webhook element from the drop-down list.
Click the Add button to create a new webhook.
Enter a webhook name and click the Save button.
Your webhook has been created. Copy the link — you need to paste it into the flow of your chatbot.
Now Make waits to receive the first webhook to determine the data structure it will need to work.
How to add a webhook link to a chatbot flow
Log in to your SendPulse account. Select the bot you need and open the flow in the chatbot flow builder.
Add and place the API Request element right after the element you want to send the webhook. Select the GET request type, and enter the URL copied from Make.
You can build a scenario by requesting a value from a specific cell — for this, add this cell to the parameter as cell = B2.
You can also request a value from a cell that the user requested. If you saved this value through User input — add this cell to the parameter as cell = {{cell}}.
Click the Test request button to send the request to Make and recognize the received data.
When Make receives the webhook, you will see a "Successfully determined" message in the Webhooks element.
Now you can add more elements.
How to get data from Google Sheets
Click on Add another module next to the Webhooks element and select Google Sheets.
Select the Get a cell action.
Connect your Google Account, select a spreadsheet and sheet.
Choose a cell of the spreadsheet you will read. It can be a static cell (like B2) or a cell that you can pass from a chatbot (like a cell).
If you do not have a variable of the form 3.cell (where 3 is the element number in Make, in which you received this variable from the chatbot), click the Run once button in the bottom left corner and start the flow in the chatbot where you configured the webhook. After the first flow runs, the variable will appear in Make.
Click the OK button.
How to pass a record from Google Sheets to a chatbot
Click on Add another module next to the table element. Choose Webhooks > Webhook response.
In the Body field, select the Value that the previous Get a Cell element returns, and write it to the variable val — this value will be returned to the chatbot:
{"val":"4.Value"}
Click the OK button to save the parameters.
Go back to editing your chatbot flow in your SendPulse account.
You can save the resulting value to a variable to use a filter and add it to the message as a variable. Or, you can display the data to the user once, without saving, using an expression like {{$['variable']}}. Read more: Using data as a JSON path expression.
Place the Message element just after the API Request element, and add a variable or JSON path expression.
Save and run
Before saving and exiting, ensure that everything works correctly.
Click the Run once button and run the flow you configured the webhook for. If you have configured everything correctly, green checkmarks will appear under the Webhook and Google Sheets elements.
You can also click on the number above the element to view the received information transmitted to the next element.
To activate the scenario, move the toggle switch to the ON position.
How to view the scenario history
Navigate to the scenario you need. You will have access to a diagram of all of elements with their execution history.
Click on the required element to view information about it. You will have access to all the information on the element: what values it received and transmitted further.
If any of the elements have not been executed, they will be highlighted in red, and when pressed, the reason for the error and a hint on how to fix it will be displayed. Note that the entire scenario may pause and not execute until the error is resolved; operations will be queued.
If you have any questions about configuration or scenario failures, you can contact Make support.
Last Updated: 05.07.2024
or