Choose an edition below: copy Basic for separate study result tables, or copy Advanced Pre/Post for paired and longitudinal reporting across multiple assessments.
The Basic template is suitable when each assessment can be reviewed as its own result table. The Advanced edition is designed for research, workshops and conferences that follow the same participants across several timepoints.
The spreadsheet copied from this page.
A separate edition based on the latest Apps Script workflow.
The Basic template can create separate Pre and Post study Views, but it does not automatically combine them into one longitudinal participant record. Use the Advanced edition when paired or multi-timepoint analysis is required.
The Basic tutorial appears first and the Advanced tutorial follows it. These are two alternative editions—not two installation stages. Choose one template for your live study and do not register the same live assessment in both spreadsheets.
Follow these five steps in order if you selected the Basic template. The Basic edition creates an independent result View for each assessment.
The Apps Script is already attached to the official template. Adding a second copy of the same functions can cause duplicate-function errors.
The installer creates a managed Form with two required questions: Magical ID and Magical Result. It then produces a prefilled link containing MAGIC_ID and MAGIC_RESULT.
BlitzTrials Setup.Always use non-live test data before inviting participants.
BlitzTrials Setup.Contains the direct Google Form response data. Do not delete, rename or manually restructure a managed Raw tab.
Displays the selected participant ID, score and question fields in a cleaner study-specific table.
| Assessment | Automatically created | Basic-edition output |
|---|---|---|
| Diabetes Workshop Pre | Pre Raw and Pre View | Separate Pre result table |
| Diabetes Workshop Post 1 | Post 1 Raw and Post 1 View | Separate Post 1 result table |
That is an Advanced-edition function. It matches selected assessment records and displays completion, missing-data and duplicate-attempt indicators without silently discarding unmatched participants.
Use these options after the first test submission has reached the correct Raw tab and you are ready to refine the study-specific View.
Participant ID, study, group, consent status, consent time or the original Magical ID.
Total marks, total time, score breakdown, question choices, question time and question points.
Locally maintained details can be matched through the Participant ID in column A of the Info tab.
Leave all optional Info fields unticked when names and local identifiers are not required.
Repair reuses the registered Form and saved study configuration where possible. It does not require you to type the locked assessment name again.
Email collection and Google Forms’ one-response limit remain controlled by the Form owner.
Your Basic setup is complete. Continue to the Advanced tutorial below only if you need paired or multi-timepoint participant matching. Use the separate Advanced spreadsheet for that workflow.
The Advanced template is intended for research studies, courses, conferences and workshops that collect results from the same participant at more than one timepoint. It uses the same Form and MAGIC-link principles explained in the Basic tutorial, then adds participant matching and combined reports.
Do not add the same live study to both editions. If you need combined reporting, copy the Advanced template and create all related timepoints inside that Advanced spreadsheet.
Create Pre, Post 1, Post 2 and later assessments separately using Add new study. Use Repair / resume only when restoring the same assessment, Form and link.
Open Extensions → Apps Script. Under Services, select +, add Drive API, save the project and run setup again. This is normally required only if the copied project did not retain the supplied service configuration.
Use the same research title followed by a clear timepoint. The Advanced report recognises Pre, Baseline, Post 1, Post 2 and Follow-up 1 when arranging assessment order.
Pre and Post are separate data collections. Giving each timepoint its own name, managed Form and MAGIC link makes the reporting order clear and prevents the wrong link from being placed in a deployment.
| Matching method | Suitable for | Requirement |
|---|---|---|
| Participant ID | Hidden or visible submission, coded research IDs and single-use tickets | The same participant must use exactly the same ID at every timepoint. |
| Google Verified Email | Visible Google Forms where respondents can sign in | The Form owner must manually enable verified email collection. Hidden submission cannot provide a verified Google email. |
Use one matching method consistently throughout the same longitudinal study. Changing from Participant ID to email between timepoints will prevent reliable matching.
Repeat the same process using Add new study for every new timepoint. Each installation produces its own resources:
| Timepoint | Managed resources | Link destination |
|---|---|---|
| Pre | Pre Form, Raw tab, View tab and MAGIC link | Pre deployment |
| Post 1 | Post 1 Form, Raw tab, View tab and MAGIC link | Post 1 deployment |
| Post 2 | Post 2 Form, Raw tab, View tab and MAGIC link | Post 2 deployment |
Add new study creates another assessment and another link. Repair / resume reuses the selected assessment’s existing Form and link; it does not create Post 1 or Post 2.
BlitzTrials Setup and select the assessment and output fields you want.Use a paired report when you want to compare two timepoints directly.
-.Use a longitudinal report when the study contains two or more selected assessments.
BlitzTrials Setup, tick only the related assessments, such as Pre, Post 1 and Post 2.The participant has one matching record for every selected assessment.
One or more selected assessments are missing. Unavailable fields display -.
More than one attempt was found for the same participant and assessment. Records remain visible for review.
The matching identifier was empty or unusable and must be reviewed manually.
A participant who completes Post but misses Pre remains visible in the combined report. The unavailable Pre values display -; the participant is not silently discarded and missing data is not converted to zero.
-99; this is separate from the report display dash.The template contains a spreadsheet-bound Google Apps Script. After you make a copy and authorise it, the reporting workflow runs under the Google account that owns or has permission to use that copy.
The script needs permission to work with the spreadsheet and to create, open or link the managed Google Form. Google may also request Drive permission when the installer is asked to support “Anyone with the link” responder access.
Only authorise the template using the Google account that should own or manage the study resources.
Reload the spreadsheet and wait several seconds. If necessary, open Extensions → Apps Script, run onOpen once, then return to the spreadsheet and reload.
Submit one dummy response. Confirm that the Raw tab contains both Magical ID and Magical Result values and that the matching assessment is selected in BlitzTrials Setup.
Reopen Add / Repair Study, select Repair / resume, choose the assessment and run it again.
Check that column A of the Info tab contains exactly the same Participant ID as the second segment of the Magical ID.
The Basic edition generates separate study Views. A combined paired or longitudinal table requires the separate Advanced Pre/Post edition.
Use the responder-access policy permitted by your Google Workspace administrator. Repair the study with the public-access option unticked when appropriate.
Keep Editor access limited to authorised personnel, particularly when the optional Info tab contains identifying information. Follow your organisation’s approved Google Workspace sharing, retention and research-data policies.