Sunday, April 9, 2017

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.

No comments:

Post a Comment