Google Sheet API 4: How to return cell string value with leading single quotes as-is?

Question

Having a cell in Google Sheets with a value of '0123 and fetching it via the API it returns 0123 and omits the leading single quote.

Tried to use any of the available ValueRenderOption but always returns the cell value without single quote.

I would expect to get the single quote using the UNFORMATTED_VALUE or FORMULA value, but this is not the case.

Therefore: How to get the raw cell value, including leading single quotes?

Answer 1

I think this would be an expected behavior given the fact that the feature just makes the system recognize the values after the apostrophe as text, so it is more like the cell formatting feature rather than an actual formula but without changing the format of the whole cell and just the values next to the apostrophe.

After making some testing in the API explorer from the Google Sheets API I found out that if you remove the apostrophe the API will definitely recognize the values as numbers when using either UNFORMATTED_VALUE or FORMULA in the ValueRenderOption parameter, so the API will return number values in the response, however if you do it using '0123, what you get in the response instead of numbers is text (see screenshots below).

Number value

Text value

With all this we can determine that this would be expected and that adding the apostrophe symbol is an exception to the ValueRenderOption parameter due to how the API works. I have also submitted feedback in the documentation page to see of Google can add this information to the documentation since there is no mention of this in that page.

References:

• Using Apostrophe to Add Leading Zero
• Format numbers in a spreadsheet