1

In apps script I want to obtain formatted 'number' strings. The input is an unformatted number. With an earlier answer posted by @slandau, I thought I had found a solution by modifying his code (see code snippet). It works in codepen, but not when I am using apps script. 1. Does anyone know what went wrong here? 2. I noticed this code works except when entering a number ending in .0, in that case the return value is also .0 but should be .00. I would like some help fixing that too. Thanks!

I have tried to look for type coercion issues, but wasn't able to get it down. I am fairly new to coding.

function commaFormatted(amount)
{
    var delimiter = ","; // replace comma if desired
    var a = amount.split('.', 2);
    var preD = a[1]/(Math.pow(10,a[1].length-2));
    var d = Math.round(preD);
    var i = parseInt(a[0]);
    if(isNaN(i)) { return ''; }
    var minus = '';
    if(i < 0) { minus = '-'; }
    i = Math.abs(i);
    var n = new String(i);
    var a = [];
    while(n.length > 3)
    {
        var nn = n.substr(n.length-3);
        a.unshift(nn);
        n = n.substr(0,n.length-3);
    }
    if(n.length > 0) { a.unshift(n); }
    n = a.join(delimiter);
    if(d.length < 1) { amount = n; }
    else { amount = n + '.' + d; }
    amount = minus + amount;
    return amount;
}

console.log(commaFormatted('100000.3532'))

The expected result would be 100,000.35. I am getting this in the IDE of codepen, but in GAS IDE is stops at the .split() method => not a function. When converting var a to a string = I am not getting ["100000", "3532"] when logging var a. Instead I am getting 100000 and was expecting 3532.

JP Douma
  • 133
  • 1
  • 8

1 Answers1

1

Based on this answer, your function can be rewritten to

function commaFormatted(amount)
{
  var inputAmount;
  if (typeof(amount) == 'string') {
    inputAmount = amount;
  } else if (typeof(amount) == 'float') {
    inputAmount = amount.toString();
  }
  //--- we expect the input amount is a String
  //    to make is easier, round the decimal part first
  var roundedAmount = parseFloat(amount).toFixed(2);
  //--- now split it and add the commas
  var parts = roundedAmount.split(".");
  parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, ",");
  return parts.join(".");
}

console.log(commaFormatted(100000.3532));
console.log(commaFormatted('1234567.3532'));
PeterT
  • 8,232
  • 1
  • 17
  • 38
  • Thank you very much @PeterT. Regular expressions are on my list to learn. How would I fix the last part this way? I want to round the part after the dot e.g. .3532 turns .35 or .3554 rounds to .36 and a special case .0 shows up as .00? – JP Douma Jan 22 '19 at 21:59
  • Second observation. When running the script in Google Apps Script the same error message appears: _TypeError: Cannot find function split in object 1260. (line 449, file "Code")_. This is probably because the argument is a number that needs to be converted to a String. – JP Douma Jan 22 '19 at 22:17
  • I've updated the answer to show you an example to round the number. – PeterT Jan 22 '19 at 22:24
  • Thanks! Let me quickly run it in the Apps Sheet editor. – JP Douma Jan 22 '19 at 22:26
  • Now I've made another update that checks the type of the input parameter to adjust the logic to consistently convert in the incoming value. – PeterT Jan 22 '19 at 22:29
  • Yes, that is more robust. Thanks, I have to pay more attention to these things (still learning). At first glance it seems to work. I am puzzled though why my log does not show something similar I would see with console.log when running it in Codepen i.e. ["100000", "3532"] instead with Logger.log I am just seeing [100000]. I am quickly going to calculate the results by hand and figure out what may happen. Can I get back to you after that? – JP Douma Jan 22 '19 at 22:37
  • Peter, thanks a lot it all works beautifully. I am still not clear about the log. When running a few numbers I am getting this:[19-01-23 05:47:30:607 ICT] [66671, 98] [19-01-23 05:47:30:607 ICT] [711198, 36] [19-01-23 05:47:30:608 ICT] [345, 00] [19-01-23 05:47:30:609 ICT] [66, 46] [19-01-23 05:47:30:609 ICT] [133, 32] - I guess, that one is for another day. Like they say Apps Script is **like** JavaScript but not quite the same :-). – JP Douma Jan 22 '19 at 22:51
  • If I change the code above by replacing `console.log` with `Logger.log`, I get the exact same results as the above snippet, the results are `100,000.35` and `1,234,567.35`. – PeterT Jan 23 '19 at 21:46
  • Really? Now you have triggered me to reproduce that as well. It has been bugging me for a day now. I'd like to compare what I have with your code in the Apps Script IDE. Could you maybe share a screenshot? Or any other means so I can compare what is not going right on my end? – JP Douma Jan 23 '19 at 21:53
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/187204/discussion-between-jp-douma-and-petert). – JP Douma Jan 23 '19 at 21:58