Site info page for cleaners¶
Overview¶
The Site Info Page displays critical information about each cleaning site for our cleaners. It pulls data from Airtable and presents it in a mobile-friendly format that cleaners can access on their phones.
Live URL format: https://matthewscleaningco.com.au/site-info?recordId={RECORD_ID}
Architecture¶
┌─────────────┐ ┌─────────────────────┐ ┌─────────────────────┐ ┌───────────┐
│ Cleaner │────▶│ WordPress Proxy │────▶│ Google Apps Script │────▶│ Airtable │
│ (Phone) │◀────│ (WPCode snippet) │◀────│ (JSON endpoint) │◀────│ (Sites) │
└─────────────┘ └─────────────────────┘ └─────────────────────┘ └───────────┘
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 (Sites Table)¶
The URL is generated via a formula field in the Sites table:
Previous formula (deprecated):
"https://script.google.com/macros/s/AKfycbzZCDFzKTTdYZcMJy3S1DEbHXiSE1N8UTHWKBr16XPW200cRoJtbwWWoW1HuB_vQflh/exec?recordId=" & RECORD_ID()
2. Google Apps Script¶
Deployment URL: https://script.google.com/macros/s/AKfycbzZCDFzKTTdYZcMJy3S1DEbHXiSE1N8UTHWKBr16XPW200cRoJtbwWWoW1HuB_vQflh/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_TABLE_NAME | Name of the Sites table |
| FIELD_SITE | Site name field |
| FIELD_STREET_ADDRESS | Street address field |
| FIELD_SUBURB_CITY | Suburb/city field |
| FIELD_SITE_KEY_NUMBER | Key number field |
| FIELD_ALARM_PANEL_LOCATION | Alarm panel location field |
| ALARM_PANEL_PHOTOS | Alarm panel photos field |
| FIELD_ALARM_SECURITY_CODE | Alarm/security code field |
| FIELD_LIGHT_SWITCH_LOCATION | Light switch location field |
| FIELD_LIGHT_SWITCH_LOCATION_PHOTO | Light switch photo field |
| FIELD_KEYS_PHOTO | Keys photo field |
| FIELD_SITE_PHOTO | Site photo field |
| FIELD_SITE_PLAN | Site plan field |
| FIELD_BIN_LOCATION | Bin location field |
| FIELD_BIN_LOCATION_PHOTO | Bin location photo field |
| FIELD_DELIVERY_LOCATION | Delivery location field |
| FIELD_DELIVERY_LOCATION_PHOTO | Delivery location photo field |
| FIELD_OTHER_SECURITY_ACCESS_INFO | Other security info field |
| FIELD_OPERATING_HOURS | Operating hours field |
| FIELD_STOCK_ROOM_LOCATION | Stock room location field |
| FIELD_STOCK_ROOM_PHOTO | Stock room photo field |
| FIELD_DISPENSER_COUNT | Number of dispensers field |
| FIELD_CLEANING_NOTES | Cleaning notes field |
| FIELD_BIN_SCHEDULE | Bin schedule field |
| FIELD_GENERAL_PHOTOS | General photos field |
| FIELD_CONSUMABLES_AGREED | Consumables agreed field |
| FIELD_CONSUMABLES_DECLINED | Consumables declined field |
| FIELD_PARKING_LOCATION | Parking location field |
| FIELD_PARKING_LOCATION_PHOTO | Parking location photo field |
| AIRTABLE_CONSUMABLES_TABLE | Consumables table name (for linked records) |
| FIELD_CONSUMABLE_NAME | Consumable name field |
Endpoints¶
| Endpoint | Description |
|---|---|
| ?recordId={id} | Returns HTML page (not used directly anymore) |
| ?recordId={id}&format=json | Returns JSON data (used by proxy) |
3. WordPress Server-Side Proxy¶
Location: WPCode plugin snippet
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:
- User visits
matthewscleaningco.com.au/site-info?recordId=xxx - WordPress intercepts the request via the
inithook - PHP fetches JSON from the Apps Script endpoint
- PHP renders the HTML and returns it to the user
- User's browser never contacts http://script.google.com directly
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_site_info_html($data)- Renders the site info page from JSON data
Wordfence: The proxy request was initially blocked by Wordfence. It has been allowlisted as a false positive.
Page Sections¶
The Site Info page displays the following sections:
- Header - Site name, address, suburb/city, key number
- Access & Security - Alarm panel location/photos, alarm code, light switch location/photo, operating hours, keys photo, other security info
- Cleaning - Cleaning notes
- Layout - Site photo, site plan
- Rubbish - Bin schedule, bin location/photo
- Stock & Consumables - Delivery location/photo, stock room location/photo, number of dispensers, consumables agreed/declined
- Parking - Parking location/photo
- General Photos - Additional site photos
Troubleshooting¶
Page shows "Missing recordId parameter"¶
The URL is missing the recordId query parameter. Check the Airtable formula.
Page shows "Error parsing site data"¶
The Apps Script returned invalid JSON. Check:
- Apps Script deployment is active
- Airtable API token is valid
- Script properties are configured correctly
Page loads but shows "UNAVAILABLE" for all fields¶
The record ID doesn't exist or the Airtable fields have changed names. Verify:
- Record exists in Airtable
- Field names in script properties match Airtable
Wordfence blocks the page¶
Go to Wordfence → Firewall → Blocking and allowlist the action.
Images don't display¶
Airtable attachment URLs may have expired. Refresh the page to get fresh URLs.
Version History¶
| Version | Date | Changes |
|---|---|---|
| 1 | Original | Initial implementation with client-side fetch |
| 2 | - | Added alarm panel location photos field |
| 3 | Dec 2024 | Embedded data server-side (no client fetch) |
| Proxy | Dec 2024 | Added WordPress proxy to bypass Google multi-account bug |