You have a Google Form that replaces a paper form; the paper form included an "Office Use" section and you want to retain this but am unsure whether and how this can be done.
It is NOT possible to hide questions. But the following answer enables a 100% digital solution. Bear in mind that there may be other ways to resolve this question.
OVERVIEW
- create a Google Form containing the user questions (without Office Use section) and link the responses to a new spreadsheet
- copy the "user questionnaire" form, name it for "Office Use", and link the responses to the same spreadsheet
- in the spreadsheet, use a script to create a profile of the item types and IDs in the "Office Use" form
- create an
onFormSubmit script, as each user submission is received, use the responses to create a submission in the "Office Use" form.
- create a url to each "Office Use" submission so that the reviewer can edit complete "Section B" of the form.
DETAILED STEPS
Step#1: Google Sheets - Part#1
- create a new Google Sheets spreadsheet
- you don't need a new spreadsheet but it simplifies building ands understanding this solution.
Step#2: Google Form - Section A
- create a Google form containing all the relevant "Section A" questions BUT without and "Section B" questions
- configure the form to export responses to the Google Spreadsheet created above.
Step#3: Google Form - Section B
- copy the "Section A" form and add the relevant questions for "Section B"
- configure the form to export responses to the Google Spreadsheet created above.
- copy a "pre-filled link" for the form.
- provide answers to all the "Section A" questions.
- where possible use a distinctive format for the answers such as first_name, last_name, **birthday"" and so on
- where answers are multiple choice or dropdown, just select any answer.
Step#4: Google Sheets - Part#2
- the spreadsheet will contain two sheets for form responses: "Form Responses 1" for "Section A" and "Form Responses 2" for "Section B"
- if you wish you can rename both sheets to something more informative; such as, "SectionA_form_responses" and/or "SectionB_form_responses".
- On sheet "SectionA_form_responses" insert an additional Column Header="Reviewer Link" to the right of the last column of form responses.
- this is column in which the script will paste the url for the Section B review.
Step#5: Sheets Project Editor
- copy the script into the Project editior
- create an installable
onFormSubmit trigger for buildUrls(e)
- "template" variable: replace the existing value with the "pre-filled link" obtained earlier.
- "values" variables: replace the variable names and Event Objects
values array index to suit the data in "SectionA_form_responses"
- FWIW, the script includes examples of the three methods of sourcing values: 1) Event Objects "values' array (as used in the script), 2) spreadsheet response value, 3) Event Objects "Named values"
- "url" variable: replace the distinctive placeholders and respective value variables
PROCESSING
"Section A" form
- each submission will update a row on "SectionA_form_responses"
and
- insert a hyperlink in the "Reviewer Link" column of the same row of "SectionA_form_responses"
- Note: each hyperlink carries pre-fill data for the respective respondent. The hyperlinks are NOT interchangeable.
"Section B" form
- the reviewer can click any hyperlink on "SectionA_form_responses"
- this will open a new "Office Use" form pre-filled with data for the relevant applicant.
- the reviewer can scan any/all the applicant answers.
- when the reviewer completes the "Section B" part of the form, the submission updates a new row on the "SectionB_form_responses" sheet.
h/t: @Mogsdad Is it possible to 'prefill' a google form using data from a google spreadsheet?
function buildUrls(e) {
// Logger.log(JSON.stringify(e)) // DEBUG
// update the 'template' variable from the pre-filled link
var template = "https://docs.google.com/forms/d/e/1FAIpQLScNM3gmrTM8m1eLvDQUhB_NZdm_SSlXWYGAoLlsH6PP-ahHaQ/viewform?usp=pp_url&entry.1360296307=**first_name**&entry.764247568=**last_name**&entry.1730236307=**employee_id**&entry.130080266=Cat+A"
/*
// assign values based on spreadsheet value
var responseSheet = SpreadsheetApp.getActive().getSheetByName("SectionA_Form_Responses")
var responsedata = responseSheet.getDataRange().getValues()
var editedRow = e.range.rowStart
// Logger.log("DEBUG: the edited row = "+editedRow)
var firstName = responsedata[(editedRow-1)][1]
var lastName = responsedata[(editedRow-1)][2]
// assign values based on question text
// Sample: "namedValues":{"Employee ID":["kfir"],"Job Category":["Cat C"],"Timestamp":["19/07/2023 17:54:35"],"First Name":["Mike"],"Last name":["Jones"]
var firstName = e.namedValues["First Name"]
var lastName = e.namedValues["Last name"]
*/
// assign values based on array index
// Sample: "values":["19/07/2023 17:54:35","Mike","Jones","k1423","Cat C"]
var values = e.values
var firstName = values[1]
var lastName = values[2]
var empID = values[3]
var jobCat = values[4]
// update url variables and values
var url = template.replace('**first_name**',firstName)
.replace('**last_name**',lastName)
.replace('**employee_id**',empID)
.replace('Cat+A',jobCat)
// Logger.log(url); // DEBUG
// find the column to insert the link
var columnHeader = 'Reviewer Link'
var headers = responseSheet.getRange(1, 1, 1, responseSheet.getLastColumn()).getValues();
var columnIndex = headers[0].indexOf(columnHeader);
// Logger.log(columnIndex) // DEBUG
var respRange = responseSheet.getRange(editedRow, columnIndex+1)
// Logger.log("DEBUG: target cell for review link = "+respRange.getA1Notation())
var respName = responsedata[(editedRow-1)][1]+" "+responsedata[(editedRow-1)][2]
// Logger.log("DEBUG: Name: "+respName)
// insert link
respRange.setFormula('=HYPERLINK("' + url + '", "' + respName + '")');
}
SAMPLES
Form Section A
Responses Sheet - Form Section A
Form - Section B (created by hyperlink & showing pre-fill)
Responses Sheet - Form Section B
