Building forms from sheets with Google's AppScript
In a project I was working on, I had to build a form for some human evaluators from a large collection of question-answer pairs, I couldn't find what I was looking for online quickly, so I turned to the obvious solution, spend time researching how to do this programmatically 😅
Enter Google AppScript—a tool I'd heard a bit about but hadn't delved into myself. After a quick dive into its capabilities, I was surprised it's pretty impressive. AppScript, in my situation, allows you to write a quick script directly within a Google Sheet, which can then generate a Google Form (no need for auth and verification stuff when creating the form if you're logged in you're good to go).
I found it pretty clean for eliminating the need for third-party apps for simple tasks like this. Check out this code snippet I used, it could save you some time.
function createFormFromSheet() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let data = sheet.getDataRange().getValues();
let form = FormApp.create('Chatbot Evaluation Form');
form.setDescription('Any questions or concerns please contact us');
for (let i = 1; i < data.length; i++) { // Start at 1 to ignore headers
let row = data[i];
let question = row[1]; // Assumes "Question" is the second column
let answer = row[2]; // Assumes "Answer" is the third column
let generatedAnswer = row[3]; // Assumes "GeneratedAnswer" is the fourth column
let item = form.addScaleItem()
.setTitle(`${question}`)
.setBounds(1, 5)
.setLabels('Poor', 'Excellent')
.setRequired(true);
item.setHelpText('********* Answer (DONT EVALUATE THIS) *********\n' + answer + '\n\n ********* Generated Answer (EVALUATE THIS) *********\n' + generatedAnswer);
}
Logger.log('Published URL: ' + form.getPublishedUrl());
Logger.log('Editor URL: ' + form.getEditUrl());
}