CODEWITHSUNDEEP
google-apps-scriptnumberscurrency
Chad Valentine
Chad Valentine

Reputation: 37

formatting numbers to currencies using setNumberFormat

I know iin apps script that if I have a column with a small number in euro format like 3 or 3,45 I can use

 var column = sheet3.getRange("J2:J")
 column.setNumberFormat("[$€]#,##0.00");

to change the number into €3.00 or €3.45

But I have no idea how I can change larger numbers like 1.995,95€ (the european way of writing it) into €1,999.95 (the right way!)

This is at least partly because I don't really understand what's going on here: setNumberFormat("[$€]#,##0.00"), I just use it.

I've looked all over google and youtube but I can't find anything about this so I'm really hoping someone can explain how it works to me and also explain how to do the Euro change above.

Please help!

Upvotes: 0

Views: 3665

Answers (1)

SputnikDrunk2
SputnikDrunk2

Reputation: 4038

Recommendation:

You would need to clean the numbers before you can format them to your desired currency. With that being said, you may refer to this sample code below to clean the numbers on your sheet (J2:J), then re-post the numbers back to your sheet on Column J, and then format them:

Code:

 function formatNumbers() {
  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = sheet3.getRange("J2:J");
  var num = [];
  var count = 0;
  column.getValues().forEach(res => {//clean the numbers from 'J2:J' removes any uncessary characters and leave only number with commas and dots
    if(res != ''){
      num[count++] = res.toString().replace(/[^\d.,-]/g,'').replace(',','.');
    }
  });
  for(var x=0; x<num.length;x++){//check if there are any numbers with 3 sections, such as [1].[995],[95]
    var cont = num[x].split('.');
    if(cont.length>=3){
      var newNum = cont[0]+','+cont[1]+'.'+cont[2]; // corrects the order of comma and dot on the number
      num[x] = newNum;
    }
    sheet3.getRange(2+x,10).setValue(num[x]); //getRange(2,10) is equivalent to 'J2:J'. 2 is row of column 10 (or column J)
  }
  sheet3.getRange("J2:J").setNumberFormat("[$€]#,##0.00");
}

Here's a sample sheet:

enter image description here

After running the code, this will be the result:

enter image description here

Additional Info:

As per the notation #,##0.00, you can refer to the Number format examples for more information. Basically, you're using the #,##0.00 notation because you wanted to make sure the first section of the number (#,##) is in decimal format and then the last section, you use 0.00 because you want to include whole numbers with .00 (e.g. 3 to 3.00).

Upvotes: 1

Related Questions