Get URL Link to a “SECTION” only

Question

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...

1. Prefilled URL links based on prefilled "multiple choice" answer (no luck)
2. Research on Google Form "Hidden Fields" (no luck)
3. 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

Answer 1

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.