I’m doing rental for machine. Before we deliver to our client, we have to do some pre-delivery inspection to make sure the machine is in good condition and as a proof that we check it.
But there’s 76 questions in total for four groups of inspection.
Every question can be answer in either, ✓, ✕ or NA
But for the summary, i need Progress Percentage and also Condition Percentage.
Eg: the question : “Check High Pressure Hydraulic Filter Condition”
Using the if then column for each question.
if the answer ✓ = Progress Percentage is Counted as 1, Condition Percentage is counted as 1 as well,
if the answer ✕ = Progress Percentage is Counted as 1, Condition Percentage is counted as 0.
So in total i have to have 76 x 3 = 228 columns basically ? it is correct?
My question is is there any shorter ways?
Much better to have 76 rows, and present as an Inline List/Collection.
Do you need to save the results of every question, or just the final outcome?
I need to save results of every questions. Oooo, Inline list?
Yeah. I’d use a Helper Table and roll the results up into a JSON string, then store that in a single column.
What i think is , before this
Report No | Q1 | Q2 | Q3 … Q76 | Progress Percentage | Condition Progress
but if it in Inline List, how to relate it with the each report No and the results?
No, you would have something like this:
[ "Question1", "Answer1" ],
[ "Question2", "Answer2" ],
[ "Question3", "Answer3" ],
[ "Question76", "Answer76"]
And that would just be saved in a single column.
Then you use a 76 row Helper Table to manipulate it.
It’s a very effective and efficient technique, but quite complicated to explain.
hmm do you have any sample the apps that using helper table ? oh but, it will not appear in Google sheet right?
because now i am using google sheet formulated with the answer, in one sheet, and using Apps script to convert to PDF and directly send to user,
The JSON would be in the Google Sheet, yes. So you could process that with Apps Script.
I think I posted a longer explanation of how this technique works a few months ago. I’ll see if I can find it.
That’s not the one I was thinking of, but yes that thread will help you understand the Helper Table concept.
Actually, this is a fairly simple use case, and so JSON might be overkill. I guess it depends on whether or not the list of questions would ever change. If they won’t, then all you really need to save is the answers.
So what you could do is create a Questions table, and list all your questions in one column. Then add a User Specific column to capture the answers. Then use a joined list column to create a joined list of all the answers, and save that.
Do you know which apps in the Glide template that is using this Helper Table?
No, I’m not aware of any.
Are you using Classic Apps or new Apps?
I have to make something like :
One sheet 74 rows with questions.
Another one sheet, single row ?
It is ?
Give me a little time, I’ll make an example for you.
Omg Darren . Best explanation! Meaning if submit button the answer will go to another new sheet right, can edit the answer as well right ?
Yes, it would be possible to come back and edit the answers later. Although the logic for that would be a little different. Basically, you’d need to load the RowID of the submitted answer, use a relation + lookup to fetch the joined list of answers, use a split text + single value to expand the answers across all rows, and then go from there.
if I need to edit the answer later, i have to set the answer back to the Answer Column from the Single Value right?
But i cannot find a way to set all Single Value Column to the Answer Column.
Because of course we edit from the sheet “Log”