CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetscustom-function
Denis Lam
Denis Lam

Reputation: 63

Google Sheets Programmatically Apply Custom Currency Symbols Number Format

I would like to programatically apply a custom number format so that I can use custom currency symbols in my cells (such as for cryptocurrency) and keep them as numbers so that I can do math with them or chart the values.

I have looked at the following options already:

Ex: =TEXT(SUM(C5:C7),"Ƀ#,##0.00000000")

reference on TEXT(): https://support.google.com/docs/answer/3094139?hl=en

Ex: function GBP(amt) { return '£' + parseFloat(amt).toFixed(2)); }

This won't work either as resulting string is not a number:

function BTC(amt) { return parseFloat('Ƀ' + parseFloat(amt).toFixed(2)); }

In short, I just want to add custom currency symbols to my numbers and still be able to do math and charts with them. Does anyone know a working, direct way to do so via Google Apps Scripts or spreadsheet functions/menus?

Upvotes: 1

Views: 3762

Answers (1)

Wicket
Wicket

Reputation: 38284

To programmatically apply a currency format to a single number and to keep it as a number in order to do arithmetic operations with it use setNumberFormat(string).

NOTES:

  • A custom function can't be used to apply a number format as custom functions can't modified the cell format.
  • Conditional Formatting doesn't apply number formatting
  • The Google documentation doesn't include currency formats. They could be got from the Google Sheets UI. First set the desired currency format for a cell by clicking on Format > More Formats > More Currencies, the click on Format > More Formats > Custom Number Formats... and copy the format from the text box.

Example:

The setNumberFormat is a partially implemented Google Apps Script function that currently applies Pound sterling currency format ([$£-809]#,##0.00) or a default format (#,##0.00) . The test function is used to call it assigning 'Pound sterling' to the format parameter.

function test(){
  setNumberFormat('Pound sterling');
}

function setNumberFormat(format) {
  var range = SpreadsheetApp.getActiveRange();
  var numberFormat = '';
  try {
    switch (format){
      case 'Pound sterling':
        numberFormat = '[$£-809]#,##0.00';
        break;
      default:
        numberFormat = '#,##0.00';
        break;
    }
    range.setNumberFormat(numberFormat);
  } catch (e){
    throw new Error('There was an error: ' + e);
  }
}

Related Q&A

References

Upvotes: 4

Related Questions