BlitzTrials Spreadsheet Results Generator

Create a study, generate its MAGIC submission link, collect responses into a hidden Raw tab, and automatically produce a clean processed View — all from one copied Google Sheet.

Your study data remains under your Google account

The official spreadsheet template contains the BlitzTrials Apps Script. When you make a copy, the script runs under the Google account that authorises that copy.

Response and optional Info data are processed inside your copied spreadsheet.

The study installer creates or reuses the specific Google Form registered for that managed study.

The Results Generator does not send participant responses or Info-tab data to BlitzTrials.

BlitzTrials does not collect or store the spreadsheet data processed by this Apps Script.

More information about Google permissions

The managed-study installer can create a Google Form, generate its prefilled MAGIC link, link that Form to this spreadsheet, and optionally configure “Anyone with the link” responder access.

This means Google may request Forms and responder-permission access in addition to spreadsheet access. The Results Generator is designed around the study resources created or registered by this setup workflow.

Step 1: Copy the official spreadsheet template

Make your own copy of the official BlitzTrials spreadsheet. The Results Generator, managed-study installer and helper code are already included, so normal users do not need to paste Apps Script code manually.

Copy to My Drive Preview Template

Do not paste a second copy of the Apps Script into the template.

The normal workflow

For a new data collection, you no longer need to create and link a Google Form manually. Use the built-in study installer.

Add studyEnter the BlitzTrials study/deployment name.

Form createdMagical ID and Magical Result fields are configured automatically.

MAGIC linkA prefilled URL containing MAGIC_ID and MAGIC_RESULT is generated.

Raw + ViewThe response tab is hidden and the processed View is created.

Collect dataPaste the MAGIC link into the BlitzTrials deployment.

Step 2: Add a new study

Open your copied BlitzTrials spreadsheet and wait for the BlitzTrials menu to appear.
Select BlitzTrials → ➕ Add / Repair Study.
Choose ➕ Add new study.
Enter the Study name. Use the same study/deployment name that BlitzTrials places at the beginning of the Magical ID.
Optionally enter a local description.
Leave Configure “Anyone with the link” as a Form responder ticked when public link-based submission is allowed by your Google account or organisation.
Select Create / repair study.

Example study name: CODE Trial Post-test Group A Expected Magical ID from BlitzTrials: CODE Trial Post-test Group A | CODE001 | Group A | CONSENT: GIVEN | ...

The installer builds the submission endpoint for you

The managed Google Form contains exactly two response fields: Magical ID and Magical Result. The generated prefilled URL contains MAGIC_ID and MAGIC_RESULT, ready to copy into the BlitzTrials Deployment Combiner.

Step 3: Follow the installation progress

The setup window runs five separate stages. Each completed stage is saved so a later failure can be repaired without unnecessarily creating another Form.

Prepare study registryCreates or loads the hidden managed-study record.

Create or validate Google Form and MAGIC URLCreates/reuses the two-field Form and generates the prefilled submission link.

Link and hide Raw response sheetLinks the Form to this spreadsheet, identifies the response tab, renames it and hides it.

Create processed ViewBuilds the visible study-specific View using the current field selections from BlitzTrials Setup.

Validate and refresh SetupChecks the managed study and displays its MAGIC link beside the study name.

If setup stops part way through

Open BlitzTrials → ➕ Add / Repair Study, choose 🛠 Repair / resume existing study, select the study and run the installer again. Completed work is reused where possible.

Step 4: Copy the MAGIC submission link into BlitzTrials

When setup finishes, the installer displays the generated MAGIC link with a Copy link button. The same link is also shown in column C of the BlitzTrials Setup tab beside the managed study.

Include	Detected Study	MAGIC Data Collection Link
☑	CODE Trial Post-test Group A	`https://docs.google.com/forms/...MAGIC_ID...MAGIC_RESULT...`
☐	CODE Trial Post-test Group B	`https://docs.google.com/forms/...`

Copy the MAGIC link from the installer or from column C of BlitzTrials Setup.
Open the matching study/deployment in BlitzTrials.
Paste the URL into the Pre-filled Submission Endpoint URL field.
Confirm that BlitzTrials detects both MAGIC_ID and MAGIC_RESULT.
Save the deployment and submit one dummy test before starting live data collection.

About the managed Google Form

For reliable data collection, keep the two managed fields — Magical ID and Magical Result — unchanged. The generated MAGIC link is connected to those Form items.

Step 5: Understand the Raw and View tabs

Hidden

Study Raw tab

Stores the direct Google Form responses. The managed workflow keeps the expected structure: Timestamp in column A, Magical ID in column B and Magical Result in column C.

Visible

Study View tab

Displays the processed study-specific table generated from the Raw data using the field selections in BlitzTrials Setup.

Example managed study: CODE Trial Post-test Group A Automatically created: CODE Trial Post-test Group A Raw ← hidden response data CODE Trial Post-test Group A View ← processed reporting table

