How to make a function format data within its own cell?

Question

I want a custom function that can auto format data units. Turning 1000 m to 1 km. Put simply I cannot get the function to format the cell it is located in.

I plan on adding conditions later to format the number depending on the input value. The Goal it to take advantage of the custom formatting properties such as the format '0.0,,"M"' which returns 1230000 as 1.2M but still holds the 1230000 data. This properties would be much more useful if you didn't have to manually change the format every time you want a different format.

I also don't want to have to manually change the code to adjust to different ranges.

Here is the simplified function that I want to get to work. Any Advice?

function NEWFORMAT(input) {
 var sheet = SpreadsheetApp.getActiveSheet();
 var cell = sheet.getCurrentCell();
 cell.setNumberFormat('#,##0,, "MV"');
 return input;
}

• Gyul: Why not use =(A1/1000)& " Km" since you are planning to use a custom formula to another column anyway?

Answer 1

A custom function cannot use "set" methods such as Range.setNumberFormat().

Try an onEdit(e) simple trigger instead, like this:

function onEdit(e) {
 if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window. It runs automatically when you hand edit the spreadsheet. See https://stackoverflow.com/a/63851123/13045193.');
 if (!Number(e.value)) return;
 e.range.setNumberFormat('#,##0,, "MV"');
}

• He Ca: It doesn't work yet. It's not returning any number.
• doubleunary: The code formats numbers as you enter them in spreadsheet cells. The underlying value remains the same. A simple trigger is not supposed to return anything.