I’ve been thinking about this and I think I have some follow up questions.
- Are you planning to present all of the questions and criteria on one page, or would there be one page per question?
- Are you planning to use a form to get the answers, or would it be some sort of detail screen?
- Will one company be filling this out at a time, or could multiple companies be filling out the answers at once?
- Do all of the answers need to be accessible to you, or are all of the answers used to calculate a final result?
- Would a user ever fill out the questionnaire again, or is this a one time only thing?
- Can answers be changed at a later time, or do they only have one chance to answer the question?
Due to the large number of questions, I think in my opinion the first thing I would do it is to have a sheet with all of the questions listed in rows along with a RowID, Topic, and Question Number. Then I would create columns to indicate which type of criteria choices to display. This can be used to filter a list of choices that can be reused among all criteria without using up too many rows. Then I would probably create user specific columns to hold the answers and the comments. So it would look something like this.
| Row ID | Topic | Question NumberQuestion | Criteria 1 Type | Criteria 1 Answer | Criteria 2 Type | Criteria 2 Answer | Criteria 3 Type | Criteria 3 Answer | Criteria 4 Type | Criteria 4 Answer | Criteria 5 Type | Criteria 5 Answer | Criteria 6 Type | Criteria 6 Answer | Comments |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 12345 | Topic 1 | 1 | Question1 | YN | **** | HML | **** | 15 | **** | TF | **** | YN | **** | TF | **** |
The Choices sheet would look something like this:
| Criteria Type | Choice |
|---|---|
| YN | Yes |
| YN | No |
| TF | True |
| TF | False |
| 15 | 1 |
| 15 | 2 |
| 15 | 3 |
| 15 | 4 |
| 15 | 5 |
| … |
What I would do is probably have a tab that shows a list of questions. A user can select a question to view it on a details screen and begin answering the criteria. Once all the required fields are filled, you could use visibility to display a Save or Submit button. This button will be tied to a compound action that will write the user email, rowid, topic, question, and answers to a new sheet through the Add Row Action, then you will clear all of the user specific columns with a Set Columns action if you choose.
At this point it gets a little to decide how you would want to navigate from question to question and there are quite a few ways to handle that. What I would probably do is add some extra user specific columns to your questions sheet. One to hold a value that you can increment to navigate through all of your questions, another to use that increment value to create a single relation to the corresponding question, and then a series of lookup columns to get the rowid, topic, question, criteria, etc. You would display the lookup columns and also use them for for displaying each question on the screen. Once a user answers the question, a third action on the submit button would be to increment the user specific value, which updates the relation, and then shows the next question.
This is most likely how I would set most of it up. I’m sure there would be some issues along the way, but I think it would be pretty easy to work through it. I think in the end, it will save you the most rows and get result added to a separate sheet for analysis. I hope this gives you some ideas.