Let's say I'm a hiring manager and I need to conduct an interview with 20 different candidates on 20 different dates, and I need a "yes" or "no" confirmation if they can attend on said date. I only want to show the date assigned to the interviewee in the form, but I need the other dates hidden away in the form (for the other interviewers) so they show up in the "Responses" sheet. It's also important that they feel "special", so I need to address them by there name in a pre-populated text field (this text field will be what's populated in the response sheet). It's important to keep the candidates hidden from each other so they aren't discouraged, so making a pre-populated "dropdown" menu for them to choose from isn't an option.
Ideally, these interviewees would only see 2 fields when opening up the form:
- There Full Name (pre-populated text field)
- There Assigned Date (multiple choice: "yes" or "no")
RECAP:
- 1 Google Form (2 form fields - the rest hidden)
- 20 Interviewees (pre-populated text field)
- 20 Assigned Dates (multiple choice: "yes" or "no")
- 1 Google "Responses" Sheet (21 columns)
I've attempted...
- Prefilled URL links based on prefilled "multiple choice" answer (no luck)
- Research on Google Form "Hidden Fields" (no luck)
- Research on sending custom link pointing to "form section" (maybe?)
Does anyone know the best way for achieving this?
Thanks in advance.
GOOGLE FORM
┌────────────────────────┐
│ NAME (pre-populated) │
└────────────────────────┘
Will you be attending on <custom-date>?
⦿ YES
⦾ NO
⦾ MAYBE
╔════════╗
║ SUBMIT ║
╚════════╝
GOOGLE SHEET (Responses)
# | NAME | 1/1 | 1/2 | 1/3 | - - - - - -> 1/20
---------------------------------------------------------
1 | ARON | YES | | |
---------------------------------------------------------
2 | BRAD | | YES | |
---------------------------------------------------------
3 | CRIS | | | NO |
---------------------------------------------------------
| | | | |
| | | | |
20 | ZEEK | | | |
V V V V V
You can create personalized links with unique URL parameters for every interviewee and use apps script doGet(e) function to get those parameters and serve personalized web pages. https://developers.google.com/apps-script/guides/web
In short, personalized link can be created like below to send a get request to the webApp
https://script.google.com/macros/s/...../exec?name=A2&date=1/2/2017
and get URL parameters with this:
function doGet(e){
var param = e.queryString //Will get name=A2&date=1/2/2017
//or
var param = e.parameter //Will get {"name": "A2", "date": "1/2/2017"}
}
You can create personalized links using a simple =CONCATENATE formula like in this example spreadsheet in "Sheet2". You can send this personalized links to each interviewee and when they access the web page you serve them personalized web page on the URL parameters like so:
function doGet(e) {
var param = e.queryString
var parameters = param.split("&")
// This just checks only 2 parameters are present else gives a invalid link
if (param != null && parameters.length == 2){
param = e.parameter
var name = param.name
var date = param.date
var html = HtmlService.createHtmlOutputFromFile("Invite")
var htmlTemplate = html.asTemplate().getRawContent()
// use the replace function to input the name and date on the page
// You also replace the hidden input values at the same time
htmlTemplate = htmlTemplate.replace(/customName#/gi, name )
htmlTemplate = htmlTemplate.replace(/customDate#/gi, date)
html = HtmlService.createHtmlOutput(htmlTemplate).asTemplate().evaluate()
}else {
var html = HtmlService.createHtmlOutput("<b> Invalid Link <b>")
}
return html
}
Html Code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div id ="div_form">
<form id="RSVPform" onSubmit = "return false">
<h1> customName# </h1> <br>
<input type = "hidden" id = "name" value = 'customName#' >
Will you be attending on customDate# <br>
<input type = "hidden" id = "date" value = 'customDate#' >
<input type = "radio" name = "RSVP" value = "Yes" checked>Yes <br>
<input type = "radio" name = "RSVP" value = "No">No <br>
<input type = "radio" name = "RSVP" value = "Maybe">Maybe <br>
<button type = "button" onClick ="sendRSVP()">Submit</button>
</form>
</div>
<div id="accept"></div>
<script>
function sendRSVP(){
var resp = []
resp[0] = document.getElementById("name").value
resp[1] = document.getElementById("date").value
resp[2] = document.querySelector('input[name="RSVP"]:checked').value;
google.script.run.withSuccessHandler(closeForm).enterRSVP(resp)
}
function closeForm(foundIndex){
var subResp
if(foundIndex){
subResp = "Thank You for your response"
} else {
subResp = "Oops! Cannot find the meeting event"
}
document.getElementById("div_form").style.display = "none"
document.getElementById("accept").innerHTML = subResp
}
</script>
</body>
</html>
Finally, this code will check and enter the response to appropriate row and column in the spreadsheet
function enterRSVP(resp){
var ss = SpreadsheetApp.openById(id)
var sheet = ss.getSheetByName("Sheet1")
var RSVPdata = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues()
//match name
for (var i=0;i<RSVPdata.length;i++){
if(resp[0] == RSVPdata[i][0]){
var setRowIndex = true
break
}
}
if(setRowIndex)
var rowIndex = i+1
else
return false
//Match Date
for (var i= 0; i<RSVPdata[0].length; i++){
if(resp[1] == RSVPdata[0][i]) {
var setColIndex = true
break
}
}
if(setColIndex)
var colIndex = i+1
else
return false
sheet.getRange(rowIndex,colIndex).setValue(resp[2])
return true
}
You can find a working example here in this spreadsheet. Just use any links on Sheet2 to get a personalized Form. Once you submit this form Sheet1 will be updated.
Note: In the above example all the dates are formatted as text/strings. This is to prevent issues during concatenation. Secondly, you will have to write a script to send the above links to each interviewee which is easily doable.
