Would this be the right approach for an Audit Checklist app?

I have an Audit Checklist that I’m trying to make an app for.
User will be entering the Date of Audit / Location of Audit and then around 30 Questions.
Each Question has a Score & Remarks that need to be entered.

I can think of 2 approaches to this;

  1. Using one single form and one sheet :
    This Form would have the Date of Audit / Location of Audit and All 30 Questions + Score + Remarks field. This would be tedious as I can’t use a repeating section or such and also I’ll have to maintain multiple columns on the sheet.

  2. Using one form, 2 sheets & trigger :
    Next option would be to have a Form (sheet) with Date of Audit & Location of Audit. On Submit, the row addition in the backend sheet would trigger entries for all 30 questions for that date (Date/Question/Score/Remarks) in another sheet. Then I can show these entries as Inline List. However, how will I filter the Inline List for the specific date so that I update only those records for the specific date?

Any other approach? Appreciate any suggestions.


1 Like

My quick thought would be to use 2 sheets, as in your number 2. But if the 30 questions remain relatively fixed a single sheet with one row per audit would make it easier to do. However with 30 questions, plus 30 Scores, plus 30 Remarks, plus the basic info about the audit, that single sheet with 90 plus columns may get out of hand. So I’m leaning back toward 2 sheets maybe even 3, the 3rd for the answers to the questions. I would try laying it out with 3 questions to start with and see how it goes. If it’s a somewhat internal app, which it sounds like, you could come up with a naming convention for each element. Each audit would have a user input unique ID based on the date. Something like 20190927-1 (first audit of today). Each question is pretty easy as they would be numbered 1 through 30. The answer sheet would have a link to the audit and the question. The links would look like 20190927-1-1, 20190927-1-2, 20190927-1-3, etc. That is how the answers would be linked to the specific Audit and the specific question. Looks like I’m recommending a 3 sheet structure, lol.


Thanks George! I like the idea of the unique Audit-Date-User option. Gives me something to think about!