Does anyone know how to change in Google sheet the currency per range in multiple sheets using Apps Script?

I have a script (from @doubleunary):

function onEdit(e) {
  const settings = {
    rangesA1: ['B3:B5', 'C6:C8',],
    dollar: '$',
    euro: '€',
  };
  let sheet;
  if (!e || !e.value) {
    return;
  }
  const currency = settings[e.value.toLowerCase()];
  if (!currency
    || e.range.getA1Notation() !== 'A3'
    || (sheet = e.range.getSheet()).getName() !== 'Sheet1') {
    return;
  }
  const format = `${currency}#,###.00`;
  sheet.getRangeList(settings.rangesA1).setNumberFormat(format);
}

I made an example to explain this script (see screenshot).

enter image description here

In this script decides the value in A3 (dropdown list) what the currency is in B3:B5 and C6:C8. (B7 and C5 stays numbers).

This is for one sheet but I want to work with multiple sheets.

I want to change the script to this:

The value in A3 on Sheet1 decides what the currency should be for all the sheets. For example on Sheet2 the range is B3:B5 and C6:C8 and on Sheet3 the range is D1:D5 and E6:E8.

CodePudding user response：

I believe your goal is as folows.

• By modifying your showing script, when the dropdown list of the cell "A3" of "Sheet1" is changed, you want to change the number formats of "Sheet1", "Sheet2" and "Sheet3".

In this case, how about the following modification?

Modified script:

function onEdit(e) {
  const settings = {
    // rangesA1: ['B3:B5', 'C6:C8',], // In this modification, this is not used.
    dollar: '$',
    euro: '€',
  };
  let sheet;
  if (!e || !e.value) {
    return;
  }
  const currency = settings[e.value.toLowerCase()];
  if (!currency
    || e.range.getA1Notation() !== 'A3'
    || (sheet = e.range.getSheet()).getName() !== 'Sheet1') {
    return;
  }
  const format = `${currency}#,###.00`;

  // I modified the below script.
  const sheets = [{ s: "Sheet1", r: ['B3:B5', 'C6:C8'] }, { s: "Sheet2", r: ["B3:B5", "C6:C8"] }, { s: "Sheet3", r: ["D1:D5", "E6:E8"] }];
  sheets.forEach(({ s, r }, i) => {
    if (i > 0) {
      sheet = e.source.getSheetByName(s);
    }
    sheet.getRangeList(r).setNumberFormat(format);
  });
}

• In this modified script, when the dropdown list of cell "A3" of "Sheet1" is changed, the number format of 'B3:B5', 'C6:C8' of "Sheet1", "B3:B5", "C6:C8" of "Sheet2" and "D1:D5", "E6:E8" of "Sheet3" is changed.

• When you want to modify the ranges and sheets, please modify sheets.