A general description of the External Request action and other instructions on how to set it up and use it can be found here .
General description
to read via the External Query action , and then write it to the subscriber's custom fields. to write data to a Google Sheet via the External Query action .
To integrate, you will first need to set up a Google account and the Google spreadsheet itself. Step-by-step instructions below.
Settings in Google
Google Sheets Settings:
- Create a Google Sheet from which you will then read data.
- In the table access settings, specify the access type "Everyone with the link" and the role "Editor".
- To set up an "External Query", copy the table ID from the table page link. The ID is a long combination of letters, numbers, and symbols. There is nothing similar in the table page link, see the screenshot below for an example.
How to set up a Google account and get an API key:
1. Log in to console.cloud.google.com under the account that contains the required Google spreadsheet.
2. Create a new project:
- Click on the Select a project in the upper left corner.
- In the window that opens, click on the New project in the upper right corner.
- In the opened tab, specify the project name and click CREATE .
- Afterwards you will be redirected to the previous page, and in a few seconds the project will be created.
- Click the Select a project in the upper left corner again and select the project you just created. You are now working in this project.
3. Activate the API and get an API key:
- You need to activate the API methods that will allow you to read data from the table. To do this, open the left navigation panel and go to the "APIs & Services" section.
- On the page that opens, click on the + ENABLE APIS AND SERVICES at the top of the screen.
- In the "API Library" page that opens, scroll down to the "Google Workspace" section, find and click on the "Google Sheets API" icon.
- Next, click on the ENABLE . After a few seconds, this API will be activated and you will be redirected to the following page.
- On the page that opens, click the CREATE CREDENTIALS in the upper right corner.
- In the window that opens, select the “User data” property and click NEXT .
- In the next section, fill in the fields: the name of your application and two fields with your email. Click SAVE AND CONTINUE .
- Do not fill in the “Scopes (optional)” section, click SAVE AND CONTINUE .
- In the "OAuth Client ID" section, in the "Application type" field, specify "Web application" and enter a name. Click the CREATE .
- After a few seconds, the "Your Credentials" section will load, then click the DONE . You will be redirected to the previous page.
- On the page that opens, in the left navigation panel, go to the "Credentials" page.
- Click the + CREARE CREDENTIALS and select "API key".
- In a few seconds, an API key will be generated. Copy it, you will need to use it when setting up the "External Request".
- This API key will not disappear anywhere, you will be able to copy it later in this section.
Bot settings in BotHelp
1. How to set up a simple bot chain to send a request and display the received data in a custom field:
- Create a bot chain of three consecutive blocks:
- "Message 1" -> "Action 1" -> "Message 2".
- In the “Message 1” block, we will add a simple text greeting.
- In the “Action 1” block, we will set up “External request”.
- In the “Message 2” block, we use a macro to display a custom field into which data read from the Google spreadsheet will be written.
2. Setting up the "External Request":
- In the Action block, select External Query from the list of actions and open the External Query setup window by clicking on the pencil.
- Set the request type to GET.
- In the "URL" field, paste the link:
https://sheets.googleapis.com/v4/spreadsheets/ {%id of Google Spreadsheet%} /values/ {%address of cell or range in spreadsheet%} ?key= {%API key%}
- Please insert your details into the link:
- ID of the Google Sheet from which to read data instead of {%Google Sheet ID%}
- the address of a cell or range (e.g. A1 or DJ22) from which to read values instead of {%address of a cell or range in a table%}
- API key received in your Google account settings, instead of {%API key%}
- The final link should look like this:
- https://sheets.googleapis.com/v4/spreadsheets/ 1OttAA3aWMJe-KrvhLZsm3UD6u0tIvaC8 /values/ A1 ?key= AIz3X-zW-6H_oQdPY-XuzF_Kh4
- This reference will return the value of cell A1.
- Go to the Response section to test the request and get a test response. In the test response, you will be able to see the structure of the response body, which will be needed to configure response mapping (save to custom fields settings).
- Click the Send Request . The received response will be displayed on the right side. Open the "Response Body" section.
- Received response body:
{ "range": "'Sheet1'!A1", "majorDimension": "ROWS", "values": [ [ "raz" ] ] }
- The value that is written in the table is in the "values" field. The value itself is "raz".
- Go to the Response Mapping tab to configure the logic for recording the received data in the custom field.
- In the "JSON Path" field, enter $.values.0.0
- This "JSON Path" value will return the value "raz" from the response body.
- Detailed instructions about JSON Path are here: link .
- In the Custom Field field, select the field in which you want to save the received value.
- Done. This completes the External Request settings.
- Click Save in the External Request settings window, and be sure to click Save and Close in the Action block settings window.
Now you can test the bot. It will send two messages: a greeting and one with the value of cell A1, obtained from a Google spreadsheet.
Full instructions for the "External Request" action are here: link .
Frequently asked questions
1. Is it possible to use variables when forming a link?
Answer: Yes, you can. In an external request, the URL field supports macros. For example, you can request a number from a subscriber and write this value to their field. Then, in the request link, you can form the address of the cell from which you need to read the value using a macro for the subscriber's field. When sending a request, the macro will automatically be replaced with the specific value that the subscriber previously entered.
2. If, depending on the conditions that are formed in the bot chain, it is necessary to request the values of certain cells, how can the cell address be formed inside the bot?
Answer: You can insert the address for cell R3C2, where R3 is the third row (row 3), C2 is the second column (column 2). In the subscriber's "number" field, write the desired value before the desired bot step.
If you have not found the answer to your question, ask us in the chat inside your account or write to BotHelpSupportBot or to hello@bothelp.io
Get 14 days of full functionality of the platform for creating mailings, autofunnels and chatbots BotHelp.