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

I have a script:

const SHEET_NAME = 'Sheet1';
const CUR_CELL = 'A3';
const FORMAT_COLUMN = 'B';
const CURS = {
  dollar: '$',
  euro: '€',
};

function onEdit(e) {
  let sheet = e.source.getActiveSheet();
  if (sheet.getName() !== SHEET_NAME
    || e.range.getA1Notation() !== CUR_CELL.toUpperCase()) return;
  let len = sheet.getLastRow() - 1;
  sheet.getRange(2, FORMAT_COLUMN.toUpperCase().charCodeAt() - 64, len)
    .setNumberFormats(new Array(len).fill().map(_ =>        [`${CURS[e.value.toLowerCase()]}#,###.00`]));
}

This script change the currency for a whole column. But for me, not every numbers in a column have to change to a currency. Also I want to change multiple columns so I only want to change a range.

I made an example to explain (see screenshot).

Example screenshot

The value in A3 (dropdown list) should decide what the currency is in B3:B5 and C6:C8. (B7 and C5 should stay numbers).

Can somebody help me with change the script? I already tried a lot but I can't find it working.

Thank you!

Kim

CodePudding user response：

Use Sheet.getRangeList().setNumberFormat(), like this:

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);
}