BLOG 5

This sprint is about what progress we have made since the last blog we have done. In the last blog, I had just a simple inject that displayed a pseudo leaderboard in the debug. At that stage, everything was very basic and only existed inside Node-RED with no real data storage or visualisation.

Since the last blog I had extended the inject nodes to inject into a function that then updates and looks up a pseudo barcode, recognises what bottle/can it is, assigns it a deposit value, and logs it to Google Sheets. This was a big step forward because instead of just printing values in the debug panel, I now have a proper data pipeline where each recycling event is stored as a row of data. 

The barcode logic works by mapping different inputs to container types such as cans, small bottles, and large bottles. Each of these is then assigned a value based on the Irish deposit return scheme (for example €0.15 or €0.25). This means every time an inject node is triggered, it simulates a real-world recycling event and produces structured data that can be used later for analysis.

The next inject node actually builds the logic to send the data to Google Sheets. This involved formatting the data correctly into an array so that each field lines up with the correct column in the sheet. The columns include things such as timestamp, school name, bin name, container type, size, deposit value, and count. Getting this structure right was important because Looker relies on clean, tabular data to generate visualisations.

So now when I click inject on the first 3 nodes, it automatically updates my Google Sheets page. Each click represents a new recycling event, and the data is appended as a new row. This effectively turns Google Sheets into a lightweight database for the system.

Google Sheets is then connected directly to Looker Studio, which displays all the data I want to show. This is where the project starts to feel like a real system rather than just a backend prototype. Instead of manually calculating totals, Looker aggregates the raw data and builds dashboards on top of it.

Within Looker, I created several key visualisations. These include a national school leaderboard based on total euro value returned, a county leaderboard, and a bin-level breakdown to show which bins are being used the most. I also added a time series chart that shows how each school is performing over time, which helps visualise trends rather than just static rankings.

In addition to leaderboards, I included KPI scorecards such as total euro returned and total number of containers recycled. These give a quick overview of the system performance. I also added a container type breakdown chart to show the distribution between cans, small bottles, and large bottles. This adds another layer of insight into user behaviour.

One important improvement in this sprint was restructuring the Google Sheets data so that it is compatible with Looker. Initially the data was not laid out correctly, but it was reorganised so that the first row contains headers and each subsequent row represents a single event. This made it much easier to connect and build visualisations in Looker.

Overall, this sprint focused on turning a simple Node-RED simulation into a full data pipeline. The system now goes from simulated input, to processing and enrichment in Node-RED, to storage in Google Sheets, and finally to visualisation in Looker Studio. This end-to-end flow demonstrates how the BinBot concept could work in a real deployment.