Google Sheets Scripts Number Decimal Place Fomatting

user_2058190 0
Viewed 12241

Using Google Sheets Script Editor, I am trying to create a custom function that can help me format a number string with a certain amount of decimal places. I need the string to be a number type so that the data can be charted.

The reason I need this function is because I have a in-cell dropdown that triggers the displays different sets of numbers with different decimal places within a cell. Therefore, I cannot rely on the static number formatting settings within Sheets.

While this returns the number of decimal places I want...

function format(amt) { return amt.toFixed(2); }

toFixed() creates a text string, therefore the value cannot be used for charting (the chart will say "No Data").

I have tried the following alternatives...

return parseFloat(amt); // var = number; can chart but cannot control decimal places
return parseFloat(amt).toFixed(2); //var = string; cannot chart
return parseInt(amt).toFixed(2); //var = string; cannot chart

Can anyone let me know if there is a way to create a custom function that lets me control the number of decimal places I want while keepign it as a number string?

google-apps-script google-sheets javascript
1 Answers
function format(x, n){
    x = parseFloat(x);
    n = n || 2;
    return parseFloat(x.toFixed(n))
}

Basically you need to parse the value after the toFixed() method is called, but you're parsing it before the toFixed() method is called.

With the above function you can say format(amt) to get 2 decimal places, or format(2, n) to get n decimal places

user_83784190
Related