So, I updated the script so now if no column name is specified, the entire tab is returned. See updated script, test piston and logs below.
I plan to use this to record important log data from both my houses in one place and then I can ask Alexa to read the logs back when I am home using a sim switch and Echo speaks. Another implementation of this thread but will support messages from multiple locations.
// version 1.1 - Guxdude
// 1.1: added capability to request full sheet vs single column
function doGet(request) {
if(request != null) {
var spreadSheet = SpreadsheetApp.openById("INSERT SHEET ID HERE");
// default tab name
var sheet = spreadSheet.getSheetByName("Logs");
// No default column name. If none provided, entire sheet will be returned
var columnName = "";
var columnNumber = -1;
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 = spreadSheet.getSheetByName(request.parameter["XtabX"]);
}
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var sheetHeaders=firstRowRange.getValues();
// if no column provided, return all data including headers
var range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
// Look for Column name
if(tt.search("colName")>=0) {
// If tab parameter is found, update all variables to this spreadsheet
columnName = request.parameter["colName"];
for(var x=0;x<sheetHeaders[0].length;x++) {
if(columnName.toString().toLowerCase()==sheetHeaders[0][x].toString().toLowerCase()) {
columnNumber=x+1;
break;
}
}
// return requested column without the header
range = sheet.getRange(2,columnNumber,sheet.getLastRow()-1,1);
}
var data = range.getValues();
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}
}
4/14/2020, 8:58:45 AM +957ms
+3748ms ║[8]: [[2020-04-13T10:32:05.000Z], [2020-05-01T07:00:00.000Z], [2020-05-02T07:00:00.000Z], [2020-05-03T07:00:00.000Z], [2020-05-04T07:00:00.000Z], [2020-05-05T07:00:00.000Z], [2020-05-06T07:00:00.000Z], [2020-05-07T07:00:00.000Z]]
+5956ms ║[8]: [[Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas]]
+8493ms ║[8]: [[All is good], [Message 1], [Message 2], [Message 3], [Message 4], [Message 5], [Message 6], [Message 7]]
+10144ms ║[8]: [[Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas], [Las Vegas]]
+12016ms ║[7]: [[LV Can Lights (Q1)], [More device 1], [More device 2], [More device 3], [More device 4], [More device 5], [More device 6]]
+13797ms ║[7]: [[10], [90], [50], [70], [0], [100], [25]]
+15426ms ║[[Date, Device, Value], [2020-04-13T22:32:12.128Z, LV Can Lights (Q1), 10], [2020-04-14T07:00:00.000Z, More device 1, 90], [2020-04-15T07:00:00.000Z, More device 2, 50], [2020-04-16T07:00:00.000Z, More device 3, 70], [2020-04-17T07:00:00.000Z, More device 4, 0], [2020-04-18T07:00:00.000Z, More device 5, 100], [2020-04-19T07:00:00.000Z, More device 6, 25]]
+15466ms ║this sheet has 3 Columns and 8 rows. value at C5 is 70
