Hi! Since version 2022.10 of Home Assistant, it’s possible to store data in Google Sheets. I will show you how in this tutorial!

---

⭐⭐⭐ NOTE: ⭐⭐⭐

This article accompanies a YouTube video. I wrote it for people who would rather read than watch a video. To keep doing this, I would like to ask you to also check out the video, leave a comment under the video, give the video a thumbs up, and subscribe to my YouTube channel. This means that the video is offered more often to new visitors so that they also stay informed of the latest Home Assistant tutorials.

Thank you for your support!

Ed

Click here to watch the video

One of my viewers asked me a while ago if it would be possible to save events in a Google Sheet. At that time it was a bit difficult to do so, but since version 2022.10 it is quite easy to store data in Google Sheets. I will show you how to set it up and how you can use it in an automation. You can also find the manual on this webpage. But, before we move on, please like this video and subscribe to my channel. I need your support so that I can keep creating these videos for you!

Create Google Developer Credentials

First, we have to make sure that we create developer credentials to allow Home Assistant access to your Google Account.

1. First, go to the Google Developers Console to enable Google Drive API and Google Sheets API
2. The wizard will ask you to choose a project to manage your application. Select a project and click continue.
3. Verify that your drive API was enabled and click ‘Go to credentials’
4. Navigate to APIs & Services (left sidebar) > Credentials
5. Click on the field on the left of the screen, OAuth Consent Screen.
6. Select External and Create.
7. Set the App Name (the name of the application asking for consent) to anything you want e.g. Home Assistant.
8. You then need to select a Support email. To do this, simply click the drop-down box and select your email address.
9. You finally need to complete the section: Developer contact information. To do this, simply enter your email address (the same as above is fine).
10. Scroll to the bottom and click Save and Continue. Don’t have to fill out anything else or it may enable additional review.
11. You will be taken automatically 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.
12. Click OAuth consent screen again and set Publish Status to Production otherwise, your credentials will expire every 7 days.
13. Set the Publishing status to production.
14. 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.
15. Set the Application type to Web application and give this credential set a name (like “Home Assistant Credentials”).
16. Add https://my.home-assistant.io/redirect/oauth to Authorized redirect URIs then click Create.
17. You will 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.
18. 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.

Set up Google Sheets in Home Assistant

Now that we’ve set up the developer credentials, we have to set up Google Sheets in Home Assistant. You might get an error during setup. That is mostly because there are already application credentials stored in Home Assistant for Google Sheets. In that case, you first have to remove these. I will first show you where you can remove this and after that, we will install the Google Sheets integration.

1. Browse to your Home Assistant instance.
2. In the sidebar click on  Settings.
3. From the configuration menu select Devices & Services.
4. In the bottom right, click on the  Add Integration button.
5. From the list, search and select “Google Sheets”.
6. Follow the instruction on the screen to complete the setup.
7. Continue through the steps of selecting the account you want to authorize.
8. 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.
9. You can now see the details of what you are authorizing Home Assistant to access with two options at the bottom. Click Continue.
10. 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.
11. You may close the window, and return back to Home Assistant where you should see a Success! message from Home Assistant.

Test Google Sheets in the developer tools

Google Sheets is now installed in Home Assistant. Let’s test it in the developer tools to see if we can add data to our Google Sheets.

Watch this part in the video to see how you can do this.

Fill Google Sheets in an automation

We’ve seen that the Google sheets integration is working. I’ll just show you one more thing and that is how you can use it in an automation and add actual data from a sensor to your Google Sheets.

Watch this part in the video to see how you can do this.

I hope that I managed to explain how you can connect Home Assistant to Google Sheets and how you can send data to your Google Sheets.

I want to thank everyone who has supported me in making these videos and tutorials so far. Thank you! I could never have done this without you. You can support me through Patreon, Ko-Fi, or by joining my channel. If you also want to support me, look in the description of this video for the links. With that, you make it possible that I can continue to make these videos for you.

Oh, don’t forget to give this video a thumbs up, subscribe to my channel, and hit the notification bell.

I will see you soon!

Bye bye!