Google Sheets
The Google Sheets integration allows you to connect your Google Drive to Home Assistant. The integration adds a service 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:
- Go the Google Developers Console Google Drive API and Google Sheets API .
- Confirm the project and Enable the API.
- 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:
This section explains how to generate a client ID and client secret on Google Developers Console.
- First, go to the Google Developers Console to enable Google Drive API and Google Sheets API .
- Select Create project, enter a project name and select Create.
- Enable the Google Drive API.
- Navigate to APIs & Services (left sidebar) > Credentials.
- 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.
- You then need to select a Support email.
- From the dropdown menu, select your email address.
- Under Developer contact information, enter your email address (the same as above is fine).
- 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.
- 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.
- Select OAuth consent screen again and under Publishing status, select Publish app.
- Otherwise your credentials will expire every 7 days.
- Make sure Publishing status is set to In production.
- In the left sidebar, select Credentials, then select Create credentials (at the top of the screen), then select OAuth client ID.
- Set the Application type to Web application and give this credential set a name (like “Home Assistant Credentials”).
- Add
https://my.home-assistant.io/redirect/oauthto Authorized redirect URIs then select Create.- This is not a placeholder. It is the URI that must be used.
- 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.
- 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 integration to your Home Assistant instance, use this My button:
If the above My button doesn’t work, you can also perform the following steps manually:
-
Browse to your Home Assistant instance.
-
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.
-
Continue through the steps of selecting the account you want to authorize.
-
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.
-
You can now see the details of what you are authorizing Home Assistant to access with two options at the bottom. Select Continue.
-
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.
-
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.
Service google_sheets.append_sheet
You can use the service google_sheets.append_sheet to add a row of data to the Sheets document created at setup.
| Service 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 a new row, one value per column. | {“hello”: world, “cool”: True, “count”: 5} |
# Example service call
service: 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) }}"