0

I have a sheet that takes in an encoded string (Base64), decodes it, and displays it in a readable format. I have just recently come across an issue where it does not properly recognize a number, but only sometimes. The issue appears to be very inconsistent, and I have not been able to pin down why it is happening. I have researched this extensively at this point, but have come to no resolution.

Below are some examples of what is happening.

This is an example of a working situation. The number boxed in red is the cell (and only cell) that has been giving me issues. In this example, it is correctly recognized as a number, and treated as such. That number is a result of a split formula being performed on cell F6 (the one with the visible header "cookie").

Working example

This is an example of a broken situation. The number boxed in red is the same cell as the previous image, but parsed from a different inputted Base64 string. This cell is not being treated as a number, but for no apparent reason.

not working

Below is the data in cell F6 for the first and second images above, respectively. This string is split by semicolon. The problem is caused by the second number in the string. Arrows (>><<) have been added by me in order to clarify the position of these numbers.

  • Works: 159149865299613.8;>>5793819823521370<<;10338;125;267962864851035.03;3488;0;0;0;0;0;0;66;52081;0;125;0;0;0;0;0;0;;0;0;0;0;0;0;0;-1;-1;-1;-1;-1;0;0;0;0;50;0;0;1;20;1655298213526;0;0;;41;0;0;37459253051.48866;50;

  • Does not work: 1705503446625.7646;>>7977823485117074<<;10364;130;268041654659599.25;3733;0;0;0;0;0;0;0;-1;0;130;0;0;0;0;0;0;;0;0;0;0;0;0;0;-1;-1;-1;-1;-1;0;0;0;0;50;0;0;2;21;1655384614318;0;0;;41;0;0;48530698632.17037;50;

Every other number in this sheet is recognized as a number and functions properly.

Below is a link to view this exact spreadsheet. Upon making a copy of the sheet, you can view the different values by copying the Save Code from the SaveData sheet (column A) and pasting them in the box in the Main sheet (make sure to paste values only, and not formatting).

This Sheet

There are a few App Scripts running in this sheet, and while I don't believe those are the issue since it's breaking before they are even run, I included the tag anyway just in case there is something simple I have missed.
TheMaster
  • 45,448
  • 6
  • 62
  • 85
kaitlynmm569
  • 1,605
  • 1
  • 6
  • 18
  • Have you looked at the number formatting at the cells that cause you issues? – ziganotschka Jun 17 '22 at 13:37
  • @ziganotschka yes, I have checked and double checked (and checked again). The problem cell is set to format as a number, and even when that formatting is reapplied, it changes nothing – kaitlynmm569 Jun 17 '22 at 13:40
  • 1
    Curious, I took the first number from the OP and cut and paste value into a spreadsheet cell without any formatting. It came in as `5.79382E+15`. The same for the second number and it came in as `7977823485117074`. Using `console.log( typeof value)`, the first is number and the second is string. Divide the second by 2 gives `3.98891E+15` so it can still be manipulated as a number. And it can be formatted as Number. – TheWizEd Jun 17 '22 at 17:01

1 Answers1

1

IMHO Google Sheets is not intended to be able to handle any number (as number).

According to Austin's answer to another question, Google Sheets can handle as number "up to 15 digits, scale from +308 to -308".

Austin answer includes a link to this other answer which explains the "precision" and "scale" concepts necessary to understand the above quote. Putting aside the concepts and terminology...

  • 5793819823521370 and 7977823485117074 have 16 digits, but the last one is not the same. This is relevant because it affects if the value is handled by Google Sheets as text or as a number.
  • If the last digit of 5793819823521370, 0, is replaced by any other digit, it also is handled as a text value.
  • If the last digit of 7977823485117074, 4, is replaced by 0, it is handled as a number.
  • If we adding more 0's to the right of 5793819823521370 and 7977823485117070 (up to certain quantity) will not make the value to be handled as text instead of a number, but the value will be displayed in the formula bar using engineering notation, i.e. 5.79381982352137E+23.

NOTE: Unfortunately the official documentation doesn't include many details about it's capabilities / limitations but there are several posts on the wild, like Austin's answer referred above, about them, but it's a bit tricky to find them. Spreadsheet might be helpful to learn about the history of the electronic spreadsheet and other stuff that could help you to improve your "Google Fu" around spreadsheets in general that might be helpful to find specific stuff about Google Sheets.

Conclusion

If you will keep using Google Sheets to store numbers having more than 15 digits, prepare your script to convert these values to a number i.e. by using Number(value) or parseInt(value).

Rubén
  • 34,714
  • 9
  • 70
  • 166