Get data from Google sheet

Use this action to retrieve rows of data from a Google Sheets document, for example, to read back values you stored earlier. This action returns response data that you can use in the rest of your automation or script.

Using this action from the user interface

If you prefer building automations and scripts visually, Home Assistant walks you through this action step by step. You pick what to target, tweak a few options, and save. No YAML knowledge required.

To get data from an automation or a script:

1. Go to Settings > Automations & scenes.
2. Open an existing automation or script, or select Create automation > Create new automation.
3. If you’re setting up a new automation, add a trigger in the When section. Scripts don’t need a trigger. They run when something else calls them.
4. In the Then do section, select Add action.
5. From the search box, search for and select Google Sheets: Get data from Google sheet.
6. Under Integration, select the Sheet you want to read from.
7. Optionally, set a name for the Worksheet. Defaults to the first one in the document.
8. Set the number of Rows to return.
9. In the Response variable field, enter a name to store the data in, such as sheet_data.
10. Select Save.``

This action does not support targets. You select the document through the Config entry field instead of choosing an area, device, entity, or label.

Options in the UI

Using this action in YAML

If you work directly in YAML, or you want to know exactly what Home Assistant does under the hood, this section has the technical reference. It lists the field names you use in YAML, their types, and which ones are required. For how an automation or a script is built in YAML, refer to Automations in YAML and Scripts.

In YAML, refer to this action as google_sheets.get_sheet. A basic example looks like this:

action: google_sheets.get_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  rows: 2
response_variable: sheet_data

This returns the last two rows from the Car Charging worksheet.

Options in YAML

Response data

The action returns a range with the requested rows. Each row is a list of cell values.

range:
  - - 04/07/2024
    - 9 Kw
  - - 05/07/2024
    - 8 Kw

Try it yourself

Ready to test this? Open Settings > Tools > Actions, search for this action, fill in the fields, and select Perform action. You see what happens on your actual entitiesAn entity represents a sensor, actor, or function in Home Assistant. Entities are used to monitor physical properties or to control other entities. An entity is usually part of a device or a service. [Learn more] without writing a line of YAML.

Still stuck?

To test the action on its own, run it in the automation editor with Run action, and check the message that it shows. To see what the action did when the automation ran, open the trace of that run. For details, refer to Testing automations. For common problems and their solutions, refer to Troubleshooting automations.

If that doesn’t help, the Home Assistant community is quick to help: join Discord for real-time chat, post on the community forum with the action you’re calling and what you expected to happen, or share on our subreddit /r/homeassistant.

Related actions

Explore related actions:

• Append data to Google sheet: Adds rows of data to a Google Sheets document.