google-apps-script

Google App Script, Find value and write a result

I have a form where the answers arrive on a google excel sheet. I would like the script to do this:

Each new row that is created:

In column "I" if I have this value "Light or gray hair" it must write in column J "No"

In column "I" if I have other values ​ ​ that are not "Light or gray hair" it must write in column J "OK"

Solution

  • How to use Form Submit using App script

    Try to use this approach with conditional statements and on form submit using Apps Script.

    You will need to adjust your range depending on your data set.

    Script used

    function myfunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  console.log(values);
  for (var i = 1; i < values.length; i++) { 
    var color = values[i][8];
    if (color === ""){
      sheet.getRange(i + 1, 10, 1, 1).setValue('');
    }
    else if (color.toLowerCase() === 'light gray' || color.toLowerCase() === 'gray hair') {
      sheet.getRange(i + 1, 10, 1, 1).setValue('NO');
      console.log(color);
    } else {
      sheet.getRange(i + 1, 10, 1, 1).setValue('OK'); 
    }
  }
}

    Sample Output

    Spreadsheet

    spreadsheet

    Add triggers

    time-driven

    ==========================================================================

    Other Option using Google Sheet Formula

    if you are open to an alternative solution other than using Google apps script to satisfy your expected result. I would suggest using Google sheet formula for easier implementation. Just paste this formula in Column J2 and make that of no value in ColumnJ.

    =BYROW(I2:I, LAMBDA(row, IF(row <> "", IF(OR(LOWER(row) = "light gray", row = "gray hair"), "NO", "OK"), "")))

    Google Sheet Formula Sample Output

    Sample

    Reference