1. Buat Google Form
2. Tentukan Form Responses
3. Buat Script di Apps Script masukan:
var sheetId = "1pTd5Av3tkKkim_HuKiKCP-Q2L-aONGKAbwAmOb2cdQ8" //Id Sheet
var formId = "1vL2D3A2Xq6UQZlOFs4T8N4JQ4CF5Mxi_tmHDIoJu1KY" //Id Form
function myFunction(){
var ss =SpreadsheetApp.openById(sheetId)
var sheet = ss.getSheetByName("Form Responses 1") //Sheet name
var form = FormApp.openById(formId)
//Roll No Short Answer
var data = [...new Set(sheet.getDataRange().getDisplayValues().map(row=> row[1]))].join('|')
var item = form.getItems().filter(item=>item.getTitle() == 'Roll No')[0].asTextItem()
var pattern = `(${data})`
var textval= FormApp.createTextValidation().setHelpText("Already exists in database !!").requireTextDoesNotMatchPattern(pattern).build()
item.setValidation (textval)
//Name Short Answer
var data1 = [...new Set(sheet.getDataRange().getDisplayValues().map(row=> row[2]))].join('|')
var item1 = form.getItems().filter(item=>item.getTitle() == 'Name')[0].asTextItem()
var pattern1 = `(${data1})`
var textval1 = FormApp.createTextValidation().setHelpText("Already exists in database !!").requireTextDoesNotMatchPattern(pattern1).build()
item1.setValidation (textval1)
//Remarks Paragraph
var data2 = [...new Set(sheet.getDataRange().getDisplayValues().map(row=> row[3]))].join('|')
var item2 = form.getItems().filter(item=>item.getTitle() == 'Remarks')[0].asParagraphTextItem()
var pattern2 = `(${data2})`
var textval2 = FormApp.createParagraphTextValidation().setHelpText("Already exists in database !!").requireTextDoesNotMatchPattern(pattern2).build()
item2.setValidation (textval2)
}
4. Buat Triger dengan settingan saat form di Submit