Hi there,
I am attempting to make a data logger that sends data to google sheets and have yet to find a good tutorial that suits my needs...
I have duinotech Uno with Wi-Fi - I have successfully coded it to connect to my home Wi-Fi (with the DIP-switches 5, 6 and 7 on; 1, 2, 3, 4 and 8 off)
I have a Spark-fun data-logging shield with an SD card, which I have successfully coded to receive data from a temperature sensor and potentiometer (with the DIP-switches 5, 6 and 7 on; 1, 2, 3, 4 and 8 off) and save it to the SD card, also display correctly on the serial monitor.
I have coded the UNO and the ESP8266 separately, is this correct? Or should I put all the code together? If so what should I do with the DIP switches? And, what one do I put the code on? (The UNO or the ESP8266?)
I am now trying to get these to send this data via Wi-Fi to google sheets. Most of the tutorials I have found don't have these 2 components together, maybe I'm am silly for using these together?
Should I put all the code in one file and code it all to the UNO? If yes, what should I do with the DIP switches?
If no, which one do I put the additional code in for the googlesheets?
(I have created a googlesheet and the google sheets script)
It does not sound to me like you understand how your board works. I am not surprised about that. I do not think I could recommend this board to anyone, especially a beginner. It is such a poor and confusing design, in my opinion. The arrangement with the dip switches force the user to write and debug two separate sketches, and prevents the two sketches from being debugged and tested when the two sketches are used together. This is very bad news for a beginner because having to write two sketches that communicate with each other is not something a beginner should attempt, it makes the whole project much more difficult.
Would you consider using another board? I would recommend a Wemos D1 mini.
I don't understand why you want to add an SD card reader, if you want to store data in Google sheets. Is there a problem with intermittent wi-fi or internet connection where the project will be deployed?
Your topic has been moved to a more suitable location on the forum. Introductory Tutorials is for tutorials that e.g. you write, not for questions. Feel free to write a tutorial once you have solved your problem
Thanks for your response.
The SD card is a backup, so that if Wi-Fi drops out.
I am new to this and hence my questions.
I have coded the UNO and the ESP8266 separately, is this correct? Or should I put all the code together? If so what should I do with the DIP switches? And, what one do I put the code on? (The UNO or the ESP8266?)
I am now trying to get these to send this data via Wi-Fi to google sheets. Most of the tutorials I have found don't have these 2 components together, maybe I'm am silly for using these together?
Should I put all the code in one file and code it all to the UNO? If yes, what should I do with the DIP switches?
If no, which one do I put the additional code in for the googlesheets?
If your topic was left in the Introductory Tutorials section, people might find it when looking for a tutorial and will be disappointed if your topic does not contain a tutorial that they can follow.
I don't think you have any other choice, you have to write a sketch for the Uno because that is connected to the data logging shield, and you have to write a sketch for the ESP because only that can connect to wi-fi/Google sheets.
I suggest starting with the Uno sketch. Get that to read your sensors and write the data to the SD card. Also echo the same data (the text lines that get written to the CSV file on the SD card, I assume) to serial monitor. This means you can check what is being written and save some example data in a document on your pc for use in testing later.
Then you can write the ESP sketch to read data from serial monitor and send it to Google sheets. You can paste some of the saved example data into serial monitor to test your esp sketch.
When both sketches are tested and working, you can set the dip switches to feed the data coming from the Uno to the ESP.
I guess you will get the time from the GPS? This will probably only work outdoors. If the sensor is indoors and there is WiFi/internet connection, it could get the time from an NTP server, but this makes things more complicated.
Thanks for your advice - I am trying to get temperature data (not from a DHT) and potentiometer through the following path:
Real time Sensor data > Arduino > SD - done and working correctly (using data logger shield)
Real time Sensor data > Arduino > LCD screen - done and working correctly
Real time Sensor data > Arduino > Serial Monitor > Done and working correctly
Arduino > integrated ESP8266 > connecting to WiFi > Done and working correctly
Real time Sensor data > Arduino > integrated ESP8266 > Done, sort of, I can see the data in the serial monitor, however it is a single line of text, e.g. 3.7321.64Date 3/9/2022Time 11:57:32
I have set up a dummy IDE that I can get to send random numbers to Google sheets so I know that I can get that part to work.
The next step is to get the single line of text, e.g. e.g. 3.7321.64Date 3/9/2022Time 11:57:32, into a format that I can send to Google Sheets.
All the examples I find seem to use fixed numbers or the DHT sensor. I don't want to use the DHT sensor because once I get this data flow working, I am going to swap out the temperature sensor and potentiometer for a flow meter and oximeter. Which is going to be way more difficult! So I want to make sure the flow from the sensor > arduino > esp8266 > google sheets works first. Then I can tackle the flow meter and oximeter part.
Does anyone have any ideas about the:
"The next step is to get the single line of text, e.g. e.g. 3.7321.64Date 3/9/2022Time 11:57:32, into a format that I can send to Google Sheets."
Thanks for all of your insights thus far, it has helped me progress!
Can anyone help with the issues above - I feel like I am very close but need a bit of advice to finish!
I have changed the string slightly to show Voltage,2.77,Temperature,21.29,Date,1/10/2022,Time,20:47:27
I am not an expert in dealing with data transfer to Google Sheets via Google Scripts, but I may be able to offer one approach. I struggled finding how to send multiple parameter data, and especially parse it from an ESP32 directly to a Script doGet(). There may be better ways, but this worked: Google Scripts has two ways of registering data: e.parameter for one value, or e.parameters in form {"key:["value1, value ",,,]}. I found that this Script function: JSON.parse("key") where key is the name of the key was convenient to parse the incoming data. In my specific case, the data string sent to the Script had the general form:
This string has three keys and the corresponding data for each key, the piezo weight sensor calibration factor, and separate arrays of time in seconds, and corresponding weights in grams.
On the Script side, the code segment to parse just the time, weight data was:
tf ((e.parameters !== "undefined")) {
var tempt = JSON.parse(e.parameters.times);
var tempw = JSON.parse(e.parameters.wts);
count = tempt.length;
for (var i=0; i<count; i++) {
sheet.getRange(i+3, newColumn).setValue(tempt[i]);
sheet.getRange(i+3, newColumn+1).setValue(tempw[i]);
} }
Be forewarned that the ESP32 sketch and the code.gs file there are fairly extensive and specific for the load cell streaming data recording project. Some effort will be required to ferret out specific information that may be relevant for your project. Because you appear to have already gone through some effort to understand communicating to a Google Script that should help understand some of the logic.
If that is the case, consider just buying the Sparkfun OpenLog. It is cheap, standalone, and simply records everything you decide to print on Serial. Gigabytes of data, if you like, and no programming is required.
Of course, you have the problem of how to recover from WiFi dropouts, no matter what you do, and that is not at all trivial to program.
