Sending specific cell value from Google Spreadsheet to Arduino

Projects Interfacing w/ Software on the Computer
1

Hello all

I am successfully posting from my Arduino to a Google Spreadsheet via Google Form using GSM/GPRS shield.
I am able to manipulate the data on the Spreadsheet.
Now I need to send some of the manipulated data in the cells on the Spreadsheet back to the Arduino.
I need to send the values of specific cells in my Spreadsheet back to the Arduino.

Is there any way for me to do this?

I am able to publish the spreadsheet to html (automatically as it changes), which then allows me to inspect its elements (i.e the value in a particular cell). I don't know if this will be of any use.

Thank you very much in advance for your help!

2

Now I need to send some of the manipulated data in the cells on the Spreadsheet back to the Arduino.
I need to send the values of specific cells in my Spreadsheet back to the Arduino.

Is there any way for me to do this?

No. The client that send the input has gone away. There is NO way for the server to contact the client.

The client (the Arduino) could ask the server what has changed, and then ask for the changed data.

3

Thanks PaulS

If I run a GET on the html published spreadsheet, is there not some clever way that I can parse the recieved text and pull out a tag value? For example if I were to recieve this text from the Arduino, will I somehow be able to run through this and pull out a variable (which would be either "Arduino" or NotArduino")? In the code below, "Arduino" is the data sitting in one of the cells on the Spreadsheet.

<html>

    <head></head>
    <body onload="init()">
        <div id="top-bar"></div>
        <div id="sheets-viewport" style="width: 1317px; height: 198px;">
            <div id="89591840" dir="ltr" style="position: relative;">
                <div class="ritz grid-container" dir="ltr">
                    <table class="waffle" cellspacing="0" cellpadding="0">
                        <thead></thead>
                        <tbody>
                            <tr style="height:21px;"></tr>
                            <tr></tr>
                            <tr style="height:21px;"></tr>
                            <tr style="height:21px;"></tr>
                            <tr style="height:21px;"></tr>
                            <tr style="height:21px;"></tr>
                            <tr style="height:21px;">
                                <th id="89591840R5" class="row-headers-background row-header-shim" style="height: 21px;"></th>
                                <td class="s1" dir="ltr"></td>
                                <td class="s0" dir="ltr">

                                    Arduino

                                </td>
                                <td class="s0" dir="ltr"></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <script type="text/javascript"></script>
        <script type="text/javascript"></script>
    </body>
</html>

Let me give a brief description of what I am doing and trying to achieve:

Using RFID card reader to obtain unique card number.
Using keypad to input a user chosen pin number.
Populating Google Spreadsheet (server) with both of these entries.
Using spreadsheet to verify if the pin code matches the unique card number
If there is a match, a single cell in the spreadsheet will be either "accept" or "denied"

The server does not need to initiate contact, the client will know to check the accept/deny state a second or so after the data has been sent to the spreadsheet

Thanks again!

4

For example if I were to recieve this text from the Arduino, will I somehow be able to run through this and pull out a variable (which would be either "Arduino" or NotArduino")? In the code below, "Arduino" is the data sitting in one of the cells on the Spreadsheet.

Where would you receive that text FROM the Arduino? Or, did you mean "if I were to receive this text ON the Arduino"?

If that is text received ON the Arduino, that could be parsed (not easily), since the data is between td and /td tags, and the data between the and contains "s0" AND more than one and .

Frankly, what you are trying to do would be far easier with PHP and MySQL than using gagme (I mean google) spreadsheets, but that might just be me.

5

PHP and mySQL (setting up a server etc) is not in my skillset, but I do need to learn it at some stage, which might be now.

I am trying to do it via Google Spreadsheets because I'm noob and also want it to be free! This is my last attempt before I learn the above.

Where would you receive that text FROM the Arduino? Or, did you mean "if I were to receive this text ON the Arduino"?

Using GET on the published spreadsheet html:

if (client.available())
  {
    char c = client.read();
    Serial.print(c);
  }

this is where I would hopefully get the text from

6

I am trying to do it via Google Spreadsheets because I'm noob and also want it to be free!

PHP and MySQL fit that budget, without all the grief of using google. Well, OK, there may not be all that much grief, and there is a learning curve (well worth tackling) to PHP and MySQL.

Using GET on the published spreadsheet html:

That gets the data ON the Arduino. You need to save data in an array until you detect stuff of interest in the stream, like '<'. When that happens, store stuff in the array, until '>' arrives. Then the array could contain something like "tr". When that happens, set a flag that indicates that interesting data may be coming.

When that flag is set, save to another array.

When the input stream contains '<', save to the first array. When the stream contains '>', look at the value in the first array. If it contains "/tr", then the second array may contain Arduino or NotArduino (although I confess I'm confused about the "accept" or "denied" comments).

If you used a PHP script, you'd have complete control over the server response, which could be simply 0 or 1. Far simpler to parse that, isn't it?

7

Thanks again PaulS

I agree fully with all you are saying. It is a possible (but bad) solution to the problem. I will investigate setting up a server, which might take a lot of time since I essentially have zero experience. Do you know off had any reference links for something like "creating servers for dummies" to get me started? Including domain name registration etc.

"Arduino" = "Accept" = 1
"NotArduino" = "Deny" = 0

Just any state variable generated on the server side

Using GET on the published spreadsheet html:

I meant that the Arduino must do something like

if (client.connect(server, 80)) {
   client.print("GET https://docs.google.com/spreadsheets/d/[private_formkey]/pubhtml");

and parse said state variable out of all that the Arduino receives

8

Do you know off had any reference links for something like "creating servers for dummies" to get me started?

A lot of that will be handled by whatever WAMP installer you use (assuming you are using Windoze).

You don't need a domain name, etc. to get started.

I meant that the Arduino must do something like

I had assumed that that was what you meant. The snippet you showed, though, was how the Arduino deals with the server response to the GET request.

9

Am dualbooting Win7 and Linux
Slight experience with Wamp and Xamp (I think, can't remember) hosting local server and playing with SQL db's. Also minor PuTTy blah blah blah...

I need to jog my memory a bit

Thanks again PaulS, I will seek any further help in different topic threads!

10

Hi!

Would like say few fords about topic.

It's pretty simple and open incredible possibilities! ....like store some historical data on spreadsheet and make some calculation and then publish (send) results to control equipment. Or "remote settings" by simple editing the spreadsheet with clear comments and strong value checking.

Important task - minimize garbage traffic from server to client.

So, to import some cell from Google Spreadsheet:

  1. Create spreadsheet
  2. File/Publish to the web. Start Publishing.
  3. Get a link to the published data. Change "Web Page" to ATOM
  4. Selectesd sell.... and give the range of sell that you need to send to Arduino. In my example it's one cell D10
  5. Copy URL it will look like:

https://spreadsheets.google.com/feeds/cells/0BpZvAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic?range=D10

Use Get request with this link. reply will look like:

<?xml version='1.0' encoding='UTF-8'?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' xmlns:gs='http://schemas.google.com/spreadsheets/2006' xmlns:batch='http://schemas.google.com/gdata/batch'><id>https://spreadsheets.google.com/feeds/cells/0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic</id><updated>2013-09-22T09:15:09.974Z</updated><category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#cell'/><title type='text'>Form Responses</title><link rel='alternate' type='text/html' href='https://spreadsheets.google.com/pub?key=0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE'/><link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic'/><link rel='http://schemas.google.com/g/2005#batch' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic/batch'/><link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic?range=d10'/><author><name>igor.kovba</name><email>igor.kovba@gmail.com</email></author><openSearch:totalResults>1</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><entry><id>https://spreadsheets.google.com/feeds/cells/0ApZvAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic/R10C4</id><updated>2013-09-22T09:15:09.974Z</updated><category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#cell'/><title type='text'>D10</title><content type='text'>456</content><link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/0ApZSAfRKvUfDdElxdmRLZ1FqSEVKejVJTjRoZmRvVEE/od6/public/basic/R10C4'/></entry></feed>

Unfortunately seems it's minimum garbage that we can get with all possible formats.

Important for us is " term " header line:

term='http://schemas.google.com/spreadsheets/2006#cell'/>D10456<link rel='self' type='application/atom+xml'

So 456 is content of D10 cell. I hope you will be able parse it with
Serial.find("'text'>D10");
Serial.parseint();.....

Obviously we can share more than one cell.

Have a nice day,
Igor

11

If some body knows how to SEND data from Arduino to Google spreadsheet (except using URL and linux in YUN:) it will be grate news.

Usually I use URL from Google Form, but it don't work after new version of Google docs :frowning:

Seems this method work if request sent form browser....but unfortunately, I can't make it work from Ethernet sheeld (with UNO or Mega).

Thanks!!!