Process improvement ยท PickleGO project
Automating the recurring ActiveSG sheet update
PickleGO needed ActiveSG class information collated into a spreadsheet on a recurring basis, and class availability changes frequently. Doing it by hand took approximately twenty minutes each time. The workflow I built reduced the recurring update to approximately one minute.
Sections on this page
Starting point
The recurring task
The task was to gather details of ActiveSG pickleball class availability and put them into a sheet in a consistent shape so the team could work with them. Class availability changes frequently, so the collation had to be repeated on a regular basis, and the same sequence of copying and reformatting happened on every update.
The recurring update, before and after
Approximately 20 minutesper update
Copying and reformatting the class information by hand on every update. These timings are practical estimates.
This is a front-end demonstration of how the workflow behaves. It is not connected to ActiveSG and no processing happens here. Where the automatic return fails, the completed workflow also allows the extracted rows to be pasted in manually.
Constraints
The routes that were set aside
Recurring cost was already an established feasibility concern in the automation thinking before this build, since a subscription paid indefinitely to save twenty minutes on each update is difficult to justify on a small project. Two technical routes were then tried and set aside during the implementation itself.
- Direct server-side access
- Requests to the ActiveSG internal API from the server side returned HTTP 403 through Cloudflare during testing.
- Jina Reader
- Using a reader service introduced an authentication and token dependency, and it did not offer the long-term reliability the recurring task needed.
- The browser-assisted route
- Reading the listing in the user's own browser avoided both dependencies and avoided a recurring paid scraping service.
Why the browser is part of the design
ActiveSG programme cards are rendered dynamically. can fetch ordinary HTTP responses and it does not operate a full interactive browser tab, so the Tampermonkey connector reads the programme information after ActiveSG has rendered it in the user's browser.
The point of the architecture choice was making the recurring time saving practical without attaching an ongoing cost or an external dependency to it.
Build
The final architecture
The refresh is manual. The source is the exact filtered PickleGO listing on ActiveSG, opened in the user's normal Edge or Chrome browser with Tampermonkey and the PickleGO connector userscript installed. Apps Script is bound to the Google Sheet and handles the refresh dialog, the request, validation, the business rules and the write through the Spreadsheet Service (opens in a new tab). I wrote the Apps Script with AI-assisted coding and configured the access it needed.
Google Cloud supplies the OAuth identity and configuration for the project, and the Google Auth Platform controls test-user authorisation while the app remains in Testing. Google Cloud sits outside the extraction and data-processing path. The built-in services documentation (opens in a new tab) sets out the boundary the split between the browser and Apps Script works within.
- 01
Refresh request
The user clicks Refresh Classes in the Sheet. Apps Script creates a request identifier and opens the refresh dialog.
- 02
Browser extraction
The filtered ActiveSG listing opens, Tampermonkey recognises the page and the connector reads the class information once the programme cards have rendered.
- 03
Return and validation
The extracted rows return to the Sheets dialog, where Apps Script validates the request, the source and the payload.
- 04
Business rules
Apps Script derives the operational fields from the verified data, including programme status and the other table values.
- 05
Sheet update
The validated table is written, with formatting, dropdown behaviour and verification information restored.
Implementation
What the implementation does
The completed implementation covers the following behaviour.
- Manual refresh only
- The update is started from the Sheet, so a person decides when a refresh happens. Nothing runs on a schedule.
- Request, source and payload validation
- The incoming request, the source it claims to come from and the returned payload are all validated before the data is considered.
- Checks before writing
- The validated data is checked before it reaches the Sheet, so an incomplete reading does not overwrite good rows.
- Concurrency protection
- Overlapping runs are prevented, so two refreshes cannot write over one another.
- Preserving verified availability
- A programme is set to Closed only on explicit evidence such as Full, registration closed, programme cancelled or programme ended. Where a current reading cannot verify a programme, the previously verified value is retained where that is safe.
- Formatting and dropdowns
- Spreadsheet formatting and dropdown behaviour are restored after a write, so the Sheet stays usable.
- Verification notes
- Remarks record the verification position, including where the latest verification failed, so someone reading the Sheet can see the basis of a value.
- Browser dependency
- The workflow depends on the configured browser environment, since the extraction runs there. A manual paste of the extracted rows covers the case where the automatic return fails.
Outcome
Value and limitations
Value to the company
A recurring task of approximately twenty minutes takes approximately one minute when the workflow is used, and the browser-assisted architecture kept that saving free of a recurring paid service. The sheet is produced in the same shape each time and the saving repeats on every update.
The limitation is that the extraction depends on the source page keeping its current structure and on the configured browser environment being in place. If the listing changes shape, the userscript needs updating. I have not measured any downstream effect on performance and I cannot say how the workflow was used after my placement ended.