Dependent Dropdown with Duplicated Fields Combined in Google Forms: GAS071
Introduction
If you are looking for a solution to create a Google Form with dependent dropdown, you may find the right place. In this Google Apps Script project, I am sharing the script to help you to create the form a little bit easier with the form data in Google Sheets.
* We don’t have to use the script to create the form like this, the script just helps to simplify the process when you have too many dropdowns
* We are creating a section for each dependent item from the parent level and link them directly
* A plus feature in the script is that we can combine the duplicated columns into one in the form responses
Demo
Try this demo form I’ve created. We have the 1st level for the “Department” with “HR, IT, PLANNING, MARKETING, and SALES”, the 2nd level will be the managers for each department selected.
https://forms.gle/56SMpXm13NytaQKB8
YouTube
Check this project introduction video on YouTube if you prefer to watch video instructions.
Instructions (5 Steps)
Follow the steps below to set up your own project.
Step 1
Make a copy of my project here to your Google Drive.
* The linked form in my Spreadsheet will not be copied, you can delete the “Form Responses”, and “Form Responses (combined)” sheets from your copy. We’ll create them in the following steps.
Step 2
Click any of the buttons from the menu “Google Form”, it will ask you to authorize the app for the 1st time to use it. Follow the guide below if you are not familiar with the authorization process.
A Complete Guide to Authorize GAS Project
Step 3
Customize your form settings in your copy since the current settings in yours are used for my demo only, you need to update it per your requirements.
Form Tab (you also can update them later in the form)
- title: the title of your form to be created
- description: the description of the form to be created
- collectEmail: weather to collect user’s email
- confirmation: the confirmation message of the form to be created
Dropdowns Tab
This is the core feature of this script, you can add your dependent dropdowns in this sheet, and the script will create them in the form for you. Header row will be used for the title of the fields, others will be used as the options for the field.
Page Tabs (you also can create them later in the form manually)
These tabs will be created as commonly used fields, you can have multiple pages. The tab name will be used for the section name in the form for each page.
Step 4
Create form by using the script “Create from” from the “Google Form” menu. It will create a new form and link it with the current Spreadsheet. In the meanwhile, it will create a tab called “Form Responses (combined)” for saving the response with no duplicated columns.
Step 5 (Optional)
To combine the duplicated columns in the original form responses, you can create a trigger with the script “Create trigger” from the menu “Google Form”. It will handle the duplicated columns and combine them in one single column in the tab “Form Responses (combined)”.
* You’ll have the option to delete the trigger if you created the trigger incase you want to combine the duplicated columns any more, the “Create trigger” will be replaced with “Delete trigger” after the trigger creation.
Source Code
Alternatively, you can copy the source code below to your own project.
class App {
constructor() {
this.wsDropdowns = SpreadsheetApp.getActive().getSheetByName("Dropdowns");
this.wsForm = SpreadsheetApp.getActive().getSheetByName("Form");
this.pageKey = "Question";
this.responseSheetName = "Form Responses";
this.sheetName = this.responseSheetName + " (combined)";
this.uuidHeader = "UUID";
this.sheet = null;
this.form = null;
}
unlinkForms() {
SpreadsheetApp.getActive()
.getSheets()
.forEach((sheet) => {
const url = SpreadsheetApp.getActive().getFormUrl();
if (url) {
sheet.setName(sheet.getName() + " (unlinked)").setTabColor("#777777");
FormApp.openByUrl(url).removeDestination();
}
});
this.form = null;
this.sheet = null;
}
getForm() {
const sheet = SpreadsheetApp.getActive()
.getSheets()
.find((sheet) => sheet.getFormUrl());
if (!sheet) return;
sheet.setName(this.responseSheetName);
this.form = FormApp.openByUrl(sheet.getFormUrl());
this.sheet = sheet;
return { sheet, form: FormApp.openByUrl(sheet.getFormUrl()) };
}
renameResponseSheet() {
const sheet = SpreadsheetApp.getActive()
.getSheets()
.find((sheet) => sheet.getFormUrl());
if (sheet) sheet.setName(this.responseSheetName);
this.sheet = sheet;
return sheet;
}
createDropdowns(form) {
const values = this.wsDropdowns.getDataRange().getValues();
const items = {};
const [parentTitle, childTitle] = values.shift();
values.forEach(([key, value]) => {
key = key.toString().trim();
value = value.toString().trim();
const item = items[key];
if (item) {
item.push(value);
} else {
items[key] = [value];
}
});
const kidPages = [];
const parentDropdown = form
.addListItem()
.setTitle(parentTitle)
.setRequired(true);
const choices = Object.keys(items).map((parent) => {
const page = form.addPageBreakItem().setTitle(parent);
form
.addListItem()
.setTitle(childTitle)
.setChoiceValues(items[parent])
.setRequired(true);
const choice = parentDropdown.createChoice(parent, page);
kidPages.push(page);
return choice;
});
parentDropdown.setChoices(choices);
return kidPages;
}
getPageItems(sheet) {
const values = sheet.getDataRange().getValues();
values.shift();
return values.map(([question, helpText, type, required, options]) => {
required = required === "Yes";
options = options.split(",").map((item) => item.trim());
return { question, helpText, type, required, options };
});
}
createPages(form) {
const pages = [];
SpreadsheetApp.getActive()
.getSheets()
.forEach((sheet) => {
if (sheet.getRange("A1").getValue() === this.pageKey) {
const items = this.getPageItems(sheet);
const page = form.addPageBreakItem().setTitle(sheet.getName());
items.forEach(({ question, helpText, type, required, options }) => {
// form = FormApp.getActiveForm()
let formItem;
switch (type.toLowerCase().trim()) {
case "paragraph":
formItem = form.addParagraphTextItem();
break;
case "checkbox":
formItem = form.addCheckboxItem().setChoiceValues(options);
break;
case "radio":
formItem = form
.addMultipleChoiceItem()
.setChoiceValues(options);
break;
case "dropdown":
formItem = form.addListItem().setChoiceValues(options);
break;
default:
formItem = form.addTextItem();
break;
}
formItem
.setTitle(question)
.setHelpText(helpText)
.setRequired(required);
});
pages.push(page);
}
});
return pages;
}
createForm() {
this.unlinkForms();
const values = this.wsForm.getDataRange().getValues();
const title = values[0][1] +
" - " +
Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
"yyyy-MM-dd hh:mm:ss",
);
const description = values[1][1];
const collect = values[2][1] === "Yes";
const message = values[3][1];
const form = FormApp.create(title);
form
.setDescription(description)
.setCollectEmail(collect)
.setConfirmationMessage(message);
const destination = DriveApp.getFileById(SpreadsheetApp.getActive().getId())
.getParents()
.next();
DriveApp.getFileById(form.getId()).moveTo(destination);
const dropdowns = this.createDropdowns(form);
const pages = this.createPages(form);
if (pages.length) {
dropdowns.forEach((dropdown) => dropdown.setGoToPage(pages[0]));
}
form.setDestination(
FormApp.DestinationType.SPREADSHEET,
SpreadsheetApp.getActive().getId(),
);
const url = form.getPublishedUrl();
SpreadsheetApp.flush();
this.form = form;
this.renameResponseSheet();
this.getForm();
const sheet = SpreadsheetApp.getActive().getSheetByName(this.sheetName) ||
SpreadsheetApp.getActive().insertSheet(this.sheetName);
sheet.clear();
return url;
}
getDropdownTitles() {
const values = this.wsDropdowns.getDataRange().getValues();
let [parentTitle, childTitle] = values.shift();
parentTitle = parentTitle.toString().trim();
childTitle = childTitle.toString().trim();
const parents = values.map((value) => value[0]);
const count = [...new Set(parents)].length;
return { parentTitle, childTitle, count };
}
onSubmit(e) {
const namedValues = e.namedValues;
const values = e.values;
const { rowStart, rowEnd, columnStart, columnEnd } = e.range;
const sheet = SpreadsheetApp.getActive().getSheetByName(this.sheetName) ||
SpreadsheetApp.getActive().insertSheet(this.sheetName);
sheet.setTabColor("#673BB7");
const form = this.getForm();
const headers = form.sheet
.getDataRange()
.getValues()[0]
.slice(columnStart - 1);
if (headers[headers.length - 1] !== this.uuidHeader) {
form.sheet.getRange(1, headers.length + 1).setValue(this.uuidHeader);
headers.push(this.uuidHeader);
}
const { parentTitle, childTitle, count } = this.getDropdownTitles();
const childValue = namedValues[childTitle]
.filter((item) => item !== "")
.join(", ");
const firstChildIndex = headers.indexOf(childTitle);
const uuid = Utilities.getUuid();
form.sheet.getRange(rowEnd, headers.length).setValue(uuid);
const newHeaders = [
...headers.slice(0, firstChildIndex),
...headers.slice(firstChildIndex + count - 1),
];
const newValues = [
...values.slice(0, firstChildIndex),
...values.slice(firstChildIndex + count - 1),
];
newValues[firstChildIndex] = childValue;
newValues[newHeaders.length - 1] = uuid;
sheet.getRange(1, 1, 1, newHeaders.length).setValues([newHeaders]);
sheet.appendRow(newValues);
}
}
function createForm() {
let title = "Apps Script";
SpreadsheetApp.getActive().toast("Creating...", title);
const app = new App();
let message = "";
try {
const formUrl = app.createForm();
message =
`The new form has been created successfully and linked to sheet "${app.responseSheetName}".`;
} catch (err) {
message = err.message;
title = "Error";
}
SpreadsheetApp.getUi().alert(
title,
message,
SpreadsheetApp.getUi().ButtonSet.OK,
);
}
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu("Google Form");
menu.addItem("Create form", "createForm");
const installed = PropertiesService.getScriptProperties().getProperty(
"installed",
);
if (!installed) {
menu.addItem("Create trigger", "createTrigger");
} else {
menu.addItem("Delete trigger", "deleteTrigger");
}
menu.addToUi();
}
function deleteTrigger() {
const ss = SpreadsheetApp.getActive();
ss.toast("Deleting...", "Apps Script");
const functionName = "onFormSubmit";
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach((trigger) => {
if (trigger.getHandlerFunction() === functionName) {
ScriptApp.deleteTrigger(trigger);
}
});
PropertiesService.getScriptProperties().deleteProperty("installed");
onOpen();
ss.toast("Trigger has been deleted.", "Apps Script");
}
function createTrigger() {
const ss = SpreadsheetApp.getActive();
ss.toast("Creating...", "Apps Script");
const functionName = "onFormSubmit";
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach((trigger) => {
if (trigger.getHandlerFunction() === functionName) {
ScriptApp.deleteTrigger(trigger);
}
});
ScriptApp.newTrigger(functionName)
.forSpreadsheet(SpreadsheetApp.getActive())
.onFormSubmit()
.create();
PropertiesService.getScriptProperties().setProperty("installed", "yes");
onOpen();
ss.toast("New trigger has been created.", "Apps Script");
}
function onFormSubmit(e) {
const app = new App();
app.onSubmit(e);
}
GoogleAppsScript Playlist (Follow My Channel for New Projects)
Links
Hire me on Upwork (work with me)
Comments
Post a Comment