Clever Geek Handbook

Changing cell value based on another value on the same row on google submit form

I am very new to Google Forms and Spread Sheets and I want to have the Age question in my form and when the user submits the form I want it so that if the age is less than 18 the cell gets the MINOR value. and if it is 18 or higher, it is filled with "MAJOR".

Basically, I want to dynamically add a value to this cell base in the "AGE" value.

What I am doing right now:

function myfunction(e) {
  var age = Number(e.values[3]);
  var minmaj;
  if (age < 18){
    minmaj = "MINOR";
  } else {
    minmaj = "MAJOR";
  }
  SpreadsheetApp.getActiveSheet().getRange(e,14).setValue(minmaj);
}

Where e,14

is the cell of the minor / major value.

+3


source to share


2 answers


This formula can work the way you want it to be entered in row 2 of the column to the right of your form response data:

=ARRAYFORMULA(IF(A2:A="",,IF(E2:E<18,"MINOR","MAJOR")))



It will automatically detect new views and populate the cell based on the value in column E

0


source


Yes, simple problem ...

SpreadsheetApp.getActiveSheet().getRange(e,14).setValue(minmaj);
                                         ^


On this line, you are trying to use an event variable e

as a line number. There should be something like this:

SpreadsheetApp.getActiveSheet().getRange(e.range.getRow(),14).setValue(minmaj);
                                         ^^^^^^^^^^^^^^^^
0


source






More articles:


All Articles