Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This is a solution to copy a Google Form responses (answers) to another Google Form. See https://webapps.stackexchange.com/a/154041/271529
// See https://webapps.stackexchange.com/a/154041/271529 for instructions
//
// EDIT THIS SECTION
// The source spreadsheet ID (find in url) and sheet Name
const sheetId = '18Z-XvuBOXIYc7LH-Ht162nGPu4JxMb0x2Hxxxxxxxxx';
const sheetName = 'Sheet 1';
// The destination form ID (find in url)
const formId = '1LGuNOBEZFKKYJIjwYvHuhVBfqRyA98kHJE-xxxxxxxx';
// Columns to skip within the spreadsheet. 1 = Timestamp. 2 = Timestamp + Email
const skipColumns = 1;
// Whether to create missing options (eg. if "Other" was activated first, and deactivated later).
// If this option is false and options are missing with "Other" deactivated, the script will throw an error.
const createMissingOptions = false;
// (Optional) How many rows to skip (the header is already included within a value of "0")
const skipRows = 0;
// (Optional) Whether to show the activity log
const showLogs = true;
//
// DO NOT EDIT THIS SECTION (unless you know what you're doing)
function BuildItems(items) {
// These types are not included in the spreadsheet, but are included in the form item definitions. Skip them.
const skipTypes = [ FormApp.ItemType.SECTION_HEADER, FormApp.ItemType.PAGE_BREAK ];
var retItems = [];
for (var i = 0; i < items.length; i++) {
var item = items[i];
var type = item.getType();
if (skipTypes.includes(type) > 0) {
continue;
}
// Need to treat every type of answer as its specific type.
switch (type) {
case FormApp.ItemType.CHECKBOX:
item = item.asCheckboxItem();
break;
case FormApp.ItemType.DATE:
item = item.asDateItem();
break;
case FormApp.ItemType.DATETIME:
item = item.asDateTimeItem();
break;
case FormApp.ItemType.TEXT:
item = item.asTextItem();
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item = item.asMultipleChoiceItem();
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = item.asParagraphTextItem();
break;
case FormApp.ItemType.SCALE:
item = item.asScaleItem();
break;
default:
// Not handling DURATION, GRID, IMAGE, TIME
Logger.log("Warning: Question '" + item.getTitle() + "' has unsupported type " + type + ". It will be skipped.");
item = null;
break;
}
retItems.push(item);
}
return retItems;
}
function FillFormfromSpreadSheet() {
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(sheetName);
var form = FormApp.openById(formId);
// All the form's questions
var items = BuildItems(form.getItems());
// Get the data within the spreadsheet
var data = sheet.getDataRange().getValues();
var EmptyString = '';
// Jumps first row, containing headers, plus user-specified amount
for (var row = 1 + skipRows; row < data.length; row++) {
try {
var response = form.createResponse();
// Using 'i' to count both data fields and filtered form items. Jump first data field containing timestamp, email, ..
for (var i = skipColumns; i < items.length; i++) {
var resp = data[row][i];
var item = items[i - skipColumns];
// Jumps the entire procedure for empty datafields, as may occur for optional questions
if (resp === EmptyString || item === null)
continue;
var type = item.getType();
// Handling special cases. Note that data[#][skipColumns] corresponds to item[0], as there's no timestamp item!
switch (type) {
case FormApp.ItemType.CHECKBOX:
// In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
// In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
if (typeof resp !== 'string') {
resp = resp.join(','); // Convert array to CSV
}
var cResps = resp.split(/\s*(?:,|;)\s*/); // Convert CSV to array
resp = cResps;
if (createMissingOptions === false) {
break;
}
var choices = item.getChoices();
var choicesName = choices.map(c => c.getValue())
var shouldSetChoices = false;
for (var c = 0; c < cResps.length; c++) {
const cResp = cResps[c];
if (choicesName.includes(cResp) === false) {
choices.push(item.createChoice(cResp));
shouldSetChoices = true;
}
}
if (shouldSetChoices) {
item.setChoices(choices);
}
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
if (createMissingOptions === false) {
break;
}
var choices = item.getChoices();
var choicesName = choices.map(c => c.getValue())
var shouldSetChoices = false;
if (choicesName.includes(resp) === false) {
choices.push(item.createChoice(resp));
shouldSetChoices = true;
}
if (shouldSetChoices) {
item.setChoices(choices);
}
break;
}
try {
if (showLogs) {
Logger.log("Q: " + item.getTitle() + " || " + type.toString() + " || " + resp);
}
}
catch (exc) {
Logger.log("Error: " + type + " || " + resp + " || row: " + row + " || form idx: " + itemIdx + " || sheet column: " + i);
throw exc;
}
// Add this answer to form
if (item) {
var respItem = item.createResponse(resp);
response.withItemResponse(respItem)
}
}
response.submit();
Utilities.sleep(500);
}
catch (exc) {
Logger.log("Error: an exception was thrown on row " + row);
throw exc;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment