› ›
The Twine forums are now archived. If you need help, please visit http://help.supermechanical.com

Google APIs using JSON?

Joel FinkleJoel Finkle
edited February 2013 in Tips and Troubleshooting Vote Up1Vote Down
Has anyone tried using Google JSON APIs for logging data from the TWINE to a spreadsheet? I haven't read the docs yet, but at first glance it might be possible using an HTTP URL.

Impost more here as I learn more.

Best Answer

Answers

  • Joel FinkleJoel Finkle
    Vote Up2Vote Down
    Got it -- see this URL:
    http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/

    1) Create a Google Docs spreadsheet with the script on that page, and first-row values of "Timestamp" and whatever else you want to log, e.g. "orientation" and "temperature".

    2) Run the Setup script twice. The second time will create a URL that you'll use for data logging, e,g,
    https://script.google.com/macros/s/MYSPREADSHEETKEYGOGETYOUROWN/exec

    3) Create a rule that does an http post with the URL that was created, and add any parameters on the end with ?orientation=up&temperature=toohot

    4) It works... just one problem: the rule entry for an HTTP request is too short for me to add any parameters to the end. After exec, I can add ?s= but not add any values before it hits the hard length

    CAN WE PLEASE HAVE A LONGER URL IN AN HTTP REQUEST?
  • Clark MitchellClark Mitchell
    Vote Up1Vote Down
    This is a great thread! I was able to get the google docs part of this working without too much trouble, but then I too ran into the url length issue. I think I have solved that issue using http://para.ms/ which lets me pass a parameter along with the shortened URL. The only problem is that I can't figure out how to pass both temp and orientation. I'm not looking for help with the URL shortener. I just wanted to +1 on support for longer URLs. This would REALLY help me out.
  • Jay EricksonJay Erickson
    Vote Up1Vote Down
    another approach: ended up writing a shell script that logs in, grabs current temp, writes to a log and posts to a google sheet (need to grab form key and form element name from source of form submit page on google). i set up in cron to run every 10 mins

    see below:

    wget -o log.txt --quiet -O temp.txt --keep-session-cookies --save-cookies cookies.txt --no-check-certificate --post-data="email=REPLACE_WITH_YOUR_LOGIN&password=REPLACE_WITH_YOUR_PASSWORD" https://twine.supermechanical.com/login

    wget -o log.txt --quiet -O temp.txt --load-cookies cookies.txt --no-check-certificate https://twine.supermechanical.com/rt/REPLACE_WITH_YOUR_TWINE_ID?cached=1

    DATE=`date`

    TEMP=`cat temp.txt | awk -F"," '{print $7}' | awk -F"]" '{print $1}' | tr -d ' '`

    echo "$DATE|$TEMP"

    GSHEET='https://docs.google.com/spreadsheet/formResponse?formkey=REPLACE_WITH_YOUR_FORM_KEY&REPLACE_WITH_YOUR_FORM_ELEMENT_NAME='$TEMP

    wget --quiet -O temp.txt $GSHEET
  • Joel FinkleJoel Finkle
    Vote Up0Vote Down
    No sweat - I can probably use a URL shortener. My use for this will be once the Vibration rules are available - the goal is to monitor how often the sump pump goes off and on.

    I should be able to add more complex alerts to that service script: has the sump pump gone on but not turned off (exit pipe might be frozen)? Has the pattern of pumping changed significantly, e.g. delta between off and on increased significantly (indicating perhaps pump stopped working because power is out)?

    The first one should be possible with TWINE by itself: A Vibrating, triggered after 200 seconds should be sufficient to say the sump isn't getting emptied. The second one is quite complex, though, and can't really be done without looking at several recent vibrate-on and vibrate-off messages.
  • YoYo PeteYoYo Pete
    Vote Up0Vote Down
    Here's a windows based script I wrote: https://github.com/YoYo-Pete/Twine/blob/master/getStatus.vbs

    Note you need to know your twine's ID just as in the shell script.

    I wanted to use WinHttp but didnt get it working so I used the IE object. Just playing with some VBS. I've got a Raspberry Pi coming that will be used in my 'Production' implementation but figured this might be helpful for some windows folks.

    Note it just returns the JSON value to a string. I've not parsed any of the values out yet. Will wait till my WinHttp script works to do that.
  • YoYo PeteYoYo Pete
    Vote Up0Vote Down
    I asked support if I can programmatically query twine such as this:

    https://twine.supermechanical.com/rt/00001a111a111a11?cached=1
    This url (when using the correct ID of my twine) will return the latest cashed JSON array for my device.

    https://twine.supermechanical.com/rt/00001a111a111a11?cached=0
    This url will force twine server to poll my device

    I have two questions:

    1) Is it okay to use/depend on these URLs for my own programmatic use?
    2) What is the min/max/error values for RSSI? (or if that is not know, which chipset is it? I can research and find out)

    They said....

    1) It's okay for you to use those URLs, but don't depend on them. I hope we'll have a similar but more robust way to do this soon.
    2) Absolute minimum is about 175, maximum about 235. (Pretty soon you'll see a graphical representation show up on the dashboard.)
  • Will LukeWill Luke
    Vote Up0Vote Down
    Great stuff, YoYo Pete. Does your JSON array display the temperature? If so, in what format? I couldn't see it listed in my array, or not in a format I could recognise
  • Brian LancasterBrian Lancaster
    edited January 2013 Vote Up0Vote Down
    I'm also playing with the JSON response and have decoded the following (what I have decoded is inserted with %BRIAN% and CR/LF onto a new line in this post) from a sample JSON response for a Twine with a magnetic switch sensor:

    {"gs_version": "Nov 15 2012-14:37:27",
    "age": 960.3220825195312, %BRIAN age of data or time since last report from Twine%
    "ts": 1358072576.0, %BRIAN Unix time of last report? %
    "values": [
    ["0000a5033a28af5600", "1.1.4"], %BRIAN Twine f/w version? %
    ["0000a5033a28af5601", 3500], %BRIAN temperature (F) * 100 %
    ["0000a5033a28af5603", "right"], %BRIAN position %
    ["0000a5033a28af5605", 2667952], %BRIAN battery voltage * 1000000 %
    ["0000a5033a28af5606", 0], %BRIAN don't know? %
    ["018a283f16000097", 1], %BRIAN MagSwitch status (1=closed)%
    ["0000a5033a28af5607", 0]], %BRIAN don't know? %
    "last_poll": 1358056960.0, %BRIAN Unix time of last report? %
    "rssi": 182} %BRIAN Receive level of WiFi during last report? %

    There are a few items that I am not sure of and would welcome anyone's input to these or a pointer to a full JSON response description (I did search for one but couldn't find it).
  • Tim WentfordTim Wentford
    Vote Up0Vote Down
    ["0000a5033a28af5605", 2667952], %BRIAN don't know? %
    I think this is battery voltage*1000000
    (obviously I'm not absolutely sure based on a sample of one...)
  • Brian LancasterBrian Lancaster
    edited January 2013 Vote Up0Vote Down
    Sounds reasonable (and obvious - doh!) - thanks Tim.
  • Will LukeWill Luke
    Vote Up0Vote Down
    Ah! Thanks Brian.
Sign In or Register to comment.