Skip to content

Instantly share code, notes, and snippets.

@fliptheweb
Last active November 13, 2020 02:04
Show Gist options
  • Save fliptheweb/e8cb628aa3fbb50b8a120fd33c7cadee to your computer and use it in GitHub Desktop.
Save fliptheweb/e8cb628aa3fbb50b8a120fd33c7cadee 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;
}
@fliptheweb
Copy link
Author

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"))

@Askhento
Copy link

Thank you! By the way, I also put comma instead of semicolon in formulas. Everything mixed up after js.

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