Google Sheets and CSV files in Home Assistant UI & automation

home assistant 2022.10 or October release home assistant has brought Google Sheets integration and I did already show you how you can activate it today we will be looking at what can you do with Google sheet integration how you can show the Google Sheets inside your home assistant but also one additional integration or service that you can use to store your data locally if you don't want to store it in the cloud let's get started with today's video thank you Google Sheets integration is the integration that allows you to save data from within your home assistant to the Google Drive inside Google Sheets which is equivalent to Excel file so it's actually saving data in the table from within home assistant while in the release video of 2022.10 I did show you on how to activate this integration let's quickly go through the steps the credentials that are used for this are the same ones for the nest if you already have those credentials set up you can skip all the steps up to step 13. let me open this Google developers console I will confirm that this is the project I want to use I will enable Google Drive API on the left menu go to API and services and on the credentials click on create credentials or out client ID application type has to be web application and for the name we can use something like this home assistant credentials click on ADD Ure to authorize redirects urls and paste the link from the documentation the link is https my.homeassistant.io redirect slash oauth and the last step we have to do here is press create and here we have our client ID and client secret we will need this client ID and client Secret in our home assistant if you by chance forget and didn't download the Json file with the client ID and client secret you can click on the link with the name home assistant credentials and here on the right side you will see them in home assistant in the Integrations page click on plus sign to add new integration we are looking for Google Sheets and fill in the necessary data give it a name I call this one home assistant Google sheet copy your client ID and of course your client secret if everything has been set up correctly when we click on ADD we will be redirected to select our account we will click on continue continue and we have to link our account be careful that this instance here is the instance that you want to use and if everything has been set up correctly you should see something like this successfully authenticated and the spreadsheet has been created and this is the URL to that spreadsheet let's press finish while there shouldn't be any errors if you followed all the steps correctly you may end up with some problems in the log files let's look at couple of possible solutions that can help you fix the errors in order for you to find out if the Google Sheets API is active click on the navigation menu apis and services and click on enabled apis this is the list of all the apis that have been enabled you can click on enable apis and services and type here sheets if the API is disabled you can click on enable button here in my case I have already enabled it the next thing that we want to make sure is that the Google Drive API is also enabled let's go back type here drive Google Drive API and also in my case this Google API drive has been unable too okay so at this point you should have Google Sheets running and integrated inside home assistant the question is what and how you can use the Google Sheets integration home assistant to store data I have been using one alternative method to save all the data locally on my hard drive and this is something that we'll be looking later on as alternative to Google Sheets but unfortunately this is a text file and Google Sheets has advantage that it allows you to also create graphs so let's first look on how you can add that to your home assistant automations in previous video where I was talking about how you can save the energy we created two automations one is called freezer getting warm and the other one is called fridge getting warm so let's add Google Sheets to these automations and see how we can save the data inside Google sheet while running those automations let's look at this freezer getting one one and at the end we have send notification with Billy bot what we can do is we can create an action call a service Google Sheets append to sheet select Google Sheets which is the default integration that we created previously if we don't specify the worksheet here it will be saved to the first or default one but we can also add new sheet let's tick this box and let's type here temperature this sheet can be from nahone used to store all the temperature information data allows us to append data to this specific sheet how does it work it needs to know to which column and we do not have any column created so far and then we also need to specify data that will be going into that specific column one column that is always created by default is created at or created by or something like that and this is the column with the timestamp when the data was entered the next one let's say will be the temperature value and here we want to insert the value from the temperature sensor let's copy this here and append it here but let's say that we also want to know from which sensor we receive this data or why this data was saved so we can have a hint on what the value actually is let's add one additional column and let's call it device and we know that this device is the freezer sensor let's press save but before we can run this automation we also have to create worksheet we now have list one we want to create one that would be called temperature while we are here we can also create one that would be called Energy let's go back to the home assistant press on Three Dots here and run we do not have any data in the list one which is the default worksheet that we didn't save any data to we do not have any data in the energy but we do have data in the temperature tab or temperature worksheet we have created and this is the date and the time when this data was inserted inside the system or when it was written to the Google Sheets then we have a temperature which is -18 and then we have the device name which we entered manually in the service call as you can see it's really easy to add the Google Sheets inside any of your existing automation the question is only for what cases can you use them on my main setup which I still haven't activated Google Sheets integration in I would use it to replace or to add additional functionality to one of my current automations and that is the midnight routine where the data for the energy use for that day is saved and I'm saving couple of data values and we will be talking about that later on but let's create one example here in my test setup let's go to settings automations let's create one automation that we want to trigger at a specific time of day at fixed time 23 59 59 meaning just one second before the system restarts the day we want to add an action that will be called service and we will be appending data to sheet once again we'll be using Google Sheets as integration and we will be saving data to the Energy tab the question is what data for example can you save let's pretend that home assistant doesn't have Energy tab or that you don't want to rely on the database that home assistant has instead you want to have a snapshot of kilowatt hours your home or apartment use up to that moment in time and that way you can know what was the difference between each of the days because data will be in a Google Sheets and also it would allow you to plot the graph with that data so let's add data to the table let's call this one total energy and here we want to insert data from one of our existing sensors since this is my test setup unfortunately I also do not have the off peak or on Peak tariff or utility meter setup in this system so I'm instead using this raw data total energy will be the state of the sensor Shelly em for the first phase because I only have one face in this apartment next we can also give name to the device that reported this value and this will be Shelley e m power monitoring and this should be it if we save this save Shelly em data in cloud and if we run this Automation in the Google Sheets and now we have a new record created once again date and time it is called total energy these are the kilowatt hours and this is the device that reported it but what if we want to expand on it and add further data there is one thing that you can do this is to add more data here such as for example these additional lines but I really wouldn't recommend it instead I would really recommend that you add action call service append to Google select sheet and add those additional lines here why would I recommend to create additional action well actually you do not have to but let's look at this example here here in the first action we are adding data to the worksheet called Energy in the column total energy the value of this sensor and then also the device name if we would add additional column and call it device total energy here and save that value here but also name the device device shelley1l which is the device that created this energy use this and this column would overlap yes that is the workaround or way around that you could for example just add number two create a new column that is now called device two but yeah the more columns you add the more times you would need to add this value or change this number so instead if we create two separate actions one will be saving total energy from this device and the other one would be saving device total energy for this device we will have one additional new line inside the Google Sheets for each of those two call actions let's save it press on three dots run and now we should have two new additional lines inside our Google Sheets one for this value of Shelly empm and the other one for this Shelley 1l yeah the data is currently unavailable from this sensor but if we would have data in the system this data or this number would be appended here this is future me just wanting to show you that yes this automation is working and when you fix the data source you do have data where it was previously unavailable okay but do you know how you can add Google Sheets to your home assistant actually this is something that you could do even previously before Google Sheets integration because this screen here is actually nothing related to the Google Sheets integration this here and also this screen here is abuse or use of panel iframes inside home assistant so let's see how you can do also that first let's check this integration here this is the official integration that allows you to insert any link or any web page inside your home assistant and it can be either internal web page or the external one be careful not all the pages can be embedded some pages have 4-bit to embed meaning that you cannot add them like this inside home assistant but Google Drive works the only thing that you need to do is you need to copy this URL and then inside your configuration email file just add following panel iframe if you do not have it already then name this I named this G sheet give it a title which is Google sheet for me give it an icon if you want and then for the URL inside the quotes just paste the URL you copied from your Google Drive of course you need to restart your home assistant and after that you will see your Google Sheets with the appropriate icon here the other option for you to add it to the UI is to click on three dots edit dashboard add card web and just paste here the URL you can name it aspect ratio can be whatever you want and click save and this is it of course you can also try and extend this to the whole screen Etc but if you are looking for a way to show this data or show this sheet inside the home assistant I would recommend to go for this option here but at the beginning of the video I told you about the alternative and this is it this is energy underscore log dot CSV which is a comma separated file where I save automatically for the last 780 days data from my home assistant I'm recording the date which is the date when the data was saved the energy used during the day the energy used during the night total used energy and three additional things are not recorded which are not none and unknown these are the minimum and maximum power use of that day also the power stats and the cost in the Croatian coolness so I have all of that data for my system for more than two years this is very similar to what you can do with Google Sheets Google Sheets has advantages you can visually better see the data you can sort it you can search it you can graph it you can calculate it this is just the data dump but maybe for some of you this is good enough so let's check what you need to do to create something like this in your system and by the way I will be keeping this while I will be also adding to that same existing integration that is saving this data also the ability to save all of the data inside the Google Sheets in my main setup first thing you have to do is you need to create new notification platform yes saving data to a file is actually notification platform and that's why it's very easy to edit also to your existing automations inside the notify platform you just add new platform which is called file give the name to this one which is the name of the notify integration that you can later on call from services for me this is energy log specify the file name just one note about the path as you can see it's slash config www.csv underscore logs slash and then the name of the file which is energy underscore log.csv which is actually this one here and it's inside www.csv logs folder we have to specify this config folder first but actually from within home assistant if we are using file editor or vs code we will not be seeing the name of this folder and the next one is timestep false if we specify timestamp true we will be recording the full system timestamp so this is actually the same thing as created field or created column in the Google Sheets you can play with that one but how do you use it inside your automation it's pretty simple almost the same way as you will be using the previous Google sheet integration for this I have one automation that runs each and every day at 11 59.50 PM 10 seconds before the end of the day I send data to the notification engine and this is sent to my mobile phone but then I have this service call notify dot energy log and if you remember the energy lock was the name we gave for that notification platform we have data and in the message field I just append all of the data that I want to send to that log file this is the sensor date which is the first field or the data field then we have sun energy day sun energy night daily energy total and we also have power stats minimum value maximum value power stats and then the daily total energy cost actually what I could do I could just copy this here and add it to the same message that will be later on Saved to the Google Sheets and then I would have data both in Google Sheets and the comma celebrated file again future me now from the same automation I have two service calls one is that previous notify energy log which is saving data to the CSV file and the new one is Google Sheets dot append underscore sheet which is saving the data to my worksheet integration and by the way all of my email files can be found also on the GitHub repository the link to my GitHub repository will be in the video description and if you open this issue here number 94 called Google sheet integration it will give you basic information about it but you will also have all the files that I changed in my automations to make this Google Sheets integration work and these are changes to my automation this Google panel and of course the dummy Secrets file where I'm storing all my dummy Secrets conclusion there is no conclusion what is better for you is what you will be using if you prefer to handle data in the Google Sheets in the cloud to be able to create graphs and charts go for that if you don't want to use Google Sheets or cloud services go for this notification platform and save all your data locally but I really would also like to hear what are some of your use cases for what you are using or for what you will be using either Google Sheets or saving data to the local file on the home assistant drive this data can help you in the future while I may not like math that much I do like statistics because statistics can help us in the future for example if I would have been tracking the temperature inside rooms for my apartment or gas usage or something like that I could predict more precisely what will be expected gas usage for this year or electricity usage for this year Etc but remember that saving data in a cloud can also be a good thing to prevent any possible data loss so don't forget to drop a line down below in the comment section and share with us some of your ideas or use cases for either Google Sheets or for notification service that saves data on the local file and while we are already here I really would like to thank everybody who has joined and become my YouTube channel member thank you all for all of your support and also thanks to each and every one of you that bought something from my merchandise store but let's not forget all of you who watched liked or subscribed to my channel thank you thank you thank you if you have any kind of a comment or a question you can always leave the comment down in the comment section below or head out to the Discord server and leave your comment there and I'll be seeing you next time until then bye bye and have fun

Channel: BeardedTinker

Views: 2,947

Keywords: google sheets automation, home assistant, home assistant sheets, home assistant csv, home assistant csv export, home assistant save to file, home assistant automation

Id: pPf1K4AJhus

Length: 22min 7sec (1327 seconds)

Published: Wed Oct 19 2022