WebCoRE logging to Google Sheets with multiple tabs


#1

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. 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.

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.

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 (!)
  }
}

WebCoRE reading from Google Sheets with multiple tabs
Logging variables to Google sheets
WebCoRE reading from Google Sheets with multiple tabs
Send Temperature to a google file
WebCoRE reading from Google Sheets with multiple tabs
#2

Thanks for the clear breakdown… I am sure this will benefit lots of people… :+1:


I know this is not your original intention, but I am curious if you have tried using another piston to pull data from that spreadsheet at a later time? That would open up many new doors!


#3

That’s my next project. I found one thread that mentioned one possible method but it seemed limited.


split this topic #4

8 posts were split to a new topic: WebCoRE reading from Google Sheets with multiple tabs


#5

I’ve run into a snag with something I’m trying to do. This app allows you to choose your sensors, and pull ONE value from it (temperature, humidity, etc). I have “multi-sensors” that have both temperature and humidity values that I want to pull. I’d like to monitor temperature AND humidity from multiple sensors in the same spreadsheet. To get Google Charts to graph temperature and humidity logically, I must have each data set in columns.

My column headers would look like this (I know they look like “rows” but try to envision them as “columns”):

Sensor1 Temp
Sensor2 Temp
Sensor 3 Temp
Sensor 4 Temp
Sensor 1 Humidity
Sensor 2 Humidity
Sensor 3 Humidity
Sensor 4 Humidity

In the current app, there can only be one value per sensor, but I really wish I could have 2.

Also, I love the idea to have multiple tabs in one spreadsheet.

Any ideas here?

Thank you!


#6

I have not double checked the syntax, but wouldn’t it be something like this very long line:

@URLscript “?XtabX=Fuel Stream" “&amp;Device=” string( [Sensor1] ) “&amp;Value=” [Sensor1:temperature]&amp;Device=” string( [Sensor2] ) “&amp;Value=” [Sensor2:temperature]&amp;Device=” string( [Sensor3] ) “&amp;Value=” [Sensor3:temperature]&amp;Device=” string( [Sensor4] ) “&amp;Value=” [Sensor4:temperature]&amp;Device=” string( [Sensor1] ) “&amp;Value=” [Sensor1:humidity]&amp;Device=” string( [Sensor2] ) “&amp;Value=” [Sensor2:humidity]&amp;Device=” string( [Sensor3] ) “&amp;Value=” [Sensor3:humidity]&amp;Device=” string( [Sensor4] ) “&amp;Value=” [Sensor4:humidity]


#7

@WCmore had the right idea. Basically, the parameter name is the column name and after the ‘=’ you put the value you want to write. So, it would look like this:

@URLscript "?XtabX=Fuel Stream&Sensor1 Temp=" [Sensor1:temperature] "&Sensor2 Temp=" [Sensor2:temperature] "&Sensor3 Temp=" [Sensor3:temperature] "&Sensor4 Temp=" [Sensor4:temperature] "&Sensor1 Humidity=" [Sensor1:humidity] "&Sensor2 Humidity=" [Sensor2:humidity] "&Sensor3 Humidity=" [Sensor3:humidity] "&Sensor4 Humidity=" [Sensor4:humidity]

My example was just that, an example. In my example I was writing both the device name and current value to separate columns in the spreadsheet. If you just want the value, you can do as shown above. You can write as much data to as many columns as you need to. I am sure there is some string limit somewhere but I believe it will accommodate what you want to do. The script will add the date time stamp for the time you write the data. ‘Fuel Stream’ should be replaces with whatever the name of your tab is. Or if you are writing to the default tab, you can leave that parameter out. You want to write all of your data at the same time to have them all on the same row of the spreadsheet. UPDATE: I tested with spaces in the column name and it does work.

EDIT: BTW, the default tab name you have in the script must exist or the script will fail. Any column names that are not already in your spreadsheet will be created automatically after the last existing column.