How to request cells in Google Sheets via Make

You can integrate SendPulse chatbots with other services to request information via Make. In this article, we will explain how to view cells in Google Sheets. This will allow you to build a scenario where you can send the user information about what time and day are available for booking.

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 your chatbot flow.

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 a Message element. Add buttons for each value passed to Make (in this case, the day of the week).

You can send a separate request for each day of the week or add a message "User data entry" to the element, write the response with the day of the week to a variable, and then pass this variable in the API request element.

Add and place the API Request element right after the element you want to send the webhook. Select the POST request type, and enter the URL copied from Make.

You can send personalized values, such as the day of the week, by adding parameters in the "day": "Monday" form to the element request body.

Example of a request with a variable:

Click the Test request button to send the request to Make so the system can recognize the received data structure.

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 Search Rows action.

Connect your Google account, select a spreadsheet and sheet. In Table contains headers select No. In the Column range specify A-ZZ.

Specify the filter by which you want to select a row (for example, by the day of the week), and click the OK button. In the first field, select a column from Google Sheets. In the second field, select a variable with the passing day.

If you do not have a variable in the form 1.day (where 1 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.

An example of a spreadsheet by day of the week:

You can also request date information and select different sheets within the same Google document.

How to pass a record from Google Sheets to a chatbot

Click on the question mark or Add another module next to the table element. Choose Webhooks > Webhook response.

In the Body field, enter the values ​​that will be returned to the chatbot and write them in JSON format.

In this case, available cells in Google Sheets will be recorded in the message variable. To do this, you need to specify the if function in the format (3.B; "8:00 booked,"; "8:00 available,"), where 3.B is the value in cell B, obtained from the previous Search Rows element. If the cell is not empty, the webhook will return "8:00 booked", otherwise — "8:00 available".

An example of a response body (before formatting, you can add your text, functions, and variables to display the text differently):

{"message": "{{2.`0`}}: {{if (2.`1`;" 8:00 booked, ";" 8:00 available, ")}} {{if (2. `2`;" 9:00 booked, ";" 9:00 available, ")}} {{if (2.`3`;" 10:00 booked, ";" 10:00 available, ")}} {{if (2.`4`; "11:00 booked,"; "11:00 available,")}} {{if (2.`5`; "12:00 booked,"; "12:00 available, ")}} {{if (2.`6`;" 13:00 booked, ";" 13:00 available, ")}} {{if (2.`7`;" 14:00 booked, ";" 14:00 available, ")}} {{if (2.`8`;" 15:00 booked, ";" 15:00 available, ")}} {{if (2.`9`; "16:00 booked,"; "16:00 available,")}} {{if (2.`10`; "17:00 booked"; "17:00 available")}} "}

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 it as a variable, using an expression like {{$['variable']}}. Read more: Using data as a JSON path expression.

Add a Message element with a response from the webhook. You can add your text, subscriber variables, or display only one response.

Repeat the previous steps in the chatbot flow for other days of the week.

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 check marks will appear under the Webhook and Google Sheets elements.

To activate the scenario, move the toggle switch to the ON position.

How to view scenario history

Navigate to the script you want. In the Last runs or History tab, click Details for the desired operation. You will be presented with the element execution history diagram.

Click on the required element to view information about it. You will have access to all the information in the element — what values ​​it received and transmitted.

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: 17.06.2024