Format currency based on condition

问题:

New to Google Apps Script, but have some VBA coding experience.

I am looking to be able to have the numeric format of a cell decided based on a different cell input.

eg.

• Col A - Client Name (Validated range)
• Col B - Currency Type (GBP, USD, EUR - validated list)
• Col C - Fee (100 - free type by user)

Based on the selection in Col B it needs to put the format in col C as either:

£100.00 or
$100.00 or
€100.00.

This should be done in such a way that the "autosum" function displayed on the bottom of google sheet is still able to add the values, or if using a sumif function will result in the USD, GBP and EUR totals being correct. I.E. it cannot be a text based solution or at least not one where this is not the result.

回答1:

Try using onEdit trigger, and set custom number format.

Here's code sampe for currency formats:

function customNumberFormat() {

  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getSheetByName('Sheet1');

  var range1 = ss.getRange("C2");
  var range2 = ss.getRange("C3");
  var range3 = ss.getRange("C4");

  var format1 = "£ 00.00"
  var format2 = "$ 00.00"
  var format3 = "€ 00.00"

  range1.setNumberFormat(format1);
  range2.setNumberFormat(format2);
  range3.setNumberFormat(format3);

}

Make script to check current entry in column with sum and format it in proper currency.

---

But text solution with formula looks better for me:

This way you are hiding the entries in column with sum, and show text with format. Sums can be used in further formulas.

There's also simple ArrayFormula: =ArrayFormula(TEXT(C2,VLOOKUP(OFFSET(B2,,,COUNTA(B2:B)),H:I,2,0)&" 0.00"))

If you paste it in D2, it'll expand automatically.