Skip to content

Instantly share code, notes, and snippets.

@shokinn
Forked from fliptheweb/get_currency.js
Created December 15, 2017 09:40
Show Gist options
  • Save shokinn/cd819062a198bc72efedef3025157d13 to your computer and use it in GitHub Desktop.
Save shokinn/cd819062a198bc72efedef3025157d13 to your computer and use it in GitHub Desktop.
Google Sheets Macros (Google App Script) for getting currency of number cell
CURRENCY_MAP = {
'$': 'USD',
'₽': 'RUB', // there is a missing character in editor font
'€': 'EUR',
'฿': 'THB'
}
function _formatToCurrency(formatString) {
var currencyRegexp = /\[.*(.)\]/g;
var currency = currencyRegexp.exec(formatString)[1];
var currencyCode = CURRENCY_MAP[currency];
if (currencyCode) {
return currencyCode.toUpperCase();
} else {
return new Error('Unknown currency '+currency);
}
}
function getCurrency(cellValue, cellRow, cellColumn) {
var spreadsheet = SpreadsheetApp.getActiveSheet();
if (!cellValue || !cellColumn || !cellRow || !spreadsheet) {
return new Error('Missing arguments');
}
var cell = spreadsheet.getRange(cellRow, cellColumn);
var cellNumberFormat = cell.getNumberFormat();
var currencyCode = _formatToCurrency(cellNumberFormat);
if (currencyCode instanceof Error) {
Logger.log(currencyCode);
return currencyCode;
}
return currencyCode;
}
@shokinn
Copy link
Author

shokinn commented Dec 15, 2017

Get currency of cell (with number formating) in Google Sheets. Copy that macros to tools > script editor... and save. Then use it by =getCurrency(A1, ROW(A1), COLUMN(A1) ). For example, if you know currency of cell, you can use google finance api from your sheets by =C7(Cell with value) * GoogleFinance(CONCATENATE("CURRENCY:", K7(cell with currency), "RUB"))


Just copied from here

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment