5S Audit – Need a simpler solution to map 25 criteria scores and comments from Google Forms to a formatted Google Sheet

[Sekar_Rajan]

What is your goal?

We are building a Make.com automation for a SISCO 5S Audit.

Each Google Form submission contains 25 checklist criteria, with a score and observation/comment for each criterion.

Our goal is to automatically take one submitted audit and populate a pre-formatted Final AUDIT RESULT sheet vertically, with the score and observation/comment placed against the corresponding 25 criteria.

What is the problem & what have you tried?

During our earlier discussion with Make.com support, we understood the approach of using Google Sheets and automation modules to process the submitted audit data.

We subsequently tested several approaches:

1. Google Sheets as the first module, followed by an Iterator.
   This did not give us the required 25 individual Criterion + Score + Comment items.

2. Set Variable → Iterator.
   The Iterator successfully produced the 25 criteria, but we were unable to reliably map the corresponding score and observation/comment as separate fields.

3. Google Sheets → Update a Cell.
   We tested fixed cell destinations and confirmed that this works correctly. For example, a score and comment from one Form response populated C13 and D13 correctly, and another response populated C37 and D37 correctly.

4. We then tried to make the destination cell dynamic using the Iterator Bundle order position, so that the 25 items would automatically go to C13:C37. This failed because the Google Sheets Cell field did not interpret the dynamic formula correctly.

The remaining approach we are considering is an Iterator followed by a Router with 25 routes and potentially 50 Google Sheets Update a Cell modules (one for each score and comment).

However, this appears cumbersome for a production solution and may also be difficult for the customer to maintain.Before we proceed with the Router approach, could you please suggest a simpler and more scalable way to transform the 25 horizontal Form-response fields into the required vertical Criterion + Score + Comment structure and update the formatted result sheet?

We would particularly appreciate your recommendation for a production-ready approach that avoids creating 25 Router branches / 50 Google Sheets update modules, if possible.

We are open to using another Make.com module or transformation method, if that would provide a simpler solution.

Error messages or input/output bundles

The dynamic Cell formula resulted in the Google Sheets Update a Cell module receiving:

Cell: C
Value: 4

and the output showed:

Updated range: ‘Final AUDIT RESULT – DEMO format’!A1

This indicates that the dynamic cell-address formula was not being evaluated correctly.

Screenshots (scenario flow, module settings, errors)

[Stoyan_Vatov]

Hey there,

You absolutely don’t need Make for this. Google forms can be set to store responses directly inside a Google sheet. From there, you just need a formula that syncs the corresponding cells between the two sheets.