Last active
June 2, 2021 13:18
-
-
Save cyberbutler/4dddb4e231dd5448f6fc34171d1abbba to your computer and use it in GitHub Desktop.
This script can be used to auto generate a google form by using Google Apps Script in conjunction with the FormApp and SpreadsheetApp APIs. In this case, I use a Spreadsheet to track Form IDs of other forms in a competition, then extract the submissions to then embed them into a single google form for Voting purposes. You can read more about the…
This file contains hidden or 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
const spreadsheet_id = "INSERT SHEET ID HERE"; | |
function createTheForm() { | |
createForm("CompetitionOne") | |
} | |
function lookupValueFromSpreadsheet(field, lookup_field, keyfield="CompetitionId", sheetname="CompetitionParameters") { | |
const charList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" | |
var ss = SpreadsheetApp.openById(spreadsheet_id); | |
var sheet = ss.getSheetByName(sheetname); | |
var named_range = ss.getNamedRanges().filter(r => r.getName() === sheetname)[0]; | |
var rows = named_range.getRange().getValues(); | |
var fields = rows[0]; | |
var items = rows.splice(1); | |
var keyfield_index = fields.map((f, index) => { | |
return f === keyfield ? index : null | |
}).filter(index => index !== null)[0] | |
var row_index = items.map((row, index) => { | |
return row[keyfield_index] === field ? index : null | |
}).filter(index => index !== null)[0] + 2; | |
var column_index = fields.map((f, index) => { | |
return f === lookup_field ? index : null | |
}).filter(index => index !== null)[0]; | |
var range = `${charList[column_index]}${row_index}`; | |
var cell = sheet.getRange(range); | |
return cell | |
} | |
function enumerateSubmissions(form_id) { | |
var form = FormApp.openById(form_id); | |
var submissions = []; | |
form.getResponses().forEach(resp => { | |
items = resp.getItemResponses(); | |
var meta = {} | |
items.forEach((item, i) => { | |
let title = item.getItem().getTitle() | |
let value = item.getResponse(); | |
if (Array.isArray(value)) { | |
file = DriveApp.getFileById(value[0]); | |
meta.filename = file.getName(); | |
meta.url = file.getUrl(); | |
meta.id = file.getId(); | |
meta.mimetype = file.getMimeType(); | |
meta.content = file.getMimeType().includes('image') ? file.getAs("image/png") : ''; | |
} else { | |
meta.title = value | |
} | |
}); | |
submissions.push(meta); | |
}) | |
return submissions; | |
} | |
function createForm(competition_id) { | |
// Lookup Values from the CompetitionParameters Spreadsheet | |
const competition_name = lookupValueFromSpreadsheet(competition_id, "CompetitionName").getValue(); | |
const competition_description = lookupValueFromSpreadsheet(competition_id, "CompetitionDescription").getValue(); | |
const competition_submission_form_id = lookupValueFromSpreadsheet(competition_id, "CompetitionFormId").getValue(); | |
// Create Form | |
var form = FormApp.create(competition_id); | |
// Set Form Properties | |
form.setTitle(competition_name); | |
form.setDescription(competition_description); | |
form.setConfirmationMessage("Thank you for voting! Have a Merry Maveristmas and a Happy Holiday!") | |
form.setAcceptingResponses(true); | |
form.setCollectEmail(true); | |
form.setLimitOneResponsePerUser(true); | |
form.setRequireLogin(true); | |
try { | |
var submissions = enumerateSubmissions(competition_submission_form_id); | |
if (submissions.length == 0) { | |
throw new Error(`[-] No Submissions for ${competition_id}`); | |
} | |
const categories = [ | |
{ name: "Most Creative" }, | |
{ name: "Most Hilarious" }, | |
{ name: "Most Original" } | |
]; | |
if (submissions.every(s => s.filename !== undefined)) { | |
// There are file entries which means we will create votes for each category | |
submissions.filter(s => s.mimetype.includes('image')).forEach(s => { | |
var image_item = form.addImageItem().setTitle(s.title); | |
try { | |
image_item.setImage(s.content); | |
} catch (e) { | |
form.deleteItem(image_item.getIndex()); | |
var item = form.addMultipleChoiceItem().setTitle(s.title); | |
item.setChoices([item.createChoice(s.url)]) | |
} | |
}); | |
const videos = submissions.filter(s => s.mimetype.includes('video')); | |
if (videos.length) { | |
var video_item = form.addMultipleChoiceItem().setTitle("View Video Submissions") | |
video_item.setChoices( | |
videos.map(v => video_item.createChoice(`${v.title}: ${v.url}`)) | |
) | |
} | |
categories.forEach(category => { | |
var item = form.addListItem(); | |
item.setTitle(category.name); | |
item.setChoices( | |
submissions.map(o => item.createChoice(o.title)) | |
); | |
}); | |
} else { | |
// No file entries, therefore we create a multi choice vote system for the best entry overall | |
var item = form.addMultipleChoiceItem(); | |
item.setTitle("Best Caption"); | |
item.setChoices(submissions.map(o => item.createChoice(o.title))) | |
} | |
Logger.log("[+] Success! Form Created for " + competition_id) | |
} catch (e) { | |
console.error(e.message); | |
} | |
} |
This file contains hidden or 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
function lookupValueFromSpreadsheet(field, lookup_field, keyfield, sheetname, spreadsheet_id) { | |
// Use this function to extract a single Cell in a table: | |
// Example Table: | |
// | ID | Name | Favorite Food | | |
// | -- | ---- | ------------- | | |
// | 1 | Sven | Cheese Burger | | |
// | 2 | Lars | Chocolate | | |
// | |
// Usage: | |
// const lars_favorite_food_cell = lookupValueFromSpreadsheet("Lars", "Favorite Food", "Name", "Example Table", "blahblahblah"); | |
// | |
// Get the value: | |
// const lars_favorite_food = lars_favorite_food_cell.getValue(); | |
// Set the value: | |
// lars_favorite_food_cell.setValue("Cucumbers"); | |
const charList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; | |
var ss = SpreadsheetApp.openById(spreadsheet_id); | |
var sheet = ss.getSheetByName(sheetname); | |
var named_range = ss.getNamedRanges().filter(r => r.getName() === sheetname)[0]; | |
var rows = named_range.getRange().getValues(); | |
var fields = rows[0]; | |
var items = rows.splice(1); | |
var keyfield_index = fields.map((f, index) => { | |
return f === keyfield ? index : null | |
}).filter(index => index !== null)[0] | |
var row_index = items.map((row, index) => { | |
return row[keyfield_index] === field ? index : null | |
}).filter(index => index !== null)[0] + 2; | |
var column_index = fields.map((f, index) => { | |
return f === lookup_field ? index : null | |
}).filter(index => index !== null)[0]; | |
var range = `${charList[column_index]}${row_index}`; | |
var cell = sheet.getRange(range); | |
return cell | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment