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?

  • 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. – contributorpw Mar 22 at 14:17
  • Maybe this is a question better fit for stack overflow, then? It involves a lot of code... – Jaden Ranzenberger Mar 22 at 14:23
  • pls share a copy of your sheet and include "particular function" in your question – user0 Mar 22 at 14:24
  • 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 at 14:28
  • @JadenRanzenberger pls do so... – user0 Mar 22 at 14:33
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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