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

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.

2,301 26 26 silver badges 65 65 bronze badges asked Oct 26, 2019 at 14:51 129 1 1 gold badge 2 2 silver badges 7 7 bronze badges

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.

Commented Oct 28, 2019 at 6:35

Amazing, thank you Sourabh. Links to an example quiz and a Sheet of the ideal output are below: docs.google.com/spreadsheets/d/… docs.google.com/forms/d/…

Commented Oct 29, 2019 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.

Commented Oct 30, 2019 at 4:57 That was a copy and paste manual job to show what I was hoping to achieve :) Commented Oct 30, 2019 at 10:37

4 Answers 4

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:

1 1 1 silver badge answered Oct 28, 2019 at 12:06 alberto vielma alberto vielma 2,322 2 2 gold badges 9 9 silver badges 16 16 bronze badges

How would you expand this for other types of questions? I guess you would insert some else clause but where?

Commented Feb 17, 2020 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.

Commented Feb 18, 2020 at 9:45

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.

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

Check you have all the permissions set.

answered Jul 27, 2020 at 5:37 Dante Bazaldua Dante Bazaldua 19 6 6 bronze badges

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.

answered Oct 26, 2019 at 15:25 510 7 7 silver badges 23 23 bronze badges

Correct - but I can't find an add-on or even the beginnings of a script to create the solution. Will keep searching - thanks

Commented Oct 26, 2019 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

Commented Oct 26, 2019 at 15:53

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.

Commented Oct 26, 2019 at 16:02

Can you click the checkbox under the up and down arrows for this question to mark it as the correct answer?

Commented Oct 26, 2019 at 16:04

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 ++; > >