Bulk currency change within the whole Google Sheet

Question

I have a "Currency" value cell on the "Cover page" tab with drop-down values "USD, EUR, GBP". What I need is to update the currency format for the whole document(more than 10 tabs with different ranges) by choosing currency value on the "Cover page". Are there any suggestions on how can I do that automatically?

Here are some methods(as an examples), I've tried.

function changeCurrency() {

  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getSheetByName('Cover Page');
  
  var targetCell = ss.getRange("J8")
  
  var targetSheet = SS.getSheetByName(sheetNameRange);
  
  var range1 = ss.getRange("B8:C200");
  var range2 = ss.getRange("M11:Q13");
  var range3 = ss.getRange("M25:Q40");

  var format1 = "£ 00.00"
  var format2 = "$ 00.00"
  var format3 = "€ 00.00"
if (targetCell = "GBP")
  {range1.setNumberFormat(format1);
  range2.setNumberFormat(format1);
  range3.setNumberFormat(format1);
  }
if (targetCell = "USD")
  {range1.setNumberFormat(format2);
  range2.setNumberFormat(format2);
  range3.setNumberFormat(format2);
  }
else
 {range1.setNumberFormat(format3);
  range2.setNumberFormat(format3);
  range3.setNumberFormat(format3);
  }
}

The first one does not work for me, because ranges with currency format can be changed - new lines can be added, new columns can be added. Also, sheet names can be changed as well by different users.

The second variant I've tried was to change the document's locale. It could be helpful, but new currency applies only if you update the currency for all the ranges with currency format.

  function changeLocale() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getSheetByName('Cover Page')
  var value = ss.getRange("J8")
if (value = "USD")
{ SS.setSpreadsheetLocale('en_US')
}
if (value = "GBP")
{ SS.setSpreadsheetLocale('en_UK')
}
if (value = "EUR")
{ SS.setSpreadsheetLocale('en_DE')
}
}

Thank you in advance for any options or suggestions regarding this questions.

Do you want to change the formatting of cells without changing the quantity in them? Let me be more clear, if you have `$ 1.00` wouldn't convert to `€ 0.90`, this is what you want to achieve? If you want to make the conversion you should use `=GOOGLEFINANCE(“CURRENCY:USDEUR")`. Therefore, it is not entirely clear to me what it is intended to achieve. Can you share a sanitized copy of the spreadsheet? — Emel, Mar 30 '22 at 14:18
Thank you for your response @Emel. Let me clarify, you're right, no need to change the quantity as it is done on my master tab and I actually vlookup needed quantity depending on "Cover page" currency value. The only thing I need is to change formatting. I'll prepare test document and add to my question. Thank you! — Vladislav Muzychka, Mar 31 '22 at 11:05
As your first approximation, have you tried, select the whole range and before making the change, with [`getNumberFormat()`](https://developers.google.com/apps-script/reference/spreadsheet/range#getnumberformat) check if contains one of the currency symbols, and if it does, change the format? — Emel, Apr 01 '22 at 08:18

score 0 · Answer 1 · answered Apr 04 '22 at 12:04

As I say in my comment, the following script checks every cell number format in the Sheet, and if it contains one of the currency symbols ($, € or £) changes it to the desired one:

function changeCurrencyFormat() {
  const ss = SpreadsheetApp.getActiveSheet()
  const range = ss.getDataRange()
  range.getNumberFormats().forEach((row, idxR) => {
    row.forEach((cellF, idxC) => {
      if (cellF.includes('$') || cellF.includes('€') || cellF.includes('£')) {
        ss.getRange(idxR + 1, idxC + 1).setNumberFormat('€ 00.00')
      }
    })
  })
}

It would be easy to adapt it to your current script to include all the formats.

Glad to hear it! If you consider my answer helpful, please consider upvote/accepting it. — Emel, Apr 12 '22 at 22:21

Bulk currency change within the whole Google Sheet

1 Answers1