Applying Formulas to Data from Google Forms in Google Sheets

formulasgoogle sheetsgoogle-forms

I have a google spreadsheet which is connected to a google forms

I wanted one formula to be automatically copied to the new lines(Column R) when google form response is submitted

the spread sheet: https://docs.google.com/spreadsheets/d/1Z7uUBnzS5zKnxGswKKNAgn2XbRunqIzQZMATDl7skoE/edit#gid=1300745141

the formula:
if($O6="UZS",index(googlefinance("currency:UZSUSD","price",$B6),2,2)**$P6,if($O6="EUR",index(googlefinance("currency:EURUSD","price",$B6),2,2)*$P6,$P6)))

Best Answer

You want to insert a formula into Column R when a form submission is received.

As outlined in the comments:

  • Use an onformSubmit trigger to take action when a new submission is received. This is installable Trigger, so you will need to ... install it.
  • Take advantage of Event Objects to get the row number of the submission. In the script you'll note the argument "e" on the function, and then the variables "range", 'row" and "sheet" are declared.
  • use setFormula(formula) to insert the formula into the sheet.

Three things to note about your formula:

  1. "...,$B6),2,2)**$P6,..." You have two asterisks, when only one is required (assuming that you mean to multiplication and not exponentiation)
  2. The formula ends in three 'closed' brackets, but only two 'closed' brackets are required.
  3. Your IF statement includes several instances of evaluating a string. These necessarily require opening and closing double quotes("). However, double quotes are also required to substitute the reference to the row number. So, internal double quotes are escaped () and each instance of a row number in your formula has been substituted with "+row+". The end result is that the formula is dynamic regarding the submission row.
function onFormSubmit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get event variables  
  var range = e.range;
  var row = range.getRow();
  var sheet = range.getSheet();

  // setformula
  sheet.getRange(row, 18).setFormula("=if($O"+row+"=\"UZS\",index(googlefinance(\"currency:UZSUSD\",\"price\",$B"+row+"),2,2)*$P"+row+",if($O"+row+"=\"EUR\",index(googlefinance(\"currency:EURUSD\",\"price\",$B"+row+"),2,2)*$P"+row+",$P"+row+"))");
}
Related Topic