Created
August 10, 2021 18:33
-
-
Save alexis-/5d0c1f182970aa34f7ff5b83c9134cd5 to your computer and use it in GitHub Desktop.
This is a solution to copy a Google Form responses (answers) to another Google Form. See https://webapps.stackexchange.com/a/154041/271529
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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