Wifi Datalogging to Google Sheets

[Christine Liu]

If you're interested in setting up your FED3 device to automatically upload information to a Google Sheet, I've managed to implement it by adding on an Adafruit QT Py ESP32-S3.

Wiring:
- Attach QT Py to the FED3 via a STEMMA QT cable on the QT Py to the extra i2c pins on the FED3 PCB (circled in magenta below). You can either solder header pins here and use a cable with sockets or just solder the wires directly. Match the wires as follows: 3V3 - Red,  SCL - Yellow, SDA - Blue,  Ground - Black. 

Coding:

The fundamental concept is that the FED3 device is a Controller and sends bytes of data through the i2c wire to the Adafruit QT Py. The QT Py is the Target and each time it receives data, it uploads to a Google Sheet. You will need to adjust the code that is uploaded to the Feather M0 to tell the FED3 what data to send, and a corresponding sketch needs to be uploaded to the QT Py that includes information for the Google Sheet. 

I currently have it set up so that each FED device uploads to its own Google Sheet with the Date/Time, Battery Level, Mode, Total Pellets Earned, and a custom variable that counts Consecutive Correct choices. You could likely configure this concept to upload all of the information that would normally be saved in the SD Card, but I only implemented this capability recently and use it mostly to track how quickly an animal is being trained, and for peace of mind that they are eating and there's sufficient battery remaining.

 - Coding the FED3 Feather M0

write a function that sends data to the QT Py, here's an example below for the variables I'm interested in (fed3.counter is a custom variable that I added to the FED3 library, yours will not have it):

     void sendData() {  //send session type and pellet count to qtpy

  Wire.beginTransmission(8);                          // Address 0x08 is set in the sketch uploaded to the QT Py

  Wire.write(String(fed3.FEDmode).c_str());

  Wire.write(",");

  Wire.write(String(fed3.counter).c_str());

  Wire.write(",");

  Wire.write(String(fed3.PelletCount).c_str());

  Wire.write(",");

  Wire.write(String(fed3.measuredvbat).c_str());

  //Wire.write("\0"); // empty byte

  Wire.endTransmission(8);

}

add to setup function:

Wire.begin();

add your custom function to  the loop function whenever you want the QT Py to receive data. I currently send data after every FED3.Feed() function:

       sendData();

 - Coding and Setting up the Adafruit QT Py:

First, you will need to set up a Google Service account and the Google Sheet(s) that you want for each device. Follow these instructions from Random Nerd Tutorials.

Attached is my template sketch, which I hope is commented well enough to follow. Key things that you will need to do:

- add your own Google Sheet info

- add your own WiFi network info

- adjust to your own time zone

- remove my counter variable and ensure your dataIn matches the sendData from your FED3 FeatherM0

Troubleshooting:

Some of my QT Py devices have better wifi connectivity than others. They are not too expensive ($12.50 USD) so you could always replace the ones that are buggy. 

The QT Py i2c address must be set by you in the QT Py sketch. Even if plugged in, it won't be detected on an i2c scan on the Feather M0 until you set the address. I arbitrarily set it to 8 (or 0x08 in i2c address syntax), but if for some reason you already have something at that address, you can set it to something else. 

The bytes that are sent over the Wire function must be chars, so convert your variables into char. I've done this by converting all of my variables to Strings, then converting them to chars. Clunky, but seems to work so far and can be done in a single line. 

-----------------

If you have any questions, suggestions, etc. please let me know! I just got this working a couple weeks ago so I'm still troubleshooting but thought I'd share as it has given me a lot of peace of mind and convenience to monitor my animals from afar.

[Christine Liu]

Here are some screenshots of one of my Google Sheets. For the example QT Py sketch I uploaded, the Google Sheet needs to have 'Sheet1' to log all of the raw input from the QT Py, and then another Sheet named 'Latest' that shows the most recent data entry, which saves you some scrolling to glance at the current status. Because access to the Google Sheet times out after about an hour, the QT Py pushes the current Date/Time to 'Latest' after 55 minutes of inactivity to prevent losing access. 

The data in 'Latest' could also be pulled to a separate aggregate Google Sheet that you could set up to see the current status of all of your FED3 devices. Note the function EPOCHTODATE() that converts the Unix Epoch Time to Human Date Time. Also I just learned you can create custom functions in Google Sheets, and I created one called FED3MODE that converted the Mode # to plain text for my own convenience. In the future I will likely configure the QT Py to just send the information as chars because every new google sheet requires you re-input your custom functions. 

The 'Data' sheet takes all the data from Sheet1 and includes functions to convert the Unix Epoch Time and Mode. This might be possible to do in Sheet1 itself but I haven't tested it yet. Note the ARRAYFORMULA(if(isblank.........) function that is in the second row of the Data spreadsheet which allows the 'Data' sheet to populate itself whenever Sheet1 is updated. This is all very preliminary and I welcome your thoughts and suggestions.

      

[Lex]

Christine thanks so much for posting this, and for your other posts!  This is incredible, I've tried in a few ways to do wireless data transfer but my approaches haven't scaled well, I'll give this a shot!  It's so cool to see you hacking into FED3 :)  -Lex

--
You received this message because you are subscribed to the Google Groups "FEDforum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fedforum+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/fedforum/49267e40-6c22-4b28-a4be-0b277bb63f6dn%40googlegroups.com.

[Christine Liu Art]

Thank you! I have learned so much while tinkering with these awesome devices you’ve designed! 

In fact, after a couple weeks of using this system, I would advise against having individual sketches for each QT Py because it is very time consuming to make updates to every file, especially at scale.

I would recommend writing one sketch that can be flashed to all of them where the FED Device number is also sent in the Wire transmission, then an if statement that uploads to the correct Google Sheet, or a tab on a common Google Sheet (I.e Sheet1 for Device 1, … SheetN for Device N) 

Cheers!

Christine 

[dautan...@gmail.com]

Hello Christine, what was the reason to use wifi transfer rather than using a very low power-hungry BLE (Bluetooth low power) that can be setup on a Windows PC and collect hundreds of boxes?

I am curious as wifi is in general very energy consumption.

[Lex]

Different wireless technologies have different pros/cons.  Whatever the technology, harvesting data wirelessly from multiple devices is not trivial to implement. I applaud Christine for hacking into FED3 and posting the first solution I've seen that works! If you have a BLE solution please post it!  Best, -Lex

To view this discussion on the web visit https://groups.google.com/d/msgid/fedforum/e564b66b-122e-4c22-8d0a-d9c1341e4b2fn%40googlegroups.com.

[Matias Andina]

Hi,

I am also curious about this solution! It looks quite promising if you want to go wireless. I was wondering about the hurdles you found with:

1) internet access and University's IT. During the last years, our IT department has constrained network access for most devices either through mandatory 2FA, restricted passwords that have device limits in the tens and also expire once a year, or blocking devices so that they can't see each other on 'separate' networks. I understand why they do this, but it's nonetheless been painful and preventing me of using ESP32 type devices.

2) I'm curious about the choice of Google docs as the place to sync (again, Google account safety stuff might introduce some hurdles). I'm wondering if this could be changed to ssh into a known Lab server with fixed IP address (this is what I use for synchronizing FEDWatcher data and other data collected in behavior stations controlled with Raspberry Pis)

Best,

Matias

To view this discussion on the web visit https://groups.google.com/d/msgid/fedforum/CAPM2s1QgE54%3DUXcVHQb5D6d80%2BQGgXPMnnouh53VbK_34ts9Lg%40mail.gmail.com.

[Christine Liu]

Thanks for the discussion! I basically went for the most convenient methods for my current situation, which might change once I have more than 5 devices, and perhaps someday a fixed location to run my animals.

Bluetooth: Wifi is ideal to start with, because we have signal in every room and our lab has several behavior rooms spread across two floors. This way, I don't need to move a bluetooth receiver/computer with my animals each time I run them somewhere different, nor would I need multiple receivers/PCs if I run them in multiple rooms at a time. Also, my task requires that the Sleep function is disabled on the FED3 which drains the battery quickly and I have not noticed any additional load from wifi transmission due to the extreme demands on the battery from the task itself. 

University IT: I was surprised that the UCSFguest network (no password) works perfectly with the ESP32. I did not expect it to, and I had talked to my PI about setting up a lab intranet, but fortunately did not have to go down that route. I am almost certain that the university's password-protected networks that also require annual password changes, mandatory 2FA, and/or specialized encryption software to be downloaded to devices, would cause issues. With a lab network, I might still have to set up multiple routers to have signal in all of our behavior rooms that are spread across two floors in our building. If you already have a lab server that can be identified over Wifi network searches, I would assume it would work. Regular password protected networks, such as at home, worked just fine during testing. Our University IT can be very non-responsive, and they prefer to restrict access to the point where most ethernet ports in our lab do not function -- I was not optimistic about setting up a lab intranet or server with fixed IP address. 

Google: As for Google Sheets, I am mentoring some undergraduates who are interested in learning Python, so I thought using Google Colab with the auto-updating Google Sheets would be a good starting point. I created a dedicated gmail address specific to this project, and would recommend other users do the same. I imagine this might also be a good starting point for many other FED3 experimenters, but I am sure there are better solutions especially when the data do not need to be accessed by multiple users.

Cheers!

[dautan...@gmail.com]

I was not criticizing, it was more a question regarding the battery life, and how does wifi transmission can affect it.

I was playing a bit with the BLE system a while back, and I really like Christine approach.