Currency format in google apps script-CodePudding

I have created some set of rows for a requirement gathering document in which for a range I have set numberformat for USD as

ex_range.setNumberFormat("[$$]#");

upon execution, in the range when the user enters the value, the formatting rounds it to a whole number.

I tried

ex_range.setNumberFormat("[$$]#,##0.0000");

and for the above, when the user enters a value say e.g. 34.56 I get $34.5600

I want the user to enter the value and whatever decimal points they enter to not round off or add extra zeros.

If the user enters 50.67, I should get $50.67 and if 45.5670 is entered I should get $45.5670

Please help in how to set the format to USD for the range and no round up happens to the values.

I am having the user fill value in the range K43:P57, they should be able to enter any value with any decimal number, the value should not get rounded off.

and the script should read the range K43:P57and identify the value with highest decimal numbers and populate in G43

CodePudding user response：

With an onEdit(e) function you can set the number format for the edited cell. This will override the column formatting. Be aware that the 0 at the end of 5.989830 will be "cut off" before sending it to the script. So the value of n inside the script below will be 5.98983 like @Tanaike is mentioning in the comments. Nevertheless i hope this will help:

EDIT: If the original column formatting is Plain text the solution below will work with tailing zero's

Extensions -> Apps script.
Paste the code.
Change the sheetname and column number to the values where theformatting needs to happen.
Save an quit.
Test it out.

function onEdit(e) {
  //Settings
  const sheetname = "BB";
  const column = 1;

  //Rest of script
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const n = e.value;

  if (isNaN(n)) return;
  if (sheet.getName() !== sheetname) return;
  if (range.getColumn() !== column) return;

  const length = n.toString().split(".")[1].length;
  const zeros = new Array(length).fill("0").join("");
  const format = "#,##0."   zeros;

  range.setNumberFormat(format);
}