Is anyone currently successfully sending data directly from Hubitat to Google Sheets without going through HA, influx/grafana or some other program, and if so, can you please share how you are doing it?
I tried to follow the directions at the various links on the below post, but I think Google may have changed something, and I don't have enough knowledge to get this to work without some additional directions.
I use Google Sheets for logging, ultimately, for charting things how I want to. I then publish the charts in the sheet and I can pull them into my dashboards.
I use scripts in the sheet to receive the data, and I send the data via a web request. I used to do it with Webcore, now I use a custom virtual device to send the data.
This is the main script I use in the sheet workbook:
// version 2.1-cburgess 6/1/22
function doGet(request) {
if(request != null) {
var ss = SpreadsheetApp.openById("");
sheet = ss.getSheetByName(request.parameter["XtabX"]);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
// columns start at one, but arrays start at 0, so this is the right number for inserting into an array that will be placed into the columns
var newColumnArrayCount=sheet.getLastColumn();
// initialize new row to be inserted before it gets filled with data
var newRow = new Array(sheetHeaders[0].length);
// Get query string to check for tab parameter
var tt=request.queryString;
// initialize new row to be inserted before it gets filled with data
var newRow = new Array(sheetHeaders[0].length);
for(var x=0;x<newColumnArrayCount;x++) newRow[x]="";
// get all the parameter values and insert into new row
for (var i in request.parameters) {
var foundRow=false;
// if the parameter is the tab name, continue to the next parameter
if(i.toString()=="XtabX") {
continue;
}
for(var x=0;x<sheetHeaders[0].length;x++) {
if(i.toString().toLowerCase()==sheetHeaders[0][x].toString().toLowerCase()) {
var currentpar = request.parameter[i];
isNaN(currentpar) ? newRow[x] = currentpar : newRow[x] = Number(currentpar);
foundRow=true;
break;
}
}
if(foundRow==false) {
if(sheet.getLastColumn()==sheet.getMaxColumns()) {
sheet.insertColumnAfter(sheet.getLastColumn());
}
sheetHeaders[0][newColumnArrayCount]=i;
newRow[newColumnArrayCount]=request.parameter[i];
firstRowRange=sheet.getRange(1, 1, 1, sheet.getLastColumn()+1);
firstRowRange.setValues(sheetHeaders);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
newColumnArrayCount++;
}
}
if(newRow[0]=="") {
Logger.log("setting date");
newRow[0]=new Date();
}
// Appends a new row to the bottom of the
// spreadsheet containing the values in the array
sheet.appendRow(newRow);
sheet.getRange(sheet.getLastRow()-1, 1, 1, sheet.getLastColumn()).copyFormatToRange(sheet, 1, sheet.getLastColumn(), sheet.getLastRow(), sheet.getLastRow());
}
}
This is what I use to delete old rows within a set number of days.
function doGet(request) {
var ss = SpreadsheetApp.openById("");
var xtab = request.parameter["sheet"];
var tab = xtab.replace("%20"," ");
console.log(tab);
sheet = ss.getSheetByName(tab);
var values = sheet.getDataRange().getValues();
var pastDate = new Date();
var deleteDays = request.parameter["days"];
pastDate.setDate(pastDate.getDate()-deleteDays);
var numRows = 0;
for( var row = values.length -1; row >= 1; --row ) {
var cellDate = values[row][0];
if (cellDate < pastDate) {
sheet.deleteRow(parseInt(row)+1);
numRows++;
}
}
};
I use this virtual device to send my logs. It has attributes to enter the URIs of the app scripts in the sheet.
It uses this format:
Set up the logParams with the data by making a string.
logParams = "Variable Name 1="variableName1+"Variable Name 2="+variableName2 ...
Then I set the sendLog attribute using the logParams string:
googleLogs.sendLog("Sheet Name", logParams)
Inside the code for the log device, there is an array built in the code that has to be manually modified for deletes, you just add entries to it in the format:
"sheet=Sheet Name&days=7" -> to keep 7 days worth of data on the sheet. It will randomly delete the rows down to the proper number of retention days for sheets added to the array.
I left my sheets in the code as an example of setting up the delete array in the code.
Edit: Note, this is a bit of a manual and hacky way to do it, and though I'm posting code this is not a release or anything, but if you want to try and use what I posted I'm happy to help you get it working.
Also note, I use just one sheet workbook for all my logs, so there are many different log sheets within the one sheet file that has the apps code installed.
Also, you have to fill in your app sheet ID in those scripts first on this line:
var ss = SpreadsheetApp.openById("");
The sheet ID is part of the sheet URL... you can google how to get that from the URL.
I'd agree that using Google Apps Scripts to establish a cloud endpoint for parsing hub data makes more sense these days compared to sending it via the Forms API.
I appreciate the response. Using Google Apps scripts is WAY above my skill level. I'll study this to determine if there is any way I can implement this. Hoping one of the smart people here can write an app that does this automatically...
Two words: Webhooks and IFTTT. Pretty sure you'll find the service offers a direct way to pass data into Google Sheets without any coding on your part, beyond setting up an Applet.
Well, I don't know what webhooks are, and I don't trust and would not subscribe to IFTTT. Back when it was free I had it doing something automatically, and found out it was failing more than suceeding . Don't trust it - too much that can go wrong.
There really is not much to it. On a sheet, you click Extensions --> App Script, and then there is a + button to add a script, and then you copy the code in. You click through the screens to install it, and at the end it gives you a URI to access the script, which you save to use in the web request to send the data. The driver I wrote uses that URI to send the data that is in the string you build that you just save in the attribute with the set command.
It might be worth trying to play around with it. You may also want to look at some of the other charting apps available like Watch Tower. I think I have tried most of them but I still prefer the apps scripts for how the control where I can send raw data or calculated data from my apps.
Thank you. I will try. Always willing to learn something new.
I've tried Watch Tower, and am using it for some things. My desire to use Google Sheets is to maintain long term data. For example, I want to log and maintain (and perhaps chart) the average and maximum relative humidity at the ridge in my encapsulated attic over many years. I don't think there is a good solution in HE for that, but HE generates all the data, so if I can get it Google Sheets I think that may be a good solution.
Yes, that is a good use for it. This is one case where I really do not want the data to be local to the hub. I can access from anywhere, and easily edit and chart the data without needing access to my local network or Hubitat. I don't need my hub chunking away on data constantly just so it is there on the occasions I want to look at it.
I usually only keep a week or so of data for my charts, but I have made some sheets that track data over long periods. I needed to know how much the max illuminance changes from my outdoor illuminance sensor through the year, as seasons change. I saved the max illuminance from each day for six months, and then I put a trend line on the chart. Then I took two points, found the slope of the line, and found how much per day illuminance changes between solstices. That value was then incorporated into my calculations to determine if it is sunny, partly sunny, or cloudy, based only on the values from an illuminance sensor and the variance of the data points over the last five minutes.
I got this far. Specifically
I reluctantly deleted all the cookies, but it is still doing the same thing.
I'm using Chrome on a desktop
That is strange. It is still working for me in Chrome, I even started a new sheet, renamed it, and it lets me set the name and add code.
Maybe try a different browser? I mean, it is a Google app so I would think Chrome would be the best thing to use. Maybe try a computer reboot, or another computer?
Exact same issue using a different computer...
But past the above using MS Edge....
So this is what I did:
Open the Google Sheet (previously created & named)
Click Extensions, selected Apps Script
At this point there are plus signs next to Files, Libraries and Services; Script is not an option. It opens in Code.gs. So instead of selecting a plus sign, I left it there.
Copied the code from your post above and pasted it, including overwriting what had auto populated on its own.
Not sure how to "click through the screens to install it." I clicked on save, then the option for "run" appeared so I clicked on that.
Then I get a pop up "Authorization required" and I clicked on "Review permissions". It then asked me to "Chose an account to continue to Untitled Project; I chose my gmail account. That takes me to a warning "Google hasn't verified this app" and my options are Advanced or Back to Safety. I selected Back to Safety. The result is a support.google.com screen about allowing access to a third party app...
At this point I stopped the Google Sheets side of things as I have no idea what I'm doing...and turned to trying to create a virtual device in Hubitat...
If I click your link above to Virtual Device, I get a page of data, but I don't know where to put that data. If I go to Devices, Add Device, ,Virtual, HE wants me to select the Virtual Device type. I selected Virtual Humidity Sensor, but still don't see where I paste the data from your link above....
I forgot an important step, Hubitat cannot use a Google account so you have to make the sheet available to everyone.
Yes, anyone, if they happen to find your sheet, could edit it. I have been using my sheet for about five years and I have never had anyone access it. I also do not consider what I am saving to be personal data that reveals anything.
For the driver code, you go settings and turn on show developer options at the top. Then you have a new area on the left sidebar that you can expand. Then you will see "apps code" and "driver code". Go into Driver code, click "new driver", then paste the code in and click the save button.
Then create the device as a new virtual device, and choose that driver.
@ritchierich, would you consider creating a "secured" app to log info to a Google sheet with the foundations of @user2574's idea above? With all of your experience with the Google API, I think you could pull it off pretty easily. 
I actually do not use the Google app, since I am Alexa based.
I wonder if you use that app, and you have already authenticated the hub with Google for that, if it will let my driver send the web request to send the data using your account. I have no idea if that works, but it looks like it may authenticate any requests from the hub, once that is set-up.
"To authenticate a Google account with Hubitat for Google Home integration,start by opening the Google Home app and tapping the "+" icon. Select "Set up device" then "Works with Google" and search for "Hubitat".Follow the on-screen prompts, which will involve signing in with your Hubitat administrator email and password, choosing your Hubitat hub, and selecting the devices you want to expose to Google Home"
Maybe expose the log to google virtual device to Google, since it is sending the request? I'd be interested to know if that works.
Edit: The more I think about it, the Google Home authorization is for the Hubitat cloud. When you send a web request from the hub, it is coming from your own IP, so the web request would have to be sent through google cloud for that to have any effect. Adding the driver as a device will not help either, since that is just looking to send device commands through Hubitat cloud back to your IP, specifically for device control.
The hub itself would need to authenticate with Google.
I think you have to say advanced and verify the app. You are verifying the apps script you added, so you have to say yes, I trust this script I just put in. I first set this up long ago when I was on SmartThings so I probably forgot that step. That also may be why I had to open the sheet to everybody, since SmartThings had no way to log into Google from Webcore which I was using at the time to do the web request.
Edit: It looks like this could have been the post I started from. I think I have modified the scripts a bit.
I also wrote a script later to pull from a Google sheet, I forgot about that but it is mentioned in the above post. I stopped using it, but I had it working for a bit to send data to a sheet, have the sheet calculate new values, and then I would get the new values back from the cells in the sheet. It was a roundabout way to do some more complex calculations when I was using Webcore.
I just found the piston I used to use. logParms is an argument, and I would send data to it from other pistons for logging.
Yes, the script will be a .gs file in the Apps Script page. You can leave the code.gs name, which is what I did. When I added the delete script, I named it delete.gs
Download the Hubitat app
