In my google sheets I have a cell(=G13) where I decided what currency should be used.

Cell G13

If I choose 'Euro' then my currency changes to '€' (Apps script)

It is easy to do with currencies with symbols but special currencies don't work.

For example: Albanian Lek: Lek1,000 Algerian Dinar: din1,000.12

Also currencies where the symbol is at the end of the amount is a problem in my script.

For example: Belarusian Ruble: 1,000 p. Danish Krone: 1,000.12 kr.

This is a part of my script:

function onEdit(e) {
  const settings = {
    'us dollar': '$',
     euro: '€'
  };
  let sheet;
  if (!e || !e.value) {
    return;
  }
  const currency = settings[e.value.toLowerCase()];
  if (!currency
    || e.range.getA1Notation() !== 'G13'
    || (sheet = e.range.getSheet()).getName() !== 'Start') {
    return;
  }
  const format = ${currency}#,###0.00;

Does somebody know how I can fix this?

CodePudding user response：

In your situation, how about declaring the number format in settings object? When this is reflected in your script, how about the following modification?

Modified script:

function onEdit(e) {
  const base = "#,###0.00";
  const settings = { // Please set this for your actual situation.
    'us dollar': `$${base}`,
    'euro': `€${base}`,
    'albanian lek': `"Lek"${base}`,
    'algerian dinar': `"din"${base}`,
    'belarusian ruble': `${base}"p"`,
    'danish krone': `${base}"kr"`,
  };

  let sheet;
  if (!e || !e.value) {
    return;
  }
  const currency = settings[e.value.toLowerCase()];
  if (!currency
    || e.range.getA1Notation() !== 'G13'
    || (sheet = e.range.getSheet()).getName() !== 'Start') {
    return;
  }
  const format = currency; // here, "currency" can be directly used as format.


  // do something.


}

• This is a sample modification. So, please modify settings for your actual situation.

Testing:

From your question, please add the values of Albanian Lek,Algerian Dinar,Belarusian Ruble,Danish Krone to the dropdown list of the cell "G13". When you use the above-modified script, when you select Albanian Lek, "Lek"${base} is used as the number format. And, when you select Belarusian Ruble, ${base}"p" as the number format.