Large Decimal Numbers and Exponential Notation for Google Spreadsheet

15 Oct 2021, 09:14

report / GasTips / spreadsheet

In this report, it has investigated the large decimal numbers and the exponential notation for Google Spreadsheet. When the large decimal numbers are put to the Spreadsheet, the Spreadsheet automatically sets the display value using the exponential notation. In this report, the result when the values are retrieved by Spreadsheet service and Sheets API is shown.

Sample script

At first, please create new Spreadsheet and open the script editor. And please copy and paste the following script. And, please enable Sheets API at Advanced Google services.

function myFunction() {
  const sheetName = "Sheet1";

  // Set values to Spreadsheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const maxDigit = 30;
  const values = Array(maxDigit)
    .fill("")
    .map((_, i) => {
      const v = 1 + Array(i).fill(0).join("");
      return [v, v.length];
    });
  const range = sheet
    .getRange(2, 1, values.length, values[0].length)
    .setValues(values);
  SpreadsheetApp.flush();

  // Retrieve the values using Spreadsheet service and Sheets API.
  const valuesFromSpreadsheetService1 = range.getValues();
  const valuesFromSpreadsheetService2 = range.getDisplayValues();
  const valuesFromSheetsAPI1 = Sheets.Spreadsheets.Values.get(
    ss.getId(),
    `${sheetName}!${range.getA1Notation()}`
  ).values;
  const valuesFromSheetsAPI2 = Sheets.Spreadsheets.Values.get(
    ss.getId(),
    `${sheetName}!${range.getA1Notation()}`,
    { valueRenderOption: "UNFORMATTED_VALUE" }
  ).values;

  // Show the result.
  console.log(valuesFromSpreadsheetService1);
  console.log(valuesFromSpreadsheetService2);
  console.log(valuesFromSheetsAPI1);
  console.log(valuesFromSheetsAPI2);
}

When this script is run, the following values are put to the sheet “Sheet1”.

Large Decimal Numbers and Exponential Notation for Google Spreadsheet

And, the put values are retrieved by Spreadsheet service and Sheets API.

Result

console.log(valuesFromSpreadsheetService1);

[ [ 1, 1 ],
[ 10, 2 ],
[ 100, 3 ],
[ 1000, 4 ],
[ 10000, 5 ],
[ 100000, 6 ],
[ 1000000, 7 ],
[ 10000000, 8 ],
[ 100000000, 9 ],
[ 1000000000, 10 ],
[ 10000000000, 11 ],
[ 100000000000, 12 ],
[ 1000000000000, 13 ],
[ 10000000000000, 14 ],
[ 100000000000000, 15 ],
[ 1000000000000000, 16 ],
[ 10000000000000000, 17 ],
[ 100000000000000000, 18 ],
[ 1000000000000000000, 19 ],
[ 10000000000000000000, 20 ],
[ 100000000000000000000, 21 ],
[ 1e+21, 22 ],
[ 1e+22, 23 ],
[ 1e+23, 24 ],
[ 1e+24, 25 ],
[ 1e+25, 26 ],
[ 1e+26, 27 ],
[ 1e+27, 28 ],
[ 1e+28, 29 ],
[ 1e+29, 30 ] ]

console.log(valuesFromSpreadsheetService2);

[ [ '1', '1' ],
[ '10', '2' ],
[ '100', '3' ],
[ '1000', '4' ],
[ '10000', '5' ],
[ '100000', '6' ],
[ '1000000', '7' ],
[ '10000000', '8' ],
[ '100000000', '9' ],
[ '1000000000', '10' ],
[ '10000000000', '11' ],
[ '100000000000', '12' ],
[ '1000000000000', '13' ],
[ '10000000000000', '14' ],
[ '100000000000000', '15' ],
[ '1E+15', '16' ],
[ '1E+16', '17' ],
[ '1E+17', '18' ],
[ '1E+18', '19' ],
[ '1E+19', '20' ],
[ '1E+20', '21' ],
[ '1E+21', '22' ],
[ '1E+22', '23' ],
[ '1E+23', '24' ],
[ '1E+24', '25' ],
[ '1E+25', '26' ],
[ '1E+26', '27' ],
[ '1E+27', '28' ],
[ '1E+28', '29' ],
[ '1E+29', '30' ] ]

console.log(valuesFromSheetsAPI1);

[ [ '1', '1' ],
[ '10', '2' ],
[ '100', '3' ],
[ '1000', '4' ],
[ '10000', '5' ],
[ '100000', '6' ],
[ '1000000', '7' ],
[ '10000000', '8' ],
[ '100000000', '9' ],
[ '1000000000', '10' ],
[ '10000000000', '11' ],
[ '100000000000', '12' ],
[ '1000000000000', '13' ],
[ '10000000000000', '14' ],
[ '100000000000000', '15' ],
[ '1E+15', '16' ],
[ '1E+16', '17' ],
[ '1E+17', '18' ],
[ '1E+18', '19' ],
[ '1E+19', '20' ],
[ '1E+20', '21' ],
[ '1E+21', '22' ],
[ '1E+22', '23' ],
[ '1E+23', '24' ],
[ '1E+24', '25' ],
[ '1E+25', '26' ],
[ '1E+26', '27' ],
[ '1E+27', '28' ],
[ '1E+28', '29' ],
[ '1E+29', '30' ] ]

console.log(valuesFromSheetsAPI2);

[ [ 1, 1 ],
[ 10, 2 ],
[ 100, 3 ],
[ 1000, 4 ],
[ 10000, 5 ],
[ 100000, 6 ],
[ 1000000, 7 ],
[ 10000000, 8 ],
[ 100000000, 9 ],
[ 1000000000, 10 ],
[ 10000000000, 11 ],
[ 100000000000, 12 ],
[ 1000000000000, 13 ],
[ 10000000000000, 14 ],
[ 100000000000000, 15 ],
[ 1000000000000000, 16 ],
[ 10000000000000000, 17 ],
[ 100000000000000000, 18 ],
[ 1000000000000000000, 19 ],
[ 10000000000000000000, 20 ],
[ 100000000000000000000, 21 ],
[ 1e+21, 22 ],
[ 1e+22, 23 ],
[ 1e+23, 24 ],
[ 1e+24, 25 ],
[ 1e+25, 26 ],
[ 1e+26, 27 ],
[ 1e+27, 28 ],
[ 1e+28, 29 ],
[ 1e+29, 30 ] ]

Summary

From the retrieved values, the following results were obtained.

Values retrieved by getDisplayValues are the same with the method of spreadsheets.values.get in Sheets API with the default option.
- The number of 15 digits can be retrieved as the raw number.
- At Sheets API, the methods spreadsheets.values.get, spreadsheets.values.batchGet and spreadsheets.get are the same result.
Values retrieved by getValues are the same with the method of spreadsheets.values.get in Sheets API with the option of valueRenderOption: "UNFORMATTED_VALUE".
- The number of 21 digits can be retrieved as the raw number.
- In this case, the option of valueRenderOption: "UNFORMATTED_VALUE" can be used for the methods of spreadsheets.values.get and spreadsheets.values.batchGet. But the method of spreadsheets.get has no option. So in this case, the method of spreadsheets.get cannot retrieve the large decimal numbers.
- Also, the result of getValues is the same with the method of getSheetValues.

Large Decimal Numbers and Exponential Notation for Google Spreadsheet

Sample script

Result

Summary

Share!