Google-sheets – How to use a time stamp from a Google Form in a Google finance function

google sheetsgoogle-forms

I have students enter a value in a form which determines which currency they want to buy. I want the Google Spreadsheet to look up and record the price when the form is submitted.

Since I want to determine a fictitious charge for the currency transaction I do not want the spreadsheet to automatically update the price of the currency in this part of the spreadsheet.

Best Answer

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

  1. Execute =GoogleFinance("CURRENCY:USDBRL") right away.
  2. 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".

Related Topic