Duplicate questions in microsoft forms answers downloaded in excel

Question

I have a rate colour form where the user has to rate colours based on their liking and their mother's liking.

This is my form

But the problem is when I download the response here in Excel the questions are not coming.

Here it is coming as Red, Red2 if I have 100 of these questions for each family member and friend then this response won't give me any idea of what is the response for whom.

Is there any way to identify that? Or is it like for each question I will have to add whose response we are asking for?

Answer 1

According to https://techcommunity.microsoft.com/t5/microsoft-forms/how-can-i-save-a-microsoft-form-response-with-questions/m-p/145463 it isn't possible to include the questions in the Excel export. Of course you can edit the Excel sheet afterwards and put the questions in the first row yourself.

You can automate this to a large extend by exporting the titles from the DevTools in the browser. For example in Firefox, from the Preview pane in Microsoft Forms, you can right-click on a question title and choose Inspect (Q) from the context menu to be taken to the html source of this element:

Here you can see that the title is in a <span class="text-format-content "> inside a <span data-automation-id="questionTitle">. You can go to the console (2nd tab in the DevTools) and write an XPath query for this:

$x('//span[@data-automation-id="questionTitle"]/span[@class="text-format-content "]/text()')

Result:

Now you can add a bit of Javascript around it to change the output format:

'"'.concat($x('//span[@data-automation-id="questionTitle"]/span[@class="text-format-content "]/text()').map(function(e) { return e.data; }).join('",,"'),'"'); 

This snippet takes the .data property of each element of the array that is returned by the XPath query (returning a new array with only the titles as strings), then joins them by puts the string ",," in between each element, and puts an additional " before and after the resulting string. This leads to the following output:

"Rate colours based on your liking",,"Rate colours based on your mom's liking"

Now you can paste this into Excel, by first adding an empty row, then pasting it normally, then from the Ctrl ⌄ menu select Text to Columns and choose Comma as the (only) delimiter. Note that if you have more than two options per question then you need to join with more commas, for example .join('",,,,"') for four options.