After creating an improved data logging script for google sheets (see this thread), @WCmore asked about reading data back from the google sheet. In response, I have created a versatile script for extracting data from a google sheet workbook. Use the instructions at the GitHub link below for creating your google script. Note that if you have the data logging script installed, you must install this script as a separate project and use that URL for your GET request. To create a new project when editing a script, simply use the menu File->New->Project. You can see all you projects by going to the dashboard using the big blue/white arrow in the upper left corner of the editing window:
Dashboard:
loverso-smartthings/googleDocsLogging
SmartThings Google Sheets Logging. Contribute to loverso-smartthings/googleDocsLogging development by creating an account on GitHub.
The google script below allows access to any tabs within the same workbook in case there are different types of logs you wish to read. Best place to start is at the GitHub link and follow the instructions through step 16 for creating the spreadsheet and script. Only change is to use the script below instead of the one on GitHub.
Once your spreadsheet and script are created and published, use the script link as follows:
- Create a global variable (@URLscript) that only contains the URL for the script:
- call the script directly from any piston using a web GET request and Expression like this example:
@URLscript “?XtabX=Fuel Stream" “&Device=” string( [Any Device] ) “&Value=” [Any Device:level]
3 replace ‘Fuel Stream’ with any other tab name and add the appropriate parameters for that tab.
As many parameters as needed can follow the ‘?’ which each parameter/value pair separated by ‘&’.
Valid parameters:
XtabX tab name Example. XtabX=Log NOTE: this parameter should always be first
colName column name to read. Example: colName=level [name not case sensitive]
startRow first row to start reading. Example: startRow=15
NOTE: negative value of startRow will read the first abs(startRow) rows of the spreadsheet
endRow last row to read. Example: endRow=45
NOTE: negative value of endRow will read the last abs(endRow) rows of the spreadsheet
startDate starting date for rows to read. uses date in column 1. Example: startDate=04/15/20
endDate ending date for rows to read. uses date in column 1. Example: endDate=12/31/20
Additional notes:
- A negative startRow cannot be combined with any value for endRow and vice versa.
- To get specific about start/end date/time use this notation: 2020-04-25T07:00:00.000Z
Example: startDate=2020-04-15T07:00:00.000Z&endDate=2020-04-15T21:00:00.000Z
See following posts for additional background on the development of this script.
Let me know if you have any questions. Enjoy!
EDIT: Slight update to include some error handling for row numbers out of range.
GoogleScript:
// version 2.0 - Guxdude
// 1.1: added capability to request full sheet vs single column
// 2.0: added request parameters to specify start and/or end date or start and/or end row
// cannot specify rows and dates together, one or the other.
// for rows, negative number takes that many rows from the beginning/end
// 2.1: added error checking for rows out of range
function doGet(request) {
if(request != null) {
var spreadSheet = SpreadsheetApp.openById("YOUR ID HERE");
// default tab name
var sheet = spreadSheet.getSheetByName("Testing");
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var sheetHeaders=firstRowRange.getValues();
// No default column name. If none provided, entire sheet will be returned
var columnName = "";
var colFound=false;
var columnNumber = -1;
var firstDate=-1;
var lastDate=-1;
var firstRow=0;
var lastRow=0;
var range;
var selectRow=1;
var selectCol=1;
var nRows=1;
var nCols=1;
//var tt=request.queryString;
// Evaluate all the query parameters
for (var i in request.parameters) {
var paramName = i.toString();
var paramValue = request.parameter[i];
switch(paramName) {
case "XtabX":
// select specified tab by name and get new first row data
sheet = spreadSheet.getSheetByName(paramValue);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
break;
case "colName":
// If colName parameter is found, search for that column
columnName = paramValue.toString().toLowerCase();
colFound=false;
for(var x=0;x<sheetHeaders[0].length;x++) {
if(columnName==sheetHeaders[0][x].toString().toLowerCase()) {
columnNumber=x+1;
colFound=true;
break;
}
}
if (colFound==false) columnNumber=-1;
break;
case "startDate":
// store date
firstDate=new Date(paramValue);
break;
case "endDate":
// store date
lastDate=new Date(paramValue);
break;
case "startRow":
// code block
firstRow=Number(paramValue);
break;
case "endRow":
// code block
lastRow=Number(paramValue);
break;
default:
// Unrecognized parameter: ignore
}
}
// If firstDate is specified, find row with first date on or after firstDate
if (firstDate>0) {
var dateRange=sheet.getRange(2, 1, sheet.getLastRow()-1, 1);
var dateData=dateRange.getValues();
//var limit=sheet.getLastRow()-1;
for(var dd=0; dd<sheet.getLastRow()-1; dd++) {
var compareDate= new Date(dateData[dd][0]);
if (firstDate.getTime()<=compareDate.getTime()) {
// dateData does not include headers so need to add 1 and rows are indexed from 1 so +1 again
firstRow=dd+2;
break;
}
}
}
// If lastDate is specified, find last row with date on or before lastDate
if (lastDate>0) {
var dateRange=sheet.getRange(2, 1, sheet.getLastRow()-1, 1);
var dateData=dateRange.getValues();
for(var dd=0; dd<sheet.getLastRow()-1; dd++) {
var compareDate= new Date(dateData[dd][0]);
if (lastDate.getTime()<compareDate.getTime()) {
// dateData does not include headers so need to add 1 and rows are indexed from 1 so +1 again
lastRow=dd+1;
break;
}
}
}
// If column was not specified, get all data; otherwise, use only the specified column
if (columnNumber<0) {
// if no column provided, return all data including headers
// range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
selectRow=1;
selectCol=1;
nRows=sheet.getLastRow();
nCols=sheet.getLastColumn();
} else {
// return requested column without the header
//range = sheet.getRange(2,columnNumber,sheet.getLastRow()-1,1);
selectRow=2;
selectCol=columnNumber;
nRows=sheet.getLastRow()-1;
nCols=1;
}
// NOTE: firstRow/lastRow = 0 means nothing specified so use default above.
if (firstRow<0) {
// if firstRow specified is <0 just get the first abs(firstRow) rows of data (not including the header)
selectRow=2;
nRows=-firstRow;
} else if ( firstRow>1 ) {
// if firstRow is specified, start there
selectRow=firstRow;
nRows=sheet.getLastRow()-selectRow+1;
}
if (selectRow>sheet.getLastRow()) {
selectRow=sheet.getLastRow();
}
if (lastRow<0) {
// if lastRow specified is <0 get the last abs(lastRow) rows of data
nRows=-lastRow;
if (nRows>sheet.getLastRow()-1) {
nRows=sheet.getLastRow()-1;
}
selectRow=sheet.getLastRow()-nRows+1;
} else if ( lastRow>0 ) {
if (lastRow>sheet.getLastRow()) {
lastRow=sheet.getLastRow();
}
// if lastRow is specified, end there
nRows=lastRow-selectRow+1;
}
if (selectRow+nRows-1>sheet.getLastRow()) {
nRows=sheet.getLastRow()-selectRow+1;
}
// get requested data
range = sheet.getRange(selectRow,selectCol,nRows,nCols);
// get data values from specified range
var data = range.getValues();
//return data in JSON format
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}
}
