0

I am using Sheets as a front end to view tons of different IDs and credentials associated with my authenticated access to a 3rd party API.

When calling this function,

I get this result:

1248531000000939219

When passed into Google Sheets, even when converting this number to a string result in the functions that produce the results beforehand, (and I can assure you that it is a plain text string,) I get this result in google sheets:

1248531000000939300

Not only is it interpreting the plain text as a number, but also rounding the number. Note that I have tried =TO_TEXT in the cell, =TEXT in the cell, and tried many different formatting options for numbers to at least not allow it to round it, but as I've gleaned from other sources, Google Sheets will only use up to 15 characters in numbers until it rounds.

Also, I had the ability to simply put the data into a cell as plain text from this exact same function before, but after clearing the content of these cells, they no longer produce the number as it appears in the result of this particular function. In other words, I know this is possible because I've done it before with the exact same inputs in the exact same sheet.

Nonetheless, I need 1 of two results to preserve the integrity of these IDs:

  • Sheets doesn't round this number

  • Sheets doesn't interpret plain text as a number

The second is much preferable to the first, but I will result in the first if necessary.

Any clue as to what's going on?

Improve this question
6
  • You need to create a simple example to prove. At the moment, nothing is clear. I can assure you that when I insert this text into a cell, everything works fine.
    – oshliaer
    Mar 22, 2019 at 14:17
  • Maybe this is a question better fit for stack overflow, then? It involves a lot of code...
    – Jaden Ranzenberger
    Mar 22, 2019 at 14:23
  • pls share a copy of your sheet and include "particular function" in your question
    – user0
    Mar 22, 2019 at 14:24
  • 1
    I will, however it's a very complex custom function that interfaces with an API. I can share that, when using the typeof javascript operator to determine the type of data that's returned from this function, it returns "string," meaning it is plain text. When it's inputted into a cell however, it doesn't interpret it as a string, despite my code returning a string value. I will put the full function and deep explanation if you determine this comment isn't useful enough.
    – Jaden Ranzenberger
    Mar 22, 2019 at 14:28
  • @JadenRanzenberger pls do so...
    – user0
    Mar 22, 2019 at 14:33

2 Answers 2

Reset to default
1

  • the upper limit for a number in Google Sheets is ~15 digits - everything after that gets rounded. you can test it with a simple formula:

    =TRUNC (RAND() * (9999999999999999999 - 1) + 1)

    and notice that Google Sheets will rewrite it to:

    =TRUNC (RAND() * (9999999999999990000 - 1) + 1)

    therefore your issue needs to be treated on script level before it comes to the sheet so it would return text string and avoid further rounding

Improve this answer
0

If you wish it to treat a string of numbers as text, this can be done by preceding the string of numbers with an apostrophe ('). You won't be able to use that number for future calculations, but if you are just keeping a record of a number or using it as the name of something, then that's your fix.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.