I’m working on a questionnaire with multiple choice, and the results of that choice need to be shown on a separate results table that is then added up to give a total score.
The problem I’m having is that I need to be able to take the value of a single cell on one table and have it replicated in another table.
Since the results are user-specific, they don’t copy back to the Google spreadsheet, so I need to do all the calculations in the glide table.
Hope this all makes sense, and sorry if I’ve missed something simple, but it’s driving me mad not be able to either find a solution and see what might be a simple solution to my problem.
Can you explain what the rows and columns in your Results table represent?
If you need anyone other than the user completing the questionnaire to see their results, then you will need an action that writes the results to a non-user specific column.
That should be easy enough to setup, we just need a little more information, I think.
The value of that choice, be it either 1 or 0 needs to be added to 1 of 4 columns on the results table. Each of the columns at the end of the questionnaire are then totalled up to give a final score per column. This then will populate a graph only the user can see.
The whole thing is a profile test based on their choices.
Just want to make sure my understanding is correct.
It looks like you have 24 sets of questions in total - does that mean that each row in your Results table represents one set of 4 questions?
How are your questions laid out? Are they all in a single table in a single row? Or…?
Just looking at your last screenshot, it appears that there would be nothing stopping a user from selecting both most and least for all 4 questions - would that be a valid response?
Apologies for all the questions - I’m just trying to get a clear picture.
Yes there are 24 sets of questions, with 4 different profile descriptions, they can only choose 2 descriptions, one that’s most like them and the other least. The button that takes them to the next question has an action assigned so it only shows when one of each is selected. (this should stop the user from selecting more than the two required responses)
Each set of questions are on their own table, so there will be 24 tables for the questions. See first post image which shows how the inputs from the user are recorded.
The results table then looks at the responses and is populated. For example on the results table, column ‘D’ cell 1 looks at the question table column ‘most’ cell 3 for a value.
Ouch, I suspected that might be the case. Okay, you’ve created an awful lot of work for yourself there. Essentially what you will need to do is create a Single Value column in each of those 24 tables that uses “N from start” to create a link to the appropriate row in your Results table. You should then be able to use a Set Column Values action through that column to set the appropriate values in the Results table as each set of answers is submitted.
This would be way way easier if you had all of your questions in a single table. In fact, you probably wouldn’t even need your Results table. Not to mention the fact that you’d only have to configure one screen for your questions - instead of 24
Thanks for the response, not 100% sure what you mean by “N from start” and then how to use a Set Column Values action.
I’d hoped it would an easy thing to do, as to have one cell on one table look at a cell on another table for the value. Obviously not as easy as I’d thought, having done this in Excel I assumed the process would be similar. Thanks for your help, this is one project that’s probably not going to happen.
If you have a look at the docs for the Single Value column, you’ll see that one of the options is to take “X from Start”. So for example, if X was 3, then it would take the 4th row from the start (it starts at zero).
So to apply that to your scenario, lets assume that Table 1 corresponds to the first row in your Results table. Your Single Value column in Table 1 would take the first row from the Results table. In Table 2, you’d take 1 from the start (Row 2), in Table 3 it would be 2 from start (Row 3), and so on.
Each of these Single Value columns would establish a link between your questions tables and the corresponding rows in your Results table.
It’s not particularly difficult, it’s just different from working with spreadsheets.
But as I mentioned earlier, this approach is creating a lot more work than is really necessary. What you are trying to do here is actually quite simple - it’s just that the way that you’ve arranged your data is making it a lot harder than it otherwise would be.
What I would recommend here is to combine all of the questions into a single table, and include an extra column for the “Section number”. So the first 4 would be section 1, the second 4 section 2, and so on. Then present the questions as a collection and use the section number as a filter. Create a user specific column to store the current section number, and increment that as the user progresses from screen to screen. It would actually just be a single screen, with the contents of the collection changing. Then once the user is finished, you could use a series of rollups to calculate the results. Everything could be done with a single screen and a single table.
