CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
L Nicol
L Nicol

Reputation: 129

Is it possible to export questions and multiple choice options from a Google Form to a Google Sheet?

We have a series of Google Forms that contain multiple choice questions, each with 4 possible answers.

I would like to be able to export the question and all possible answers to a Google Sheet for all of the questions and answers in that Google Form.

For example:

Q1: What is the capital of England?

I've tried a variety of add-ons. There are loads that allow Google Sheets > Google Form, but nothing in reverse (that I can find), so I assume it will be a script of some kind.

Any help would be really appreciated.

Thanks. Liam.

Upvotes: 10

Views: 26824

Answers (4)

NickB
NickB

Reputation: 409

I needed a script to convert some Google Forms to the GIFT Moodle format. I modified @alberto-vielma script to obtain a SpreadSheet with the questions and choices in Moodle GIFT format.

Just copy and paste the values in the the SpreadSheet in a text file to import into Moodle.

// Open a form by ID.
var form = FormApp.openById('YOUR-FORM-ID'); // YOU GET IT FROM THE URL

// Open a sheet by ID.
var sheet = SpreadsheetApp.openById('YOUR-SPREADSHEET-ID').getSheets()[0];

// variables for putting the questions and answers in the right position
// Change this number to the line you want the question starts
var question_position = 1;

// main function to run
function getFormValues() {
  form.getItems().forEach(callback);
}

// Iterate over all questions 
function callback(el){
  
  // check if the question is multiple choice
  if (el.getType() == FormApp.ItemType.MULTIPLE_CHOICE) {
    // change the type from Item to MultipleChoiceItem
    var question = el.asMultipleChoiceItem();
    var choices = question.getChoices();

    // set the title of the question in the cell
    var qRange = sheet.getRange(question_position++, 1);
    qRange.setValue(question.getTitle() + " {");
    
    
    var i = 0;
    // set the answers in the right cells
    for (i; i < choices.length; i++){
      var choiceRange = sheet.getRange(question_position++, 2);
      var current = choices[i];
      
      var prefix = current.isCorrectAnswer() ? "'=" : "~";
                  
      choiceRange.setValue(prefix + current.getValue());
    }

    var qRangeEnd = sheet.getRange(question_position++, 1);
    qRangeEnd.setValue("}");

    question_position ++;
  }
    
}

Upvotes: 0

Dante Bazaldua
Dante Bazaldua

Reputation: 19

I got almost the same problem that you were dealing with, I created a little script with the documentation for my own purposes but I think it may help you to understand how to retrieve the information.

You need to be aware of these two API: https://developers.google.com/apps-script/reference/forms (forms) and https://developers.google.com/apps-script/reference/spreadsheet (sheets)

Google Form mapping

Then, I would check how to post it into a Google Sheet through the API.

Check you have all the permissions set.

Upvotes: 1

alberto vielma
alberto vielma

Reputation: 2342

In the following code, which I made using Apps Script, you can find a way to extract questions and answers from a google form and then put the values in a certain sheet of your choice

// Open a form by ID.
var form = FormApp.openById('YOUR-FORM-ID');
// Open a sheet by ID.
var sheet = SpreadsheetApp.openById('YOUR-SHEET-ID').getSheets()[0];

// variables for putting the questions and answers in the right position
var question_position = 0;
var answers_position = 0;

// main function to run
function getFormValues() {
  form.getItems().forEach(callback);
}

// Iterate over all questions 
function callback(el){
  
  // check if the question is multiple choice
  if (el.getType() == FormApp.ItemType.MULTIPLE_CHOICE) {
    // change the type from Item to MultipleChoiceItem
    var question = el.asMultipleChoiceItem();
    var choices = question.getChoices();
    // set the title of the question in the cell
    sheet.getRange(question_position +1, 1).setValue(question.getTitle());
    
    var i = 0;
    // set the answers in the right cells
    for (i; i < choices.length; i++){
      sheet.getRange(answers_position + 1, 2).setValue(choices[i].getValue());
      answers_position++;
    }
    question_position += i;
    answers_position++;
  }
  question_position++;
  
}

Docs:

If you're wondering where I got all this info you can check these two links:

Upvotes: 9

Rami
Rami

Reputation: 520

This seems like you'd need an Apps Script add-on or a manually developed Apps-Script script. Try to find a freelancer or a coworker to build it for you.

Sheets is the easiest to work with: https://developers.google.com/apps-script/reference/spreadsheet/

Upvotes: 0

Related Questions