WebCoRE reading from Google Sheets with multiple tabs


#1

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:

GitHub

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:

  1. Create a global variable (@URLscript) that only contains the URL for the script:
  2. 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:

  1. A negative startRow cannot be combined with any value for endRow and vice versa.
  2. 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);
  }
}

Writing to and reading from Google Sheets
Logging variables to Google sheets
Temperature Records API using Google sheets
WebCoRE logging to Google Sheets with multiple tabs
Global variable as a dictionary
What makes the UI slow?
#2

This looks very intriguing. I wish that I had a use case to try this out.


#3

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

#4

I am seeing a lot of potential here, @guxdude. Keep it up!!

One thing that comes to mind is the spreadsheet is auto-populating, so after a week, there may be dozens (or hundreds) of dataPoints in a column.

To keep our code (and response) streamlined here in webCoRE, I think it would be very important to be able to grab only the last ‘X’ dataPoints in a column.


#5

Would be easy enough to add firstRow and lastRow parameters. Will play with it later tonight and post an update when I have it working.


#6

I love what you are doing here @guxdude, but I believe I owe you an apology. It was not my intention to take focus away from your original post. (which is awesome, BTW)

My mind just took your idea to the next level. It took me a few hours to realize that I probably should have started a new thread for this topic.


#7

It’s OK. I enjoy the challenge! I have some of it working but am trying to figure out if I can set a date range which seems like it would also be useful. Not sure if it should go to a different thread. It’s all about writing and reading google sheets.


#8

To be honest, I think we should keep the focus in the other thread to your original “Example Piston”, and keep this new project separate in the “Piston Design Help” category…


#9

@guxdude, new topic is all yours, edit away on that first post.


#10

Thanks, @ipaterson!!


#11

Really nicely done!


#12

@guxdude - Thanks for you work on this. I’m trying to read from a sheet with your code, but each time I get the following error:

TypeError: Cannot read property &#39;getRange&#39; of null (line 13, file &quot;Code&quot;)

What am I missing?


#13

What is the get URL you are using? Seems like the URL is not formatted correctly. Are you trying to read a specific row or column?


#14

@guxdude
I cannot not get my script correct. I have several questions.

I’m not sure what this means…


Do I add the word “Time” in cell A1 and freeze that row? Why would I do this?

I attempted to test…not sure what “test data” I need to delete from my spreadsheet.

When I tested, I got this…
image

My script.


#15

Yes, A1 should be the string “Time”.

The first row is the header row. A1 is the timestamp, and A_n_ is the label of the value being logged. When a new value comes in, the JS looks for an existing column for that label before adding one.


#16

When I click the “Debug” button I get this…

image


#17

(not sure why are clicking “Debug” - you don’t want to use that)

Your script is truncated. It’s about 50 lines.


#18

Yes, add the word ‘Time’. Then the script will automatically add the actual time of each log to that column. Additional columns will be added automatically to the right of this column for any column name that does not already exist.

Did you test by pasting the URL into browser window? Did you make sure you have the correct URL? Did you publish your function as a web app after creating it?


#19

yes, I think so, and yes.


#20

How do you know that? I only see 4.