0

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

  • 1
    Welcome. You probably saw several variations on this during your research. 1) you need a trigger to take action when a new submission is received: onFormSubmit=installable Trigger; 2) you want to take advantage of Event Objects to get the row number of the submission; 3) use setFormula(formula) ref to insert the formula into the sheet. – Tedinoz Jan 14 at 4:54
1

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+"))");
}
| improve this answer | |
  • Thank you very much for your help! to be honest, I am neither a programmer nor someone who can write codes. I am just an accountant. That's why I have just copied what you have written above. but there is something going wrong. i am trying to understand and follow what you have suggested but it seems I have no knowledge enough to solve the issue, anyway thank you VERY MUCH one more time – Aroon Advisory Group Jan 15 at 7:08
  • Have you installed the Trigger? – Tedinoz Jan 24 at 6:36
  • i have, i think! here is what i have: – Aroon Advisory Group Jan 29 at 15:30
  • var form = FormApp.openById('1300745141'); ScriptApp.newTrigger('myFunction') .forForm(form) .onFormSubmit() .create(); 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+"))"); } – Aroon Advisory Group Jan 29 at 15:31
  • now it is saying no item with the give ID could be found – Aroon Advisory Group Jan 29 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.