Skip to content

Instantly share code, notes, and snippets.

@rjurney
Created February 27, 2020 02:35
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rjurney/20c8321ae7ff000483e2c2ff39f49ec0 to your computer and use it in GitHub Desktop.
Save rjurney/20c8321ae7ff000483e2c2ff39f49ec0 to your computer and use it in GitHub Desktop.
How to make a Google Form from a Google Sheet using Google Apps Script that nobody will be able to load to label Amazon open source projects because Forms doesn't scale to 50 questions...
//
// Google Apps Script that reads a Google Sheet based on the Github data and creates a form to classify projects
//
function createForm() {
// Create a new form, then add a checkbox question, a multiple choice question,
// a page break, then a date question and a grid of questions.
var form = FormApp.create('Amazon Open Source Project Classification');
form.setDescription("This form is for labeling open source projects using the project url and description with the following 4 categories:\n\n1) \
API - this is an API library that can only be used to access an Amazon Web Services (AWS) API\n2) EDUCATION - this is an educational project or example \
of how to use an AWS API\n3) GENERAL - this is a general purpose open source project with utility for the public\n4) DATASET - this is a dataset \
published by researchers as part of their researchz\n")
return form;
}
function addQuestion(form, url, description) {
form.addMultipleChoiceItem()
.setTitle("What category does the project fall under?")
.setHelpText(`${url} - ${description}`)
.setChoiceValues(['API','EDUCATION', 'GENERAL', 'DATASET']);
form.addPageBreakItem();
}
function formFromSheet(questionOffset, questionLength) {
// Stopping point
var questionRange = questionOffset + questionLength;
// Create the form for this sheet
var form = createForm();
var sheetId = "1kQ-zxSVjxNeGOC6emmR-TlQ_i0EjPm0KoDYoL_d0AFY";
var rangeName = 'Amazon Repository Data!A2:E2569';
var values = Sheets.Spreadsheets.Values.get(sheetId, rangeName).values;
for (var row = questionOffset; row < values.length && row < questionRange; row++) {
// Print columns A and E, which correspond to indices 0 and 4.
var rowId = values[row][0];
var recordId = values[row][1];
var url = values[row][2];
var description=values[row][3];
addQuestion(form, url, description);
}
Logger.log('Created form with starting offset of %s and ending offset of %s', questionOffset, questionRange);
}
function main() {
var sheetNumber = 0;
var questionOffset = 0;
var questionLength = 50;
formFromSheet(questionOffset, questionLength);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment