Introduction: How to Use Google Docs for Data Logging
I have a Netduino+ hooked up on the Internet with data logging via Google docs.
Before i found this i was collecting the data via a SD card, on the Netduino.
I read almost every day Hackaday here i stumbled up at http://hackaday.com/2012/05/31/data-logging-directly-to-google-docs-google-drive/
But this didn't work for me, but i knew i was on the right trail.
After a lot of googleling it worked for me, this (story) is how i did it.
I have tried to simply the whole thing in only text, with links to more relevant documents.
Please ask in the comments and i will try to answer these.
If something relevant is asked i maybe will change this instructable to clarify some streps, so please read first then ask.
This way of collecting is also useful with writing software with a database, you don't need a server with SQL or other language.
Before i found this i was collecting the data via a SD card, on the Netduino.
I read almost every day Hackaday here i stumbled up at http://hackaday.com/2012/05/31/data-logging-directly-to-google-docs-google-drive/
But this didn't work for me, but i knew i was on the right trail.
After a lot of googleling it worked for me, this (story) is how i did it.
I have tried to simply the whole thing in only text, with links to more relevant documents.
Please ask in the comments and i will try to answer these.
If something relevant is asked i maybe will change this instructable to clarify some streps, so please read first then ask.
This way of collecting is also useful with writing software with a database, you don't need a server with SQL or other language.
Step 1: Filling a Spreadsheet
Filling a spreadsheet in google docs goes always via a form:
After hitting the Submit button on this form the URL is send to Google docs:
https://docs.google.com/spreadsheet/formResponse?formkey=Here_goes_the_formkey&ifq&entry.0.single=opeens&entry.1.single=nu&pageNumber=0&backupCache=&submit=insturen
When you change the URL with the right values then you can send your data from a micro-controller or software to the spreadsheet.
After hitting the Submit button on this form the URL is send to Google docs:
https://docs.google.com/spreadsheet/formResponse?formkey=Here_goes_the_formkey&ifq&entry.0.single=opeens&entry.1.single=nu&pageNumber=0&backupCache=&submit=insturen
When you change the URL with the right values then you can send your data from a micro-controller or software to the spreadsheet.
Step 2: Use Google Docs to Collect Data of a Micro Controller.
To use Google docs you need an account, if you make your project for somebody else then make for that project a new account.
Login and go in google docs to new-->form
Very importantvVia this form the data is collected and put in the spreadsheet.
No form, No spreadsheet.
For every parameter you will follow, make a field.
(# means a number
: means "this will be" "in the URL")
Standard HTML:
Text field : entry.#.single
textarea : entry.#.single
radio : entry.#.group // Same group number belongs to the same group.
checkbox : entry.#.group // Same group number belongs to the same group. entry.3.group=value1&entry.3.group=value2
option/select : entry.#.single
Special:
scale : based on type radio on a row
grid : based on type radio in a grid
The most used fields would be a text field for values and radio button for boolean.
Login and go in google docs to new-->form
Very importantvVia this form the data is collected and put in the spreadsheet.
No form, No spreadsheet.
For every parameter you will follow, make a field.
(# means a number
: means "this will be" "in the URL")
Standard HTML:
Text field : entry.#.single
textarea : entry.#.single
radio : entry.#.group // Same group number belongs to the same group.
checkbox : entry.#.group // Same group number belongs to the same group. entry.3.group=value1&entry.3.group=value2
option/select : entry.#.single
Special:
scale : based on type radio on a row
grid : based on type radio in a grid
The most used fields would be a text field for values and radio button for boolean.
Step 3: How to Implement This?
To try it out as a URL in the browser follow these steps:
In the form editor click in the bottom of the page on view in real, open here the source of the HTML.
Search for FORM action=""
In here is the link to the spreadsheet.
In the form editor click in the bottom of the page on view in real, open here the source of the HTML.
Search for FORM action=""
In here is the link to the spreadsheet.
Step 4: The (simplified) Code
const string FormKey = "Here comes the form key"; // Important DON't use the spreadsheet key. this is different key
static string GoogleIP = "spreadsheets.google.com"; // Google adress
static Int32 GooglePort = 80; // Port Number 443=https 80=http
Fill the parameters:
formdata = "&entry.0.single=" + value.ToString();
formdata += "&entry.1.single=" + value.ToString();
formdata += "&entry.2.single=" + value.ToString();
formdata += "&entry.3.single=" + value.ToString();
formdata += "&pageNumber=0&backupCache=&submit=insturen"; // close with this these are hidden on the form
Send parameters to Google Doc(formdata are your parameters)
request = "POST /formResponse?formkey=";
request += FormKey;
request += "&ifq HTTP/1.1\r\n";
request += "Host: ";
request += GoogleIP+"\r\n";
request += "Content-Type: application/x-www-form-urlencoded\r\n";
request += "Connection: close\r\n";
request += "Content-Length: " + formdata.Length + "\r\n";
request += "\r\n";
request += formdata;
request += "\r\n";
How to send your data is for each program language different.
static string GoogleIP = "spreadsheets.google.com"; // Google adress
static Int32 GooglePort = 80; // Port Number 443=https 80=http
Fill the parameters:
formdata = "&entry.0.single=" + value.ToString();
formdata += "&entry.1.single=" + value.ToString();
formdata += "&entry.2.single=" + value.ToString();
formdata += "&entry.3.single=" + value.ToString();
formdata += "&pageNumber=0&backupCache=&submit=insturen"; // close with this these are hidden on the form
Send parameters to Google Doc(formdata are your parameters)
request = "POST /formResponse?formkey=";
request += FormKey;
request += "&ifq HTTP/1.1\r\n";
request += "Host: ";
request += GoogleIP+"\r\n";
request += "Content-Type: application/x-www-form-urlencoded\r\n";
request += "Connection: close\r\n";
request += "Content-Length: " + formdata.Length + "\r\n";
request += "\r\n";
request += formdata;
request += "\r\n";
How to send your data is for each program language different.
Step 5: Now You Can Use the Spreadsheet As a Database
Document for using a spreadsheet as a database:
https://developers.google.com/chart/interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL
Database examples:
https://spreadsheets.google.com/tq?tqx=out:html&tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27&key=Spreadsheet key
here is:
tqx=out:html (or csv|json) Maybe other outpu formats?
tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27
(* means all)
Select all columns but use a format on column A --> dd-MMMM-yyyy hh:mm somthing like 03-august-2012 12:23
https://spreadsheets.google.com/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E&key=Spreadsheet key
here is:
tq=select+A,+B,+C,+D,+E
Select all data of column A,B,C,D,E,F
With select you can use calculations or other simple query's. See the document for examples.
With this URL you can get the data in your program, and manipulate it.
https://developers.google.com/chart/interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL
Database examples:
https://spreadsheets.google.com/tq?tqx=out:html&tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27&key=Spreadsheet key
here is:
tqx=out:html (or csv|json) Maybe other outpu formats?
tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27
(* means all)
Select all columns but use a format on column A --> dd-MMMM-yyyy hh:mm somthing like 03-august-2012 12:23
https://spreadsheets.google.com/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E&key=Spreadsheet key
here is:
tq=select+A,+B,+C,+D,+E
Select all data of column A,B,C,D,E,F
With select you can use calculations or other simple query's. See the document for examples.
With this URL you can get the data in your program, and manipulate it.
Step 6: Ready
I wrote this as is. So please ask and i will try to answer.
This step for step instructables i have made is for my self so i can not forget it,
but i have put it in the public because it is a nice way to collect your data and like to share with you.
Google docs will soon be Google drive, i hope this will work then to.
I have tried it 03-07-2013 and it still works:
spreadsheets.google.com/formResponse?formkey=Here comes your formkey&ifq&entry.0.single=10&entry.1.single=20&entry.2.single=30&entry.3.single=40&pageNumber=0&backupCache=&submit=insturen
Greetings,
This step for step instructables i have made is for my self so i can not forget it,
but i have put it in the public because it is a nice way to collect your data and like to share with you.
Google docs will soon be Google drive, i hope this will work then to.
I have tried it 03-07-2013 and it still works:
spreadsheets.google.com/formResponse?formkey=Here comes your formkey&ifq&entry.0.single=10&entry.1.single=20&entry.2.single=30&entry.3.single=40&pageNumber=0&backupCache=&submit=insturen
Greetings,