Google sheet webcore?

[beau76]

I would like to put the message on a google sheet
how can I do the so each time it runs the entry is on its own line

[Terminal]

Here is an example of a piston I use to send hourly energy usage to a Thingsspeak account.
The URL would of coarse need to be updated but I think it works on the same principle.

[kevin]

Logging variables to Google sheets

I do something similar using the Google Sheets smart app Once you have this sheets script set up you can log directly from webCoRE (I’m using http GET) to sheets bypassing the smart app “https://script.google.com/macros/s/KEY/exec?{$currentEventDevice} {$currentEventAttribute}={$currentEventValue}” The associated google script takes the data before = makes that column heading, then appends new row with time stamp and data in appropriate column.

[beau76]

Hi @kevin
I have three questions
1, I have two devices can i do one sheet for each device ?
2 if I am using webcore do i need the smartapp part installed?
3. can you post a example of one of you webcore piston?
thank Beau

[kevin]

1-it will log timestamp in first column, each device parameter following columns. If you are familiar with excel or sheets, you can set up formulas to copy whichever data you want to other sheets
2-no you don’t need the SmartApp, follow the setup for google sheets and there it shows a test url. That url is the key, once you have that, you use it in webCoRE.
3-on my phone, I’ll try to find good example when I get back to a pc.

[beau76]

HI Kevin
in web core do you use a get or post can you show me how you format it?

[kevin]

GET. I stripped bunch of junk out of my TinyCamPro status log to Google Sheet, so I haven’t tested but I think this should work…

[beau76]

Hi Kevin
here my code an output in the log if you look at the stop it not filling in the time any idea

time	Start	Stop	Room	Pump
1/27/2020 14:47:58	Mon, Jan 27 2020 @ 2:47:46 PM CST	1/27/2020 0:00:00	Beau’s R
1/27/2020 17:12:31	Mon, Jan 27 2020 @ 5:12:24 PM CST	1/27/2020 0:00:00	Beau’s

here my full code

also get this error Error executing virtual command [].setVariable: (1ms) groovy.lang.MissingPropertyException: No such property: result for class: script_app_6c4a97a42cd9c01ed79ae72c875a75bf6c50e82c665272109ecaf6627b7135

[kevin]

Try selecting that column in sheets and format date and time differently? Try using $time24 instead $now. I don’t remember the difference between them.

[beau76]

Hi Kevin
$time24 is hour:min need at least hour:min:sec tried localnow but time was way off
stop is not filing could this be a problem in the script sheet
or is there a limit to a get

[kevin]

Were you able to try re-formatting the column in Sheets? Another test, try to format the date time more simply in webcore, before it gets sent to sheet. formatDateTime({sumpTriggerEnd}, “M/dd/yyyy HH:mm:ss”

[beau76]

Hi Kevin
I have been testing today the date is not a big deal I just need to times
I keep getting this error Error executing virtual command [].setVariable: (1ms) groovy.lang.MissingPropertyException: No such property: result for class: script_app_6c4a97a42cd9c01ed79ae72c875a75bf6c50e82c665272109ecaf6627b7135

update still getting error some times got times in sheet
have noticed one thing here my data
oom={room}&Pump={pump}&START={formatDateTime(sumpTriggerTime,‘h:mm:ss:SS’)}&STOP={formatDateTime(sumpTriggerEnd,‘h:mm:ss:SS’)}"

start and stop go in b1 and c1 room and pump is d1 and e1 i think it wold be the other way

[kevin]

If you delete the columns, they might come back in the order you mentioned. Also, you can move columns in Sheets and the script will put new data in correct column.

For the error, try changing type of variables, like message. you initialize it to 0, which might make it integer, but later it is set to a string. ??? Try using the trace and log features to help identify where the problem is.

[guxdude]

I have two devices can i do one sheet for each device ?

I have updated the script so it can edit any tab in your google sheet. Just add the parameter XtabX=SHEET_NAME. They all need to be in the same sheet

// version 2.0-Guxdude
function doGet(request) {
  if(request != null) {

    var ss = SpreadsheetApp.openById("Insert your sheet ID");
    // default spreadsheet
    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 (!)
  }
}

[beau76]

@guxdude Where do I add the parameter XtabX=SHEET_NAME ?

[guxdude]

Where do I add the parameter

It will be one of your parameters in your http GET request. For example:

https://script.google.com/macros/s/YOUR_SCRIPT_CODE_HERE/exec?XtabX=Fuel Stream&Device=LV Can Lights (Q1)&Value=off

In my example, ‘Fuel Stream’ is the tab I want to edit and I am adding values to the ‘Device’ and ‘Value’ columns. The timestamp will be automatically entered in the first column.

[Studio1930]

Have been looking for some code to do this with my Google sheets. Thank you much!