Quality inspection (QM) report page

Overview

The Quality Inspection Report page displays quality (QM and pre/post-fix inspections after a complaint) inspection findings to cleaners and allows them to submit feedback/responses. It pulls data from Airtable (QM Reports and Line Items tables) and presents it in a mobile-friendly format.

Live URL format: https://matthewscleaningco.com.au/qm-report?recordId={RECORD_ID}

Architecture

┌─────────────┐     ┌─────────────────────┐     ┌─────────────────────┐     ┌───────────────┐
│   Cleaner   │────▶│  WordPress Proxy    │────▶│  Google Apps Script │────▶│   Airtable    │
│   (Phone)   │◀────│  (WPCode snippet)   │◀────│  (JSON endpoint)    │◀────│ (QM Reports)  │
└─────────────┘     └─────────────────────┘     └─────────────────────┘     └───────────────┘
                              │                           │
                              │      POST feedback        │
                              └──────────────────────────▶│

The page uses a server-side proxy hosted on our WordPress site. This was implemented to work around a Google Apps Script bug that causes pages to fail loading when users are signed into multiple Google accounts.

Components

1. Airtable Formula (QM Reports Table)

The URL is generated via a formula field in the QM Reports table:

"https://matthewscleaningco.com.au/qm-report?recordId=" & RECORD_ID()

Previous formula (deprecated):

"https://script.google.com/macros/s/AKfycbyliKloUEwIcb72aeEpb9oHFtxlYtGkxP9Z88YT27gqMDPF5frGOC3uLkoySAiCmkLq/exec?recordId=" & RECORD_ID()

2. Google Apps Script

Deployment URL: https://script.google.com/macros/s/AKfycbyliKloUEwIcb72aeEpb9oHFtxlYtGkxP9Z88YT27gqMDPF5frGOC3uLkoySAiCmkLq/exec

