search

Synthoid / ExportSheetData

Add-on for Google Sheets that allows sheets to be exported as JSON or XML.
MIT License
234 stars 46 forks source link

(Bug) Exporting power values of 2 #140

Closed sgaumin closed 3 years ago

sgaumin commented 3 years ago

Describe the bug Even if the display on the Google sheet is not precise, the export of power values of 2 are correct until 2^54 and become false after that point.

To Reproduce Steps to reproduce the behavior:

  1. List index from 0 to 63 on one column (called IndexCell)
  2. Create the formula "=Formula(2, IndexCell)"
  3. Export or Visualize data
  4. See error on index 55

Expected behavior Power values of 2 should be correct until 2^63.

Screenshots Screenshot1

Screenshot2

Synthoid commented 3 years ago

Interesting. ESD should be pulling the value straight from the sheet and I thought that E notation would translate as well. I wonder if this is a sheet data retrieval issue, a JavaScript number issue, or something else. I'll look into this. Good catch!

Synthoid commented 3 years ago

After running a few tests, I think this is a rounding issue with sheets. I duplicated your steps and got the same value. I manually entered values as well to be sure, but got the same result. Based on your screenshot and some other tests I ran, the expected value there is 36028797018964000 instead of 36028797018963970 correct?

I ran the exponential number through some E notation calculators to double check my work and despite some rounding weirdness it seemed like the 36028797018964000 value was the more standard result. I'm not sure if the Sheets POWER function is rounding more precisely or if it has a rounding error, but either way this seems to be related to Sheets, not ESD interpreting the value incorrectly. As such, I'm not really sure what can be done here unfortunately.

Screen Shot 2021-04-06 at 11 30 19 PM
sgaumin commented 3 years ago

Sorry for the late reply!

Actually the expected value of 2^55 is 36028797018963968 (compute with this calculator).

The google sheet is starting rounding values from 2^50 however ESD still is correct until 2^54. 69Pre4Ipf6

Based on that difference, the issue may comes from the javascript code itself.

This hypothesis seems to be confirmed by trying to compute power of 2 on this console, it was possible to reproduce the issue on 2^55. GAGbfuuJJP

After quick search I find this post which is interesting but without any solution

Synthoid commented 3 years ago

Now I'm the one with the late reply!

It's interesting that the JavaScript code itself is what causes the imprecision, but considering that it uses floating point numbers that's not too surprising at large numbers like 2^55. That does reinforce my concern that I can't really do much about it since ESD can only pull data from Sheets as is. The solution here seems to be writing a high precision calculator to evaluate function expressions instead of pulling in reported values from Sheets directly... Which is a bit of a lift.

Unfortunately, that means this is probably an issue that I can't fix, or at least would not get around to for quite some time since building a calculator is outside the scope of ESD's functionality. I'm going to close this issue for now but if a good solution is found later I'll reopen it and see what I can do.