The function GoogleFinance
can do one of two things:
- return "realtime" data (with usual reporting delays, about 20 minutes). Example:
=GoogleFinance("CURRENCY:USDBRL")
- return historical data: the closing price on a particular day. Example:
=GoogleFinance("CURRENCY:USDBRL","price",DATE(2015,4,1))
What it does not do (unfortunately) is look up the price at a particular time of a particular day. So, if you want to record the price at the time of form submission, you should
- Execute
=GoogleFinance("CURRENCY:USDBRL")
right away.
- Store only the value returned by this function, because the function itself will auto-update.
Here is a script that does this on form submission.
function recordPrice() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var row = sheet.getLastRow();
var cell = sheet.getRange(row,3);
cell.setFormula('GoogleFinance("CURRENCY:USDBRL")');
while (!cell.getValue()) {}
cell.copyTo(cell, {contentsOnly:true});
}
function Install() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.newTrigger("recordPrice").forSpreadsheet(ss).onFormSubmit().create();
}
function Uninstall() {
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
ScriptApp.deleteTrigger(allTriggers[i]);
}
}
The function recordPrice
works with the sheet holding the form entries. To each new row it adds an entry in column C (just because I used sheet.getRange(row,3);
-- could be another column) with the currency rate. After setting the formula, it waits for the formula to fetch result, and then copies the result to the same cell, but only the value itself. As a result, the formula gets overwritten and the currency rate remains what it was at the time of
submission.
The other two functions are technical: run "Install" to install the script (it will prompt for authorization). If you want to stop collection of currency rates, run "Uninstall".