0

When a user enters a number with trailing zeros into Google Sheets, I want the number displayed in the sheet’s cell and the number retrieved via Apps Script to be identical to the number entered. The numbers being entered have a variable number of digits before and after the decimal, e.g. 1.00,11.00, 1, and 11.0. All need to be displayed and retrieved as entered and as numbers.

The I only way I know how to resolve this is by formatting the value as a string, and then converting it to a number in Apps Script before further processing. I’m trying to avoid formatting numbers as strings to begin with though. Unless I'm missing something, Google Sheets makes this impossible.

How can I keep the variable number of trailing zeroes both in the display of the number and when the number is retrieved via getValue() in Apps Script? Is there way to do this without formatting as string?

Format options that I’ve tried:

  • Automatic and ### formats will display entry 1.00 as 1.
  • ####0.00 and 0.00 formats will display entry 1 as 1.00.
  • Scientific formatting will display 1 as 1.00E+00.
  • Plain text format. I have =ISNUMBER(myRange) as a custom data validation formula so it will reject any entry made as plain text. I would rather not drop this validation as these entries must be further processed as numbers. Therefore, I want to ensure that they're numbers upon entry.

For @Kos:

This can be reproduced by executing any of the formatting options that I outlined above. I didn't realize that this needs to be spelled out:

  1. Open a Google Sheet.
  2. Click the Format menu.
  3. Hover over Number.
  4. Click Custom number format.
  5. Paste ####0.00 into the entry field.
  6. Click Apply.
  7. In the cell that you formatted, enter 1.0. (Will display as 1.00, which can be expected.)
  8. In Google Apps Script run the following:
function testFormat() {

let sheet =  SpreadsheetApp.openById(<your_sheet_id>).getSheetByName(<your_sheet_name>);
let formattedRange = sheet.getRange(<your_cell_row_number>,<your_cell_column_number>,1,1);

Logger.log(formattedRange.getValue())
}

The above will return 1.0 regardless of entering 1.0 or 1.00.

Marion
  • 101
  • 1
  • 10
  • What's steps to reproduce your issue? – Kos Dec 30 '21 at 17:48
  • Does this answer your question? [Difference between getValue() and getDisplayValue() on google app script](https://stackoverflow.com/questions/34691425/difference-between-getvalue-and-getdisplayvalue-on-google-app-script) – Kos Dec 30 '21 at 18:19
  • I think plain text will give you what you desire – Cooper Dec 30 '21 at 18:31
  • I mentioned that the entries need to be "displayed and retrieved ... as numbers," not text. @Kos I'm aware of the difference between these two already. The problem with `getDisplayValue()` is that it returns a string, not a number. I understand that I can convert this but it's not good practice to store/retrieve numbers as text to begin with. @Cooper Like I mentioned, I understand this can be handled with plain text. I'm interested to learn if I can handle this without using plain text as these values are numbers. – Marion Dec 30 '21 at 18:44
  • 1
    `The above will return 1.0 regardless of entering 1.0 or 1.00.` ok, and what do you need that for? Number is number, use it for calculations or whatever – Kos Dec 30 '21 at 18:54
  • Do you understand that number format is a string right? – Kos Dec 30 '21 at 18:55
  • Related: https://stackoverflow.com/questions/44533300/what-data-types-does-google-sheets-support https://stackoverflow.com/questions/57428683/unexpected-output-values-are-coming/57429354#57429354 – TheMaster Dec 31 '21 at 08:28

0 Answers0