Read data from Google Sheets

tip

For this functionality, you can also use the ready-made Google Sheets integration block.

You can store information in Google Sheets and use it as needed. To read data from Google Sheets, use the HTTP request block. Aimylogic allows your bots to get all the data from a Google Drive spreadsheet as a JSON object, which can then be used in a script.

How to read data from Google Sheets

If your Google Sheets has data separated by a period or comma (e.g., dates such as 03.04 or 05.12), then skip the Create a spreadsheet and Get link sections of the current article. Follow the instructions for converting spreadsheets from CSV to JSON first. Then return to the section of the current article on adding HTTP requests.

Create a spreadsheet

Create a new spreadsheet in Google Sheets and design it according to your needs. Publish your spreadsheet: File → Share → Publish to web → Publish.

You do not need to copy the path from the pop-up window. Close the pop-up window.

You only need to publish the sheet once. There is no need to publish the sheet again after making changes to it.

You also need to allow access to the spreadsheet by link. To do this, click Share in the upper right corner of the page, then in the new window select Anyone with the link. Set the access mode as Viewer. Click Done to complete the setup.

Get link

Open Notepad and paste the link there, which should look like this:

https://tools.aimylogic.com/api/googlesheet2json?sheet=<sheet_name>&id=<spreadsheet_id>

• You can get the <spreadsheet_id> parameter from the spreadsheet’s URL. Copy and paste it into the link above after id=.

  

• The <sheet_name> parameter is specified at the bottom of the spreadsheet on the sheet’s tab. Copy and paste it into the link above after sheet=.

If you need to get data from a sheet named Sheet1 from a spreadsheet with the URL above, then the link will look like this:

https://tools.aimylogic.com/api/googlesheet2json?sheet=Sheet1&id=1GaCjqsFRk7ZItwIiPzGaYuG04wU9qrzdvMWnm4cxvMI

To check the URL’s correctness, paste it into your browser. If the URL is correct, you will see the spreadsheet’s content as a JSON array.

If you need to access different sheets of the same spreadsheet in one script, you will need to create different HTTP requests for this. One HTTP request retrieves data only from one sheet.

tip

Learn more on how to select the desired sheet directly in the bot script

Add an HTTP request to a spreadsheet

Create an HTTP request block in your script and paste the line copied from Notepad into the URL.

Fill out the rest of the HTTP request fields according to your needs.

As a result of the HTTP request, the service will return an array. The array elements contain JSON objects with fields corresponding to your spreadsheet column names. Further in the script, you can retrieve data from the array following the appropriate syntax.

[
    {
        "SKU": 657486,
        "Name": "Item 1",
        "Price": 1500,
        "Amount": 5
    },
    {
        "SKU": 386756,
        "Name": "Item 2",
        "Price": 4560,
        "Amount": 2
    },
    {
        "SKU": 396735,
        "Name": "Item 3",
        "Price": 1200,
        "Amount": 11
    },
    {
        "SKU": 874533,
        "Name": "Item 4",
        "Price": 3520,
        "Amount": 8
    }
]

How to read data from several Google Sheets

You can read data from a particular Google Sheet depending on a script branch. To do it, you do not need to create multiple HTTP requests.

To set URLs for different Google Sheets, you can use variables.

For example, if the Google Sheet you want to use depends on the button the client selects, you can save their choice into a variable via the text to $var block.

1. Add the text to $var block.
2. From the text to $var block, create a new screen with the Conditions block. In the Conditions block, specify the conditions with Google Sheet identifiers using the conditional operator, for example:

• $id = ($sheet == "Sheet 1") ? "1-4ZlVWhzYEAjSdfL4j7FKjhD1MWKkBkj4pgMzuKEIzU" : false

• $id = ($sheet == "Sheet 2") ? "1-4ZlVWhzYEAjSu167H1mb6J4HMWKkBkj4pgMzudlH7q" : false

• $id = ($sheet == "Sheet 3") ? "1jUN8g5SRPEGKhs0lULQoOUHeXV6bB9_sD6WLjsh6Hya" : false

  

3. Connect each condition with a new screen with an HTTP request block.

4. In the HTTP request block, specify the URL as follows:

   https://tools.aimylogic.com/api/googlesheet2json?sheet=Sheet1&id=${id}

   

The script example:

How to write data to spreadsheets

If you need to save data to spreadsheets, use the IFTTT service.

If you need to write something into a spreadsheet, follow the instructions for using the IFTTT service.

If you need to retrieve any data from your spreadsheet, follow these instructions for reading data from Google Sheets.

Troubleshooting

Error -1

• Syntax error. Perhaps there is a space or some other extra characters in the URL field of the HTTP request. Repeat the steps carefully from the beginning.
• You may have filled out other fields in the HTTP request incorrectly, such as the BODY field.

Error 404

• Make sure you’ve published the page. Repeat the Create a spreadsheet step.
• Make sure you get the document ID correctly. Go back to the Get link step and repeat it. Please note that after you have published your page, a pop-up window appears and you are prompted to copy the link. There is no need to copy the path and no need to try to get the document ID from the pop-up window.
• Try pasting the full link into your browser. If you did everything correctly, then you should see the spreadsheet content as a JSON array.