Building forms from sheets with Google's AppScript

sheetsformsappscript

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());
}
photophoto