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 the one for Google Calendar. If you have already set up the correct credentials, you can do step 1 and then skip to step 13 on the below instructions.
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
- The wizard will ask you to choose a project to manage your application. Select a project and click continue.
- Verify that your drive API was enabled and click ‘Go to credentials’
- Navigate to APIs & Services (left sidebar) > Credentials
- Click on the field on the left of the screen, 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. To do this, simply click the drop down box and select your email address.
- You finally need to complete the section: Developer contact information. To do this, simply enter your email address (same as above is fine).
- Scroll to the bottom and click Save and Continue. Don’t have to fill out anything else or it may enable additional review.
- You will then be automatically taken to the Scopes page. You do not need to add any scopes here so click Save and Continue to move to the Optional info page. You do not need to add anything to the Optional info page so click Save and Continue which will take you to the Summary page. Click Back to Dashboard.
- Click OAuth consent screen again and set Publish Status to Production otherwise your credentials will expire every 7 days.
- Make sure Publishing status is set to production.
- Click Credentials in the menu on the left hand side of the screen, then click 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/oauth to Authorized redirect URIs then click Create. This is not a placeholder and 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 these shortly. Once you have noted these strings, click OK. If you need to find these credentials again at any point then simply 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, click on 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.
- Double check that the Google Drive API has been automatically enabled. To do this, select Library from the menu, then search for Google Drive API. If it is enabled you will see API Enabled with a green tick next to it. If it is not enabled, then enable it.
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 access your 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. Click Continue.
-
The page will now display Link account to Home Assistant?, note Your instance URL. If this is not correct, please refer to My Home Assistant. If everything looks good, click 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) }}"