How do you use Accounting format in Google Script App?

[Stevan]

function onEdit(e){

    var sheetName = "Debt"; //name of sheet to adjust formatting on

    var currencyCol = 8; //column H

    var amountCol = 6; //column F

    var defaultFormat = "[$€]#,##0.00";

    var currencyFormat = {"USD":"[Red][$$]#,##0.00",

                          "GBP":"[Red][$£]#,##0.00",

                          "EUR":"[Red][$€]#,##0.00"};

                          

    var r = e.range;

    if(e.source.getSheetName()==sheetName && r.getColumn() == currencyCol){ //edits one cell at a time

      var uf = currencyFormat[r.getValue()];

      uf = uf?uf:defaultFormat;

      r.offset(0,amountCol-currencyCol).setNumberFormat(uf);

    }

}

In the function above, I am using column H to adjust the format of column F. I would like to adjust this format to an accounting format, but I am struggling to do so. Does anyone know how to convert this string, "USD":"[Red][$$]#,##0.00", to an accounting format? 

[Clark Lind]

Not sure what you mean by Accounting format (not an accountant.. lol), do you have an example?  Like:  1,234.56? or something else?

[Clark Lind]

One way to find out how to do it would be to type in some generic numbers in a sheet. Then record a macro (tools/macro) and change the cell formatting to the number format you desire. I had a spreadsheet opened and did just that, this is the resulting code (if it helps):

function UntitledMacro() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('BL17').activate();

  spreadsheet.getActiveRangeList().setNumberFormat('_("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"* "-"??_);_(@_)');

};

[CBM Services]

That is a great trick Clark which can apply to many other situations. Thanks for sharing.

---

From: Clark Lind
Sent: ‎2020-‎02-‎02 10:47 AM
To: Google Apps Script Community
Subject: [Apps-Script] Re: How do you use Accounting format in Google ScriptApp?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6c78c6ad-f322-4393-8977-819c45d56be2%40googlegroups.com.

[Clark Lind]

Thanks George. I do it when I just can't figure something out. "Well, how would Google instruct a spreadsheet to do it?" lol  

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.