Google Sheets Programmatically Apply Custom Currency Symbols Number Format

Question

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:

If I use the spreadsheet function "CONCATENATE", I get a string and cannot do math or chart with the final value.
Spreadsheet function TEXT() can get the number format to display how I want but not all custom currency symbols "work" - the following for instance turns the value into a text string and I cannot do math/charts with it.

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

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

If I create a custom function, say format(cell), and use JS to add any text strings to the value, it's no longer a number type.

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

setNumberFormat(format) cannot be used in a custom function to be called via spreadsheet formulas.
Copying another cells formatting? I also see this as a workaround but it looks really not efficient if I have to copy and paste formatting for every cell I want the proper formatting : Applying "Automatic" number formatting
Copying the values into another column just for conditional formatting and the original numbers only range used for maths/charting. This is more a workaround a not a clean solution.

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?

Why not just Format > Number > More Formats > Custom Number Format and supply `[$Ƀ]#,##0.00000000`? — Robin Gertenbach, Nov 16 '17 at 09:26
`query` can do custom formatting without making it as text. You could try using query instead of `text()` in sheets API. — TheMaster, Nov 16 '17 at 09:30
@RobinGertenbach The reason I cannot do that is because the cell values are dynamic - the values are determined by a data validation drop down. So different values require different formats. Your method will give that cell a format to apply to all conditions. — Denis Lam, Nov 16 '17 at 13:27

score 4 · Accepted Answer · answered Nov 17 '17 at 07:50

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

Set cell format to text with google apps script

References

Google Sheets Programmatically Apply Custom Currency Symbols Number Format

1 Answers1