Updated: Datalogging with Google Sheets in 2017

In 2011 I posted an article about datalogging with Google Sheets.  I just got a comment asking if the info was still valid.  Google Sheets has changed a lot in the last few years, so here is an updated tutorial for 2017.

1.  Go to Google Sheets
2.  Create a blank sheet

3.  Create a form

4.  Create a single question of type "short answer"

5.  When you've created the question "What is the temperature" with type "short answer" as above, close the tab in your browser.

6.  Reopen the spreadsheet from step 3.  You should now see:

If you don't see this, close and re-open the spreadsheet from Google Sheets.

7.  Google now stores all of your forms at Google Forms.  Go there and you should see the form you created in step 4.

8.  Click the SEND button

9.  Click the link icon to get a link to your form

10.  Copy/paste the link into your browser.  I'm using Chrome.

11.  After the form opens in your browser, hit ctrl+shift+i to open the developer console.

12.  Click the "Network" tab, and also click the option "Preserve Log".  Your screen should look like this:

13.  Type an answer in your form and hit submit.

14.  A bunch of stuff will appear in the Network tab.  This is the browser magic that happens in the background.  Scroll to the top and find the "formResponse" with type "document", like this:

15.  Click this line, and you'll be able to see more information.  The cool stuff will be the "general" tab and the "form data" tab as shown here:

16.  Now we have enough information to assemble a URL that simulates a form response.  The format is:

• Your request URL: (copy/paste from the information above)
• Add a question mark to the end of the request URL
• Add entry.xxxxxx= after the question mark (copy from the "Form Data" section)
• Paste that entire string into your browser's address bar and hit enter.

Here is an example using my spreadsheet:

https://docs.google.com/forms/d/e/1FAIpQLSe0K7ICSWcMv_-oBO42-irBkkSln4Qu5IxPfPgT-9GQqOyB7w/formResponse?entry.1743242728=11223344

If you copy/paste the above URL into your browser, you will append "11223344" into my example sheet.

Similarly, if you copy/paste this URL into your browser:

https://docs.google.com/forms/d/e/1FAIpQLSe0K7ICSWcMv_-oBO42-irBkkSln4Qu5IxPfPgT-9GQqOyB7w/formResponse?entry.1743242728=999999

999999 will be appended into my example sheet.  I've made the sheet public and you can see the results instantly here.

If you follow the instructions above, you can do the same thing for your own sheets, and use them for datalogging, from IoT devices or really anything that is internet-enabled.

Have fun.

Martian Notifier Teardown

I bought a Martian Notifier watch new from Amazon for $33.  This seemed to be the perfect 'smart' watch for me, it has only one line of text and has a battery life (for the 'smart' portion) of about five days.  The 'watch' portion uses a quartz movement and separate battery that will give you the time with real hands for about three years.  Although it is now on the market for more than 3 years, the liquidation price was attractive to me.

My Martian Notifier (with aftermarket nylon band)

I like the look of a nylon band (seems military-ish) so I bought this one originally for my Moto 360 (a pile of garbage that I returned after a week).  I still had this band and the 22mm size was a match for the Notifier, so on it goes:

Aftermarket 22mm nylon band on the Notifier

(Thinking about) Hacking the Notifier

I've been wearing the Notifier for a week now, and I like it.  However, as with any tech there are things I wish it did differently.

I can't find any evidence that anyone has tried to hack the Notifier's firmware yet.  So far I've been able to capture a firmware flash of the watch, but I've not tried to reflash it yet.  Martian created a Windows or Mac based Firmware upgrade tool that downloads the firmware and updates the watch.

I've got USB captures of this tool asking the watch for the firmware version as well as the entire upgrade process.

Before bricking my new watch, I wanted to know what kind of hardware was inside it, so I purchased a broken one on eBay for $5.  The broken watch was in perfect shape, so I charged it overnight to be sure it was dead.  The watch showed some signs of life, with the notification LED blinking red (solid red is normal for charging) but the watch never fully booting.  I took the watch apart and found that it had terminal water damage.  It was a good teardown candidate.

Notifier Teardown

Surprisingly, it seems no one has done a public teardown of this watch yet.  I did find this commercially available teardown report but to you have to buy it to read it.

Here's my ten-cent teardown.

Photo album of terrible photos here.

Martian Notifier Watch

• Watch movement: Miyota 2025 2 hand quartz movement
• Same movement used in Citizen quartz watches
• 3 year battery life
• Specifications
• Drawing:
• Video of movement appearance
• Watch battery: 377 / SR626SW

Martian Notifier Layout

Martian Notifier Connected Device Hardware Details

• Battery: AE292025P -- marked as containing lead -- LiPoly? 
• 3.7V (?), 0.44Wh
• Can't find a hit for the 292025 part number, but 302025 looks very similar.
• Display: PG-9616TSWD
• 96x16 OLED
• I2C interface
• This one is similar
• Microprocessor: CSR 8670CG
• Has a lot of capabilites, including onboard DSP and audio output
• Not much info about programming this device in the public domain
• 3-Axis Accelerometer: 263 8451 3EAF
• Comes from NXP.  Datasheet here.
• Probably Martian was intending to use this accelerometer for fitness tracking, but maybe due to the hit to battery life decided against it.  The watch software and phone companion app makes no use of this hardware that I can tell.
• Flash memory: MX25U1635E (3R181 000-X)
• Macronix serial flash, 1.8V, 16 MBit
• Not clear what the purpose of this flash storage is.  The 8670 micro has 16 Mbit of flash onboard already.
• Guess: this flash storage is possibly used for holding an updated version of the device firmware, rather than reprogramming the flash storage on the micro?

If I get any farther hacking the watch, I'll have another post about it.