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

  1. Create google sheet with multiple tabs. Name tabs as desired.
  2. Copy the browser URL and save. Will need ID for script
    ->https://docs.google.com/spreadsheets/d/VALUE HERE IS ID/edit#gid=0
  3. Enter script editor (tools->script editor)
  4. Copy and paste script
  5. Edit script to insert sheet ID from step 2 and change default spreadsheet name to one of the tab names in your spreadsheet.
  6. 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.
  7. Copy script URL and use to create global variable @URLscript
  8. 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
  9. 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 (!)
  }
}

Logging variables to Google sheets
Writing to and reading from Google Sheets
Write to file? Create history file?
WebCoRE reading from Google Sheets with multiple tabs
WebCoRE reading from Google Sheets with multiple tabs
Global variable as a dictionary
Global variable as a dictionary
What makes the UI slow?
Send Temperature to a google file
Writing to and reading from Google Sheets
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.


#8

Thanks. I’ve updated my script and got everything I need. I simplified the way the URL is created and made it easier on myself.


#9

Great! Yes, I find putting it together in pieces makes it clearer. Glad you got it working. Was hoping someone would try it so I knew it worked for someone other than me :wink:


#10

Any chance someone could provide an example Piston? (The more thorough the better).

I’ve been able to successfully create the other solutions mentioned by the OP, but am having trouble with the global veritable and how to structure/format correctly in WC.

Must thanks!


#11

@JJJSchmidt, Here is an example I put together a little while ago…

You asked about the global variables. They both have the format

string @URLReadScript = https://script.google.com/macros/s/MYPRIVATEKEY/exec

And then I use them as seen above in the pistons.


#12

I prefer to just use Google Forms to write logs. Takes minutes to build a form and link the responses to a Google sheet.

A simple web request to the form is then all you need.

Example, make a web request to:

https://docs.google.com/forms/d/e/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/formResponse?usp=pp_url&entry.922906038={yourData1}&entry.760865007={yourData2}&entry.1343069662={yourData3}&entry.1032338001={yourData4}&submit=Submit

To write to multiple tabs, just create a different Google Form and link it to the relevant tab.

When building a Google Form there is an option in the hamburger menu to ‘get pre-filled link’, use this to get the base format of the URL and add &submit=Submit on the end.


#13

Has anyone got this to work?

@Robin
I can get the URL to work and believe its just the “&submit=Submit” added to the end that’s not working.
because If I just past the URL without the “&submit=Submit” into a browser the form pops up with the correct answers entered. then I just have to click on the Submit to enter it.


#14

I figured it out!!!

I also needed to add formResponse? in place of the viewform? of the pre-filled link so it matches the above Example.


#15

sorry, that’s correct, my bad.


#16

Odd behavior with writing to my test Google Sheet.

No output or log created after clicking “Test” with Full logging on.

I am getting partial output to my test google sheet. But I am only getting 9 rows, and the first should be “0”.


#17

It could be writing too fast in a loop like this. Try adding a 3 or 5 second wait between writes. Also, if you make the first column “Date”, then the date and time of each log will automatically be added.


#19

@guxdude, can I read and write to the same Google Sheet?


#20

Any way to force overwrite of the existing rows?


#21

Yes! I think I explained in the instructions somewhere. You have to make a separate project in google script because the name of the scripts are the same. As long as they both point to the same spreadsheet, you can add and read. I just have a @URLwriteScript and @URLreadScript variable for accessing from any piston.

Unfortunately, I did not figure out how to overwrite entire rows but I did create a script to replace individual values. You have to specify row, column, and value [in addition to tab if not default] and you can only do one value per call. [I tried multiple but for some reason, the parameters did not go to google script in the same order I listed them-bug?] I have a @URLreplaceScript for accessing this one.

Here is the script:

// version 2.0 - Guxdude
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 rowNumber = -1;
    //var firstDate=-1;
    //var lastDate=-1;
    //var firstRow=0;
    //var lastRow=0;
    var range;
    var newValue;
    
    var selectRow=1;
    var selectCol=1;
    var nRows=1;
    var nCols=1;
    
    //var tt=request.queryString;
    
    Logger.log(request.parameters)
    
    // 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
          Logger.log("tab name is %s",paramValue);
          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();
          break;
        case "colNum":
          columnNumber=Number(paramValue);
          Logger.log("row is %d", rowNumber);
          break;
        case "rowNum":
          rowNumber=Number(paramValue);
          Logger.log("row is %d", rowNumber);
          break;
        case "Value":
          newValue=paramValue;
          Logger.log("value is %s", newValue);
          break;
        default:
          // Unrecognized parameter: ignore
      }
    }
       
    if (columnNumber<0) {
    // Parameters do not seem to be in order so have to evaluate all before setting
      Logger.log("column name is %s", columnName);
      colFound=false;
      for(var x=0;x<sheetHeaders[0].length;x++) {
        Logger.log(sheetHeaders[0][x].toString().toLowerCase())
        if(columnName==sheetHeaders[0][x].toString().toLowerCase()) {
          columnNumber=x+1;
          colFound=true;
          break;
        }
      }
      if (colFound==false) {columnNumber=-1; Logger.log("column not found"); }
    }

    if (rowNumber>0 && columnNumber>0) {
      // set value
      sheet.getRange(rowNumber, columnNumber).setValue(newValue);
      Logger.log("Setting value %s", newValue);
      //range = sheet.getRange(selectRow,selectCol,nRows,nCols);
    }
    
    // get data values from specified range
    // var data = range.getValues();
    //return data in JSON format
    //return sheet.getRange(rowNumber, columnNumber).getValue().toString();
    return ContentService.createTextOutput(JSON.stringify(sheet.getRange(rowNumber, columnNumber).getValue())).setMimeType(ContentService.MimeType.JSON);
  }
}