@jhans
Canvas cannot do this on its own, you would need to download extra information and process it. That can be written into a script to automate the process.
For Classic Quizzes, you can generate a Student Analysis report that contains every question a student was asked and the points they received on the question. Unfortunately, from the Excel file, there is no way to know which question goes with which chapter. It does have the text of the question, but that is unlikely to identify the chapter. It also has the question ID, but the order of the questions is in the order they were answered. That might work if there is no randomization or bank usage and the first student answered all of the questions. If you use a bank or group of questions, then it's going to appear in a random order.
For New Quizzes, there is also a Student Analysis report. I only use New Quizzes for testing purposes with a sandbox that has one student in it. Canvas won't generate the report until at least three students have completed the quiz. That would break one use of the report I have where I have small classes (I've had 2 students before) but need the report to copy/paste their answers to check them. I did find another instructor who uses New Quizzes and generated the report for one of his classes. There are now four columns for each question rather than 2. The Question ID is a separate column rather than being combined with the text of the question.
You would need to the Canvas REST API to get the question IDs and question order to make sense out of the report if you had any kind of question banks. That may eliminate getting the information out of New Quizzes as the support for those is nowhere near the level it is for Classic Quizzes. The rest of this message will only apply to Classic Quizzes.
For Classic Quizzes, you could bypass the Excel file and go straight to the Canvas API. From there you can get the question IDs and question positions so you know which question goes with which chapter. It also gives the student response and points received. If you fetch the submission records and include the submission_history, it includes the submission data for the quiz.
You can pull this information out of Canvas Data 2 (CD2). CD2 is a database with a lot of the information in Canvas so it's usually reserved for a few people at the institution. But if you or someone at your institution has access, the quiz_submissions table has a submission_data column. That column has the same information that is available through the API (which requires programming, but would be available to instructors).
Whether from the API or Canvas Data, each question has whether the question is correct (true/false), the points received, the question_id, the answer_id, and the text of the question (this might be the answer ID for some question types). This is stored in a JSON object as an array. I believe the order of the array is the same as the order the questions were asked, but you would want to verify that (my question IDs are not in increasing order, so if there is an order, it's probably the order the questions were given).
It sounds like you're an instructor and want the information to give back to the students fairly quickly. That would suggest going the API route since you wouldn't have to rely on other people to run the reports for you. There is also a delay with CD2 data between when the student takes the quiz when it is available in CD2. You still have to refresh the CD2 data before you can use it. Depending on how often the data is updated, it's usefulness for your need may be limited. If you were part of a multi-faculty team who was looking at improving course delivery and assessment and wanted to go back and look across multiple quizzes and courses -- but didn't need the information until the course was over -- then CD2 would be a great solution. If you are at an institution like that, you probably have an institutional research team that could help with those things. But CD2 has significant overhead to install and is not so great for near real-time needs.
