I have a google form to collect data having a unique key named customer_code. I would like to show an immediate notification if user types a customer code which is already in google sheet. When value of customer code matches with the already entered value, form will immediately provide message so that user no more try for giving entry in the lower field.
For this, I have developed my code as follows:
`function myFunction(){
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(“Form Responses 1”); //sheet name
var form = FormApp.openById(formId);//Reference Number Short Answer
var data = […new Set(sheet.getDataRange().getDisplayValues().map(row=> row[1]))].join(‘|’);
var item = form.getItems().filter(item=>item.getTitle() == ‘Customer Code’)[0].asTextItem();
var pattern = ‘(${data})’;
var textval = FormApp.createTextValidation().setHelpText(“Already Exists in database”).requireTextDoesNotMatchPattern(pattern).build();
item.setValidation(textval);
}
`
Habib KGDCL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.