Some excellent work has already been done in this area with logging available in SmartThings or directly from WebCoRE. A lot of people seemed to be involved but these were my main sources:
My extension modifies the google script to allow access to multiple tabs within the same spreadsheet in case there are different types of logs you wish to create. The GitHub link instructions have lots of good pictures for creating the spreadsheet and script. Only change is to use the script below instead of the one on GitHub. Here are simplified instructions:
Getting started:
- Create google sheet with multiple tabs. Name tabs as desired.
- Copy the browser URL and save. Will need ID for script
->https://docs.google.com/spreadsheets/d/VALUE HERE IS ID/edit#gid=0 - Enter script editor (tools->script editor)
- Copy and paste script
- Edit script to insert sheet ID from step 2 and change default spreadsheet name to one of the tab names in your spreadsheet.
- Publish script as web app (Publish->Deploy as web app)
->There will be some security options you need to get through the first time
->The script will provide access to the unpublished spreadsheet if someone obtained the script link but they would also have to know the spreadsheet name. Suggest for added security any sensitive data NOT be stored on the default spreadsheet. - Copy script URL and use to create global variable @URLscript
- Log directly from any piston or Create piston for logging
->@URLscript “?XtabX=Fuel Stream" “&Device Name=” string( [Any Device] ) “&Current Value=” [Any Device:level] “&Another column name=” anotherValue - If using single piston for logging, Copy piston URL to log from other pistons
Replace ‘Fuel Stream’ with any other tab name and add the appropriate parameters for that tab.
Note 1: You can insert Column 1 name as a parameter as well but as long as you create that first column, you can leave it blank and a timestamp will be automatically filled in by the script.
Note 2: If you omit the XtabX parameter, the script will use the default tab name. Be sure to edit that in the script to a valid tab in your spreadsheet.
Note 3: The parameter names above are just examples. The parameter names other than XtabX correspond to the headings in the spreadsheet (not case sensitive). If a heading does not exist, a new column will be added. The value for the parameter is the value that will be placed in the corresponding column of the spreadsheet. All values included in the get URL will be added in a new row after all existing rows so, to write a row of data, you must include all the columns.
Let me know if you have any questions. Enjoy!
// version 2.0-Guxdude
function doGet(request) {
if(request != null) {
var ss = SpreadsheetApp.openById("INSERT SPREADSHEET ID");
// default tab name
var sheet = ss.getSheetByName("Logs");
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var 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;
// Look for tab parameter
if(tt.search("XtabX")>=0) {
// If tab parameter is found, update all variables to this spreadsheet
sheet = ss.getSheetByName(request.parameter["XtabX"]);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
newColumnArrayCount=sheet.getLastColumn();
}
// 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()); // New from SEBASTIAN CORONA FERNANDEZ 11/8/16 in Sheet chat (!)
}
}
