10

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.

Sourabh Choraria
  • 2,255
  • 25
  • 64
L Nicol
  • 111
  • 1
  • 2
  • 5
  • This looks like an easy, interesting task to work on :) Could you perhaps share a few sample Google forms and the Sheet format in which you expect the data to be as an output? I don't mind helping out. – Sourabh Choraria Oct 28 '19 at 06:35
  • Amazing, thank you Sourabh. Links to an example quiz and a Sheet of the ideal output are below: https://docs.google.com/spreadsheets/d/16xNP3802dnKYmU6VqStKnvYkTNf7a3UKrGxSRe8yiXA/edit#gid=0 https://docs.google.com/forms/d/16Hkr9le3bdl7X_y5XKUtoo2AC5Y6R5MVdfgrUGyYlmU/edit – L Nicol Oct 29 '19 at 19:08
  • Thanks for sharing these but I think you already have one of the answers that should do the trick. Feel free to let know should you need more assistance. – Sourabh Choraria Oct 30 '19 at 04:57
  • That was a copy and paste manual job to show what I was hoping to achieve :) – L Nicol Oct 30 '19 at 10:37

4 Answers4

9

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:

Community
  • 1
  • 1
alberto vielma
  • 2,302
  • 2
  • 8
  • 15
  • How would you expand this for other types of questions? I guess you would insert some else clause but where? – Tea Tree Feb 17 '20 at 21:25
  • @TeaTree, you could open a new question, in that way you could explain the result you want to obtain and it will be easier for me (or other people) to help you. – alberto vielma Feb 18 '20 at 09:45
1

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.

Dante Bazaldua
  • 19
  • 4
0

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/

Rami
  • 490
  • 7
  • 22
  • Correct - but I can't find an add-on or even the beginnings of a script to create the solution. Will keep searching - thanks – L Nicol Oct 26 '19 at 15:50
  • Well, I can build one as can many developers and people charge money for these scripts/add-ons. There likely isn't one that is out there that solves your problem. I've created add-ons that back up Gmail attachments to Google-docs, migrate sheet data to Gmail, etc. They're a good amount of effort – Rami Oct 26 '19 at 15:53
  • 1
    That's fair enough - thank you. We work in education so there's no chance that we could clear the budget to have someone make a solution. Just trying to get something up and running myself on a small scale. Thank you. – L Nicol Oct 26 '19 at 16:02
  • Can you click the checkbox under the up and down arrows for this question to mark it as the correct answer? – Rami Oct 26 '19 at 16:04
0

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 ++;
  }
    
}
NickB
  • 349
  • 4
  • 15