Location: Google Apps Script project (access via http://script.google.com )

Deployment settings:

• Execute as: Me ( glenn@matthewscleaningco.com.au )
• Who has access: Anyone

Script Properties Required

The script uses the following properties (configured in Project Settings → Script Properties):

Property	Description
AIRTABLE_TOKEN	Airtable API token
AIRTABLE_BASE_ID	Base ID for the Airtable base
AIRTABLE_QM_REPORTS_TABLE	QM Reports table name
AIRTABLE_QM_LINEITEMS_TABLE	QM Line Items table name
AIRTABLE_EMPLOYEES_TABLE	Employees table name
AIRTABLE_FINDINGS_TABLE	Findings table name
FIELD_EMPLOYEE_NAME	Employee name field
FIELD_FINDING_NAME	Finding name field
FIELD_SITE_NUMBER	Site number field
FIELD_SITE_NICKNAME	Site nickname field
FIELD_PREPARED_BY	Prepared by field (linked to Employees)
FIELD_INSPECTION_DATE	Inspection date field
FIELD_QR_CODE_PRESENT	QR code present checkbox
FIELD_CLEANING_EQUIPMENT_PRESENT	Cleaning equipment present checkbox
FIELD_VACUUM_PRESENT	Vacuum present checkbox
FIELD_VACUUM_BAG_OK	Vacuum bag OK checkbox
FIELD_VACUUM_FILTERS_OK	Vacuum filters OK checkbox
FIELD_VACUUM_EXTENSION_OK	Vacuum extension OK checkbox
FIELD_OVERALL_COMMENTS	Overall comments field
FIELD_QM_LINEITEMS_LINK	Link to line items field
FIELD_AREA	Area field (line items)
FIELD_LEVEL	Level field (line items)
FIELD_FINDING	Finding field (line items)
FIELD_SUMMARY	Summary field (line items)
FIELD_PHOTOS	Photos field (line items)
FIELD_REPORT_NO	Report number field (optional, defaults to "Autonumber")
FIELD_LINEITEM_AUTONUMBER	Line item autonumber for sorting (optional)

Cleaner Response Properties (Multi-Response Mode)

Property	Description
AIRTABLE_CLEANER_RESPONSES_TABLE	Cleaner responses table name
FIELD_RESPONSE_NAME	Cleaner name field
FIELD_RESPONSE_TEXT	Response text field
FIELD_RESPONSE_REPORT_LINK	Link to report field
FIELD_RESPONSE_LINEITEM_LINK	Link to line item field
FIELD_RESPONSE_TYPE	Response type field (optional)

Legacy Overwrite Mode Properties (if not using multi-response)

Property	Description
FIELD_CLEANER_RESPONSE_SUMMARY	Summary response field on report
FIELD_CLEANER_RESPONSE_AREA	Area response field on line item
FIELD_CLEANER_NAME_SUMMARY_RESPONSE	Cleaner name field for summary response
FIELD_CLEANER_NAME_AREA_RESPONSE	Cleaner name field for area response

Endpoints

Endpoint	Method	Description
?recordId={id}	GET	Returns HTML page (not used directly anymore)
?recordId={id}&format=json	GET	Returns JSON data (used by proxy)
/exec	POST	Submits cleaner feedback (JSON body)

POST Body Format (Feedback Submission)

{
  "type": "summary" | "area",
  "recordId": "recXXXXXXX",
  "name": "Cleaner Name",
  "response": "Feedback text"
}

3. WordPress Server-Side Proxy

Location: WPCode plugin snippet (combined with Site Info proxy)

Why it exists: Google Apps Script has a known bug where web apps fail to load when users are signed into multiple Google accounts. The proxy fetches the data server-side, bypassing this issue entirely.

How it works (GET - page load):

1. User visits matthewscleaningco.com.au/qm-report?recordId=xxx
2. WordPress intercepts the request via the init hook
3. PHP fetches JSON from the Apps Script endpoint (with cache-busting parameter)
4. Apps Script converts Airtable attachment URLs to base64 to prevent expiration
5. PHP renders the HTML with no-cache headers and returns it to the user

How it works (POST - feedback submission):

1. User submits feedback via the form
2. JavaScript sends POST to window.location.pathname (the proxy URL)
3. WordPress intercepts the POST request
4. PHP forwards the JSON payload to Apps Script
5. Apps Script saves to Airtable and returns success
6. PHP returns the response to the browser

WPCode Snippet

The proxy is implemented as a PHP snippet in WPCode. It handles both /site-info and /qm-report URLs in a single combined snippet.

Key functions:

• render_qm_report_html($data, $report_id) - Renders the QM report page from JSON data

Wordfence: The proxy request was initially blocked by Wordfence. It has been allowlisted as a false positive.

Caching: A CloudFlare Page Rule is configured to bypass cache for matthewscleaningco.com.au/qm-report* . SiteGround's server cache may also need purging after code changes.

Page Sections

The QM Report page displays the following sections:

1. Report Info - Report number, site number/nickname, prepared by, inspection date
2. Summary - Overall comments, checklist items (QR code, equipment, vacuum checks), feedback form
3. Area Sections (one per line item) - Area name, level, finding badge (Major/Minor/No defects), summary, photos, feedback form

Finding Badges

Badge	Color	Meaning
Major defects	Red (#d32f2f)	Significant issues requiring attention
Minor defects	Orange (#ef6c00)	Minor issues to address
No defects	Green (#2e7d32)	Area passed inspection

Feedback System

Cleaners can respond to inspection findings:

1. Click/tap on the feedback text area
2. Type their response
3. Enter their name
4. Click Submit

Feedback is saved to Airtable:

• Multi-response mode (recommended): Creates a new record in the Cleaner Responses table for each submission
• Legacy mode : Overwrites the response field on the report/line item record

Troubleshooting

Page shows "Missing recordId parameter"

The URL is missing the recordId query parameter. Check the Airtable formula.

Page shows "Error parsing report data"

The Apps Script returned invalid JSON. Check:

• Apps Script deployment is active
• Airtable API token is valid
• Script properties are configured correctly

Feedback submission fails with error

Check:

• Wordfence isn't blocking POST requests
• Apps Script deployment is active
• Cleaner responses table exists (if using multi-response mode)
• Required fields exist in Airtable

Photos don't display

Images are converted to base64 server-side to prevent Airtable URL expiration. If photos still don't display:

• Purge SiteGround cache (click "Purge SG Cache" in WordPress admin bar)
• Purge CloudFlare cache
• A CloudFlare Page Rule bypasses cache for /qm-report* - verify it's active

Wordfence blocks the page or feedback

Go to Wordfence → Firewall → Blocking and allowlist the action.

Page works but feedback says "Error: [object Object]"

The POST request is being blocked or returning an unexpected response. Check browser console for details.

Version History

Version	Date	Changes
Original	-	Initial implementation with google.script.run for feedback
Multi-response	-	Added support for dedicated cleaner responses table
Proxy	Dec 2025	Added WordPress proxy to bypass Google multi-account bug
Base64 images	Dec 2025	Images converted to base64 server-side to prevent Airtable URL expiration; added cache-control headers; CloudFlare bypass rule

Related Documentation

• Site Info Page
• QM Reports Table Structure
• Cleaner Responses Table Structure