Step 6: Change how your processed data is displayed

The automated workflow does not remove the advanced Results Generator controls. Open BlitzTrials Setup whenever you want to change the columns shown in the processed View.

Select studies

Tick one or more managed or legacy studies. Use Select All Studies to include all listed studies.

Magical ID fields

Choose Study, Participant ID, Group, Consent Status, Consent Date/Time or the complete original Magical ID.

Magical Result fields

Choose Total Marks, Total Time, Score Breakdown, Question Choice, Question Time, Question Points, Bank Identifiers or the original result.

Optional Info fields

Choose Participant Name, Hospital Number, Study Group or any other headings detected from your local Info tab.

After changing the selections, tick Generate Selected Tabs or use BlitzTrials → Generate Selected Study Tabs. For a managed study, the script automatically uses that study's dedicated Raw tab as the data source.

Advanced settings for older or manually linked studies

New managed studies already know their own Raw response tab and use Magical ID in column B and Magical Result in column C automatically.

The Response Tab, Subject ID Column and Result Column settings remain available mainly for legacy or manually linked studies.

Step 7: Optional local participant information

The Info tab allows you to match a coded Participant ID to information that you keep locally inside your own spreadsheet.

Participant ID	Participant Name	Hospital Number	Study Group	Additional Information
CODE001	Participant A	H12345	Group A	Optional notes
CODE002	Participant B	H12346	Group B

Column A must contain the same Participant ID used inside the Magical ID.

Magical ID: CODE Trial Post-test Group A | CODE001 | Group A └── Participant ID Info tab: A2 = CODE001

Anonymous studies

Leave all Info fields unticked when participant names or local identifiers are not required.

Repair or resume an existing managed study

Use Repair / resume when setup was interrupted, when you want to validate a managed study again, or when its processed View needs to be rebuilt.

Select BlitzTrials → ➕ Add / Repair Study.
Choose 🛠 Repair / resume existing study.
Select the study from the dropdown.
Review the responder-access option.
Select Create / repair study.

The installer reuses the registered Form and existing study configuration where possible instead of deliberately creating a new study from scratch.

Existing or legacy studies

The Results Generator still supports studies collected through an older shared response sheet or a manually linked response tab.

Open BlitzTrials Setup.
Select the appropriate Response Tab.
Confirm the Magical ID and Magical Result column letters, usually B and C.
Tick Refresh Lists.
Select the detected study and required output fields.
Tick Generate Selected Tabs.

Existing manual tabs remain protected

The generator identifies the result tabs that it created. It does not intentionally overwrite an unrelated manually created tab that does not contain the BlitzTrials generated-results marker.

Troubleshooting

The BlitzTrials menu does not appear

Reload the spreadsheet and wait several seconds. If the menu still does not appear, open Extensions → Apps Script, select onOpen, run it once, then return to the spreadsheet and reload. This is a troubleshooting step only.

Google asks for more permissions than before

This can occur because the managed-study version creates or reuses Google Forms, links them to the spreadsheet and can configure responder access. Use the Google account that should own or manage the study resources.

Setup stops during one of the five stages

Read the stage-specific error shown in the installer. Then reopen Add / Repair Study, choose Repair / resume existing study and run the same study again.

The study exists but no responses have been submitted yet

A managed study can still appear in BlitzTrials Setup because it is recorded in the hidden managed-study registry. You do not need to wait for the first participant response before copying the MAGIC link.

The processed View is empty

Submit one dummy response and confirm that the hidden Raw tab receives a Magical ID in column B and a Magical Result in column C. Also confirm that the study name at the start of the Magical ID matches the study/deployment name you configured in BlitzTrials.

Participant information shows NA

Confirm that the Participant ID in column A of the Info tab exactly matches the Participant ID inside the Magical ID. Remove accidental spaces before or after the identifier.

My organisation blocks “Anyone with the link”

Run Repair / resume existing study with the public responder option unticked, then use the responder-access policy permitted by your Google Workspace administrator.

I changed which result fields I want

Change the checkboxes in BlitzTrials Setup, then tick Generate Selected Tabs. The processed View will be rebuilt using the current selections.

Privacy and access summary

Your study resources remain in your Google environment

Response data and optional Info-tab data are processed in the copied spreadsheet, and the Results Generator does not send those data to BlitzTrials.

Technical privacy and access details

The built-in Apps Script runs under the Google account that authorises the copied spreadsheet.

The managed-study installer can create or reopen the Google Form registered for a study, link it to the copied spreadsheet, and configure that Form's responder permission when requested.

BlitzTrials does not collect or store the spreadsheet data processed by this Apps Script. The spreadsheet and associated Google resources remain subject to the user's or institution's Google account and sharing controls.

Keep Editor access limited to authorised study personnel, especially when the optional Info tab contains participant-identifying information. Use your institution's approved Google Workspace sharing and retention policies.