Skip to content

Instantly share code, notes, and snippets.

@cyberbutler
Last active June 2, 2021 13:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save cyberbutler/4dddb4e231dd5448f6fc34171d1abbba to your computer and use it in GitHub Desktop.
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…
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);
}
}
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