WebCoRE logging to Google Sheets with multiple tabs


#6

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

@URLscript “?XtabX=Fuel Stream" “&Device=” string( [Sensor1] ) “&Value=” [Sensor1:temperature]&Device=” string( [Sensor2] ) “&Value=” [Sensor2:temperature]&Device=” string( [Sensor3] ) “&Value=” [Sensor3:temperature]&Device=” string( [Sensor4] ) “&Value=” [Sensor4:temperature]&Device=” string( [Sensor1] ) “&Value=” [Sensor1:humidity]&Device=” string( [Sensor2] ) “&Value=” [Sensor2:humidity]&Device=” string( [Sensor3] ) “&Value=” [Sensor3:humidity]&Device=” string( [Sensor4] ) “&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?

@RobinWinbourne
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);
  }
}

#23

It may be my programming, but it appears that the new write script is writing blanks to the sheet. I’ll continue to debug. Please check my syntax.

image


#24

Sorry, for the overwrite script, the parameters are: XtabX, Value, colName, colNum, and rowNum. You can specify either the column name using colNum or column number using colNum but not both. If colNum is supplied, ColName will be ignored. So I think the parameters you want are:

“&XtabX=Sheet1&colNum=1&rowNum=” Row_Number “&Value=” Foods[$index]

or

“&XtabX=Sheet1&colName=Fruit&rowNum=” Row_Number “&Value=” Foods[$index]

Either should work.


#25

@Pantheon, NOTE: Right now I am having trouble reading anything from google sheets using my script. Seems to be a redirect issue with the latest webcore update. Writing still seems to work fine.


#26

That explains a few things. I appreciate all of your help!


#27

@guxdude, this project is awesome. I am going to be able to use your scripts to do several things. However, at this time, my READ is working, but my WRITE is not. It is probably related to the redirect issue you mentioned.