A general description of the “External Query” action and other instructions on how to configure and use it can be found here.
General description
This integration will allow you to read data from the specified Google Sheet through the “External Query” action in order to later enter it in the person’s custom fields.
You will not be able to enter data to a Google Sheet using the “External Query” action.
To integrate, you will first have to set up a Google Account and the Google Sheet itself. Step-by-step instructions are below.
Google Settings
Google Sheet Settings:
- Create a Google Sheet which you will then read the data from.
- In the access settings of the spreadsheet, specify the access type “Anyone with the link” and the role “Editor”.
- To set up an “External Query”, copy the spreadsheet ID from the link of the spreadsheet page. An ID is a long combination of letters, numbers, and symbols. You will easily find it in the link of the page with the spreadsheet. For an example, see the screenshot below.
How to set up a Google Account and get an API key:
1. Log in to console.cloud.google.com with the account that contains the required Google Sheet.
2. Create a new project:
- Click the Select Project button in the upper-left corner.
- In the window that pops up, click the New Project button in the upper-right corner.
- In the tab that opens, enter the project name and click CREATE.
- After that, you will be redirected to the previous page, and the project will be created in a few seconds.
- Click the Select Project button in the upper-left corner again and select the newly created project. Now you are working on this project.
3. Activate the API and get the API key:
- You have to activate API methods that will allow you to read data from the spreadsheet. To do this, open the left navigation dashboard and go to the “APIs & Services” section.
- On the page that opens, click the + ENABLE APIS AND SERVICES button at the top of the screen.
- In the “API Library” page that opens, scroll down to the “Google Workspace” section, find and click the “Google Sheets API” icon.
- Next, click the ENABLE button. After a few seconds, this API will be activated, and you will be redirected to the next page.
- On the page that opens, click 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 the value “Web Application” and enter a name. Click the CREATE button.
- After a few seconds, the “Your Credentials” section will load, and then click DONE. You will be redirected to the previous page.
- On the page that opens, go to the “Credentials” page in the left navigation dashboard.
- Click + CREATE CREDENTIALS and select “API key”.
- An API key will be generated in a few seconds. Copy it, you will have to use it when setting up the “External Query”.
- This API key will not disappear, you can copy it later in the same section.
Flow settings in BotHelp
1. How to set up a simple flow chain to send a query and display the received data in a custom field:
- Create a flow chain of three consecutive blocks:
- “Message 1” -> “Action 1” -> “Message 2”.
- In the “Message 1” block, we will just add a text greeting.
- In the “Action 1” block, we will set up an “External query”.
- In the “Message 2” block, we use a macro to output a custom field which the data read from the Google Sheet will be recorded in.
2. Setting up “External Query”:
- In the “Action” block, select “External Query” from the list of actions and open the window to configure the “External Query” by clicking on the “pencil”.
- Specify the GET query type.
- In the “URL” field, insert the link:
https://sheets.googleapis.com/v4/spreadsheets /{%google table id%}/values/{%address of a cell or range in the table %}?key={%API key%}
- Insert your data into the link:
- the ID of the Google Sheet which you want to read the data from instead of {%Google table id%}
- the address of the cell or range (e.g., A1 or DJ22), where you want to read the values from instead of {%the address of the cell or range in the table %}
- The API key obtained 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 link will return the value of cell A1.
- Go to the “Response” section to test the query and get a test response. In the test response, you will be able to see the structure of the response body, which you will need to configure the matching of responses (save settings to custom fields).
- Click Send Query. The received response will be displayed on the right side. Open the “Response body” section.
- Received response body:
{
"range": "'Лист1'!A1",
"majorDimension": "ROWS",
"values":
[
[
"raz"
]
]
}
- The value that is entered in the spreadsheet is in the “Values” field. The value itself is “raz”.
- Go to the “Matching responses” tab to configure the logic of entering the received data in a custom field.
- In the “JSON Path” field, enter $.values.0.0
- The value “JSON Path” will be returned from the response body by the “raz” value.
- Detailed instructions on the JSON Path are here: link.
- In the “Custom Field” field, select the field where you want to save the received value.
- Done. This completes the settings for the “External Query”.
- Click Save in the settings window of the “External Query”, be sure to click Save and Close in the settings window of the “Action” block.
Now you can test the flow. Two messages will be sent from it: a greeting and one containing the value from cell А1 received from the Google Sheet. 
The full instructions for the “External Query” action are here: link.
Frequently asked questions
1. Is it possible to use variables when forming a link? 
Answer: Yes, it is. In an external query, the URL field supports macros. For example, you can request a certain number from a person and enter this value in their field. Then, in the query link, you can generate the address of the cell which you want to read the value from using a macro for the person field. When sending a query, the macro will automatically be replaced with the specific value that the person previously entered. 
2. If, depending on the conditions that are formed in the flow chain, a query has to be made for the values of certain cells, how can the cell address be generated inside the flow?
Answer: You can insert an address for cell R3C2, where R3 is the third row (row 3), C2 is the second column (column 2). In the person’s “number” field, enter the desired value before the desired flow step.
If you haven’t found the answer to your question, contact us in the chat in your profile or email [email protected]😃
Get 14 days of full functionality of BotHelp, a platform for creating broadcasting, autofunnels, and chatbots.