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

Question

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?

• A: London
• B: Paris
• C: Madrid
• D: Helsinki

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.

Solution 1

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:

• Spreadsheet
• Google Forms

Solution 2

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.