home-assistant.io/source/_integrations/google_sheets.markdown

title, description, ha_category, ha_iot_class, ha_release, ha_config_flow, ha_domain, ha_codeowners, ha_integration_type, google_dev_console_link, api, api_link, api2, api2_link

title	description	ha_category	ha_iot_class	ha_release	ha_config_flow	ha_domain	ha_codeowners	ha_integration_type	google_dev_console_link	api	api_link	api2	api2_link
Google Sheets	Instructions on how to use Google Sheets in Home Assistant.	Utility	Cloud Polling	2022.10	true	google_sheets	@tkdrob	service	https://console.developers.google.com/start/api?id=drive	Google Drive API	https://console.developers.google.com/start/api?id=drive	Google Sheets API	https://console.cloud.google.com/apis/enableflow?apiid=sheets.googleapis.com

The Google Sheets {% term integration %} allows you to connect your Google Drive to Home Assistant. The integration adds an action to allow you to append rows to a Sheets document. The idea is that you can store data on there for further processing. When you set up a config entry, your drive will have a new sheet called Home Assistant. You can then rename this to whatever you like.

Note: The integration currently only has access to that one document that is created during setup.

Prerequisites

You need to configure developer credentials to allow Home Assistant to access your Google Account. These credentials are the same as the ones for Nest, YouTube, and Google Mail. These are not the same as Device Auth credentials previously recommended for Google Calendar.

{% include integrations/google_client_secret.md %}

{% include integrations/config_flow.md %}

{% include integrations/google_oauth.md %}

Troubleshooting

If you have an error with your credentials you can delete them in the Application Credentials user interface.

Video tutorial

This video tutorial explains how to set up the Google Sheets integration and how you can add data from Home Assistant to a Google Sheet.

Action google_sheets.append_sheet

You can use the google_sheets.append_sheet action to add rows of data to the Sheets document created at setup.

{% details "Create event action details" %}

Data attribute	Optional	Description	Example
config_entry	no	Config entry to use.
worksheet	yes	Name of the worksheet. Defaults to the first one in the document.	Sheet1
add_created_column	yes	Add created column containing date-time to the data being appended. Defaults to True.	True
data	no	Data to be appended to the worksheet. This puts the data on new rows, one value per column.	{"hello": world, "cool": True, "count": 5}

{% raw %}

# Example action
action: google_sheets.append_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  add_created_column: false
  data:
    Date: "{{ now().strftime('%-d-%b-%y') }}"
    KWh: "{{ states('input_number.car_charging_kwh')|float(0) }}"
    Cost: "{{ states('input_number.car_charging_cost')|float(0) }}"

# Example action with multiple rows
action: google_sheets.append_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  data:
    - Item: "Car 1 cost"
      Cost: "{{ states('input_number.car_1_charging_cost')|float(0) }}"
    - Item: "Car 2 cost"
      Cost: "{{ states('input_number.car_2_charging_cost')|float(0) }}"

{% endraw %}

{% enddetails %}

Action: Get sheet

You can use the google_sheets.get_sheet action to retrieve rows of data from a Sheets document.

{% details "Create event action details" %}

Data attribute	Optional	Description	Example
config_entry	no	Config entry to use.
worksheet	yes	Name of the worksheet. Defaults to the first one in the document.	Sheet1
rows	no	Maximum number of rows from the end of the worksheet to return.	2

{% raw %}

# Example action
action: google_sheets.get_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  rows: 2

{% endraw %}

{% enddetails %}

{% details "Example action response" %}

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

{% enddetails %}