2024.11.0
Home ▸ Integrations ▸

Google Sheets

The Google Sheets integration allows you to connect your Google Drive to Home Assistant. The integration adds an actiion 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.

Scenario 1: You already have credentials

In this case, all you need to do is enable the API:

  1. Go the Google Developers Console Google Drive API and Google Sheets API .
  2. Confirm the project and Enable the API.
  3. Continue with the steps described in the Configuration section.

Scenario 2: You do not have credentials set up yet

In this case, you need to generate a client secret first:

To generate client ID and client secret

This section explains how to generate a client ID and client secret on Google Developers Console.

  1. First, go to the Google Developers Console to enable Google Drive API and Google Sheets API .
  2. Select Create project, enter a project name and select Create.
  3. Enable the Google Drive API.
  4. Navigate to APIs & Services (left sidebar) > Credentials.
  5. In the left sidebar, select OAuth consent screen.
    • Select External and Create.
    • Set the App name (the name of the application asking for consent) to anything you want, e.g., Home Assistant.
  6. You then need to select a Support email.
    • From the dropdown menu, select your email address.
  7. Under Developer contact information, enter your email address (the same as above is fine).
  8. Scroll to the bottom and select Save and continue.
    • You don’t have to fill out anything else here. Adding other information to this page (like an app logo) may trigger an additional review process from Google and delay setup by days.
  9. You will then be automatically taken to the Scopes page.
    • You do not need to add any scopes here. Select Save and continue to move to the Test users page.
    • You do not need to add anything to the Test users page. Select Save and continue, which will take you to the Summary page.
    • Select Back to dashboard.
  10. Select OAuth consent screen again and under Publishing status, select Publish app.
    • Otherwise your credentials will expire every 7 days.
  11. Make sure Publishing status is set to In production.
  12. In the left sidebar, select Credentials, then select Create credentials (at the top of the screen), then select OAuth client ID.
  13. Set the Application type to Web application and give this credential set a name (like “Home Assistant Credentials”).
  14. Add https://my.home-assistant.io/redirect/oauth to Authorized redirect URIs then select Create.
    • This is not a placeholder. It is the URI that must be used.
  15. You will then be presented with a pop-up saying OAuth client created, showing Your client ID and Your client secret.
    • Make a note of these (for example, copy and paste them into a text editor), as you will need them shortly.
    • Once you have noted these strings, select OK.
    • If you need to find these credentials again at any point, then navigate to APIs & Services > Credentials, and you will see Home Assistant Credentials (or whatever you named them in the previous step) under OAuth 2.0 Client IDs.
    • To view both the Client ID and Client secret, select the pencil icon. This will take you to the settings page for these credentials, and the information will be on the right-hand side of the page.
  16. Congratulations! You are now the keeper of a client secret. Guard it in your treasure box. In most cases your new credentials will be active within a few moments. However, Google states that activation may take up to five hours in some circumstances.

Configuration

To add the Google Sheets service to your Home Assistant instance, use this My button:

Manual configuration steps

If the above My button doesn’t work, you can also perform the following steps manually:

  • Browse to your Home Assistant instance.

  • Go to Settings > Devices & Services.

  • In the bottom right corner, select the Add Integration button.

  • From the list, select Google Sheets.

  • Follow the instructions on screen to complete the setup.

The integration setup will next give you instructions to enter the Application Credentials (OAuth Client ID and Client Secret) and authorize Home Assistant to connect to Google Sheets.

OAuth and device authorization steps

  1. Continue through the steps of selecting the account you want to authorize.

  2. NOTE: You may get a message telling you that the app has not been verified and you will need to acknowledge that in order to proceed.

  3. You can now see the details of what you are authorizing Home Assistant to access with two options at the bottom. Select Continue.

  4. The page will now display Link account to Home Assistant?, note Your instance URL. If this is not correct, refer to My Home Assistant. If everything looks good, select Link Account.

  5. You may close the window, and return back to Home Assistant where you should see a Success! message from Home Assistant.

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.

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
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} 
# Example action
action: google_sheets.append_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  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) }}"