Floating point values appear in sheet as 0 (zero)

[davidhbrown]

This is a super-helpful library and the essentials are working great... although whenever I try to valueRange.set(..., a_float) the value appears in the sheet as 0.

In the code I'm developing, I mostly want to log temperature. The temperatureC is coming from a Dallas OneWire 18B20 temperature sensor and rtc.temp() is from a DS3231 which returns an (int) in centidegrees Centigrade:

        float temperatureC = sensors.getTempC(tempDeviceAddress);
...
        valueRange.add("majorDimension", "COLUMNS");
        valueRange.set("values/[0]/[0]", epochTime);
        valueRange.set("values/[1]/[0]", rtc.temp());
        valueRange.set("values/[2]/[0]", temperatureC);
        valueRange.set("values/[3]/[0]", 12.34); // just to test

The first two columns 0/1 have their expected values. The next two columns (2/3) are both 0.

Your Firebase library says it should work with floats (and a bunch of other types besides), so maybe this is specific to this Sheets library? I could work around by multiplying by 10^precision_needed, but perhaps someone here knows what's needed for this to work as expected.

I would ideally like to send a human-readable string time, too.

Thanks!

[mobizt]

The ValueInputOption parameter in update and batch functions, will be USER_ENTERED by default, you should set it to RAW when setting float number or set the number string "12.34" which will be parsed as 12.34 when ValueInputOption was USER_ENTERED.

[davidhbrown]

Thank you very much; that was a very helpful clue! The answer for me was the second option, converting the values to strings (or character arrays).

I tried changing the following code where the data is appended to the sheet:

        // Append values to the spreadsheet
        bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */);

to pass "RAW" as the fifth parameter:

        // Append values to the spreadsheet
        bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */, "RAW");

but still got 0s. Maybe I did that wrong? Anyway I reverted that and tried the string approach which is working and gets the job done:

        char floatCharBuffer[10]; // a few extra bytes to guard against overflow from data glitches?

        valueRange.add("majorDimension", "COLUMNS");
        valueRange.set("values/[0]/[0]", epochTime);
        dtostrf(((double) rtc.temp())/100.0, 6, 2, floatCharBuffer);
        valueRange.set("values/[1]/[0]", floatCharBuffer);
        dtostrf(temperatureC, 6, 2, floatCharBuffer);
        valueRange.set("values/[2]/[0]", floatCharBuffer);
        valueRange.set("values/[3]/[0]", "12.34"); // just to test

(including the code for others who, like me, had to look up how to convert a double to a char array in Arduino)

[andreavalle]

I'm curious about which device you're using. I have tried the very same code (similar to yours) on a ESP32-C3 and ESP32-S2. It works nicely and as expected (values as numbers with decimals) on the S2, but on the C3 I only get '0' for floating points.
I've opened a new bug because I think the library has some issues with the serialization of floating point numbers on the ESP32-C3 which uses software emulated floating points, while the S2 has hardware FPU.
@mobizt

[davidhbrown]

I was/am using the ESP32-C3, embedded in Seeed Studios' XIAO C3.

[andreavalle]

Ok then it proves my assumption, that the problem is due to floating point value processing within the library.
I have worked around the issue by converting FP values to strings and appending them with ValueInputOption set as USER_ENTERED.