BLITZTRIALS ASSESSMENT TUTORIAL

A Definitive Guide to Automating Clinical Data Processing. Transforming raw clinical assessment data into structured insights through advanced Apps Script automation.

Phase 1: Data Connectivity Hub

Objective: Establishing a secure, live pipeline between your collection portal and the processing spreadsheet.

Open your primary Google Form where assessments are conducted.
Navigate to the 'Responses' tab located at the top center of your screen.
Locate and click the green 'Link to Sheets' icon on the right side.
Choose "Select existing spreadsheet" from the popup dialog box.
It is vital to select THIS specific spreadsheet. The BLITZ_SPLIT automation script is stored directly within this file.
Confirm the new tab "Form Responses 1" has successfully appeared.

Phase 2: Workspace Initialization

Objective: Establishing the internal processing engine that powers automated data extraction across all assessment banks.

Locate the Plus icon (+) at the bottom left of your Google Sheet.
Double-click the new tab name and rename it to "Clean Data".
Ensure the sheet is completely blank to avoid any data collisions during extraction.
Every automated table begins at the very top-left. Select Cell A1 as your starting point. This ensures all programmatic headers and horizontal columns align perfectly with the grid architecture.

Command Execution

Initialize the Engine: Copy and paste the core command into the formula bar of Cell A1, then press Enter to initialize the extraction engine.

=BLITZ_SPLIT('Form Responses 1'!B2:C)

Note: Make sure to copy the entire formula exactly as shown above, including the equals sign and all punctuation.

Phase 3: Customizing Data Insights

Objective: Tailoring the BLITZ_SPLIT output to meet specific audit requirements and reporting needs. The formula accepts two optional instructions to filter the vertical columns and horizontal metrics. All instructions (ID Parts and Metrics) must be wrapped in double quotes and separated by a comma.

View Name	What it Shows (Best For...)	Formula to Copy & Paste
The "Just the Answers" View	Shows only the Participant ID and the Answers they selected.	`=BLITZ_SPLIT('Form Responses 1'!B2:C, "2", "Choice")`
The "Grader" View	Shows Participant ID, Group, and Points earned per question.	`=BLITZ_SPLIT('Form Responses 1'!B2:C, "2, 3", "Pts")`
The "Time Audit" View	Shows Participant ID and seconds spent on each question.	`=BLITZ_SPLIT('Form Responses 1'!B2:C, "2", "Time")`
The "Answers & Scores" View	Shows Assessment Name, ID, chosen Answers, and Points.	`=BLITZ_SPLIT('Form Responses 1'!B2:C, "1, 2", "Choice, Pts")`
The "Full Data Backup" View	Extracts absolutely everything: All ID parts, Choices, Points, and Time.	`=BLITZ_SPLIT('Form Responses 1'!B2:C, "1, 2, 3", "Choice, Pts, Time")`

Phase 4 (Advanced): Dynamic Bank Management

Structural Integrity

We decouple raw input from processed output. By working on a new tab, you ensure the original Google Form data remains untouched and auditable at all times.

Real-Time Sync

The "Clean Data" sheet acts as a live mirror. Every new participant submission triggers an immediate update in your processed table, zero manual refreshes needed.

Seamless Expansion

The logic scans every row before rendering. It automatically stretches your spreadsheet horizontally to accommodate every bank used across your study without manual column creation.

Logical Separation

Banks are processed in sequence. Question 1 of Bank A will never overwrite Question 1 of Bank B, maintaining absolute data integrity across complex assessments.

Phase 5: Local Patient De-Anonymization

Objective: Securely match anonymous Subject IDs back to real patient names offline, ensuring Protected Health Information (PHI) never touches the web application.

Create an "Info" Tab: In your Google Sheet, click the Plus icon (+) at the bottom left to create a new tab. Rename this tab exactly to Info.
Build your Master List: In the new Info tab, list your anonymous Subject IDs in Column A and the real Patient Names in Column B.
Prepare the Data Tab: Go back to your Clean Data tab (where your BLITZ_SPLIT formula is running). Create a new column header in row 1 called Patient Name.
Inject the Auto-Formula: Click the empty cell directly below your new header (e.g., Row 2) and paste the exact formula below.

=ARRAYFORMULA(IF(B2:B="", "", VLOOKUP(B2:B, Info!A:B, 2, FALSE)))

How this works

The ARRAYFORMULA command runs infinitely down the entire column. The moment a new anonymous assessment is processed by BLITZ_SPLIT, this formula instantly cross-references the ID with your private Info tab and reveals the patient's real name automatically. Note: If your Subject ID is not located in Column B on the Clean Data sheet, simply change the B2:B in the formula to match your actual ID column letter.

Appendix: Resilience & Best Practices

Loading State

Standard behavior while processing large datasets. Allow 3–10 seconds for completion without refreshing.

#REF! Error

Formula path is blocked. Delete any text in the cells below or to the right of A1 to allow expansion.

Missing Data

Verify the data range in the formula. Avoid row caps (like B2:C50) to ensure unlimited vertical growth.

Integrity Checklist

Always maintain a Master Copy of the spreadsheet before editing the core script, keep raw form responses on an isolated tab, and only allow "Editor" access to trusted personnel to protect the processing logic.