How to get raw numbers from Google Sheets API

user_240742 0
Viewed 499

I have a simple function that gets the data from a google sheet.

for example https://sheets.googleapis.com/v4/spreadsheets/{MYSHEET}/values/Sheet1!A1:D5

I have a field there with long numbers, that google automatically displays in scientific notation, such as 1.234E+8.

I don't mind how its displayed in Sheets, as its never used directly. But in my API call I must be able to get the original/raw number.

How is this accomplished?

PS. Changing the format in the sheets ui might help, but this has 2 issues:

  1. It will require double and triple work for each field in each sheet, instead of fixing in the code that gets the data.
  2. I tried it for testing, but it seems to ruin existing data, by changing also the raw "data-behind". Instead of only the display value. So I can't go that route anyway.

Update

I posted a sample sheet. This is the full URL I'm using to download:

https://sheets.googleapis.com/v4/spreadsheets/1pSQ4W0GV85Z-6QrUx0UxVA3VCSHE3x93BT3c-R6mHZk/values/Form Responses 1!A1:AZ1000?valueRenderOption=UNFORMATTED_VALUE

but the returned json is wrong, as follows:

{
  "range": "'Form Responses 1'!A1:F103",
  "majorDimension": "ROWS",
  "values": [
    [
      "cc"
    ],
    [
      4.3668413379866e+15
    ],
    [
      4.14809933325943e+15
    ]
  ]
}
google-sheets-api
1 Answers

From your endpoint, I thought that you might be using the method of spreadsheets.values.get in Sheets API. If my understanding is correct, in your situation, how about using a query parameter of valueRenderOption? When your endpoint is modified, it becomes as follows.

Modified endpoint:

https://sheets.googleapis.com/v4/spreadsheets/{MYSHEET}/values/Sheet1!A1:D5?valueRenderOption=UNFORMATTED_VALUE
  • In this modification, the query parameter of valueRenderOption=UNFORMATTED_VALUE is added.

Reference:

Added:

When I saw your updated question, it seems that your values are 16 digits. At Sheets API, 15 digits can be seen as the decimal number. When the number is larger than 15 digits like 1000000000000000, the value of 1e+15 is retrieved. When I tested this with the methods of spreadsheets.values.get , spreadsheets.values.batchGet and spreadsheets.get, the same result is obtained. When I saw your Spreadsheet, 4.3668413379866e+15 and 4.14809933325943e+15 are retrieved.

Fortunately, when I tested this using Spreadsheet service of Google Apps Script, I noticed that the values of 4366841337986600 and 4148099333259430 can be retreieved. So as a workaround, I would like to propose the following flow.

Usage:

1. Create a new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access https://script.new/. In this case, if you are not logged in to Google, the log-in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Sample script.

Please copy and paste the following script to the created Google Apps Script project and save it. This script is used for Web Apps.

function doGet(e) {
  const {id, range} = e.parameter;
  if (!id || !range) return ContentService.createTextOutput("No spreadsheet ID or range.");
  const values = SpreadsheetApp.openById(id).getRange(range).getValues();
  return ContentService.createTextOutput(JSON.stringify(values));
}

3. Deploy Web Apps.

The detailed information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
  2. Please click "Select type" -> "Web App".
  3. Please input the information about the Web App in the fields under "Deployment configuration".
  4. Please select "Me" for "Execute as".
    • This is the importance of this workaround.
  5. Please select "Anyone" for "Who has access".
    • In this case, the user is not required to use the access token. So please use this as a test case.
    • Of course, you can also access to your Web Apps using the access token. Please check this report.
  6. Please click "Deploy" button.
  7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

4. Testing.

In order to test this Web Apps, please access to the following endpoint by replacing it with your Web Apps URL.

https://script.google.com/macros/s/###/exec?id=1pSQ4W0GV85Z-6QrUx0UxVA3VCSHE3x93BT3c-R6mHZk&range=Form%20Responses%201!A2:A3

When this endpoint is accessed by the browser, the following result is obtained.

[
  [4366841337986600],
  [4148099333259430]
]

Note:

  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
  • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
  • My proposed script is a simple script. So please modify it for your actual situation.

References:

user_71086530
Related