Last active
November 13, 2020 02:04
-
-
Save fliptheweb/e8cb628aa3fbb50b8a120fd33c7cadee to your computer and use it in GitHub Desktop.
Google Sheets Macros (Google App Script) for getting currency of number cell
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you! By the way, I also put comma instead of semicolon in formulas. Everything mixed up after js.