Building a Reporting Pipeline with Lambda and App Scripts
Over the course of this project, we transformed a static HTML/CSS dashboard into a dynamic, secure, serverless reporting pipeline using a suite of AWS services. The journey began with refining the front-end design: we laid out a two-column grid with a fixed sidebar and flexible main panel, then built a 12-column CSS grid inside the main area. By setting equal flex values on the three “stat-card” elements and matching their combined width to the larger charts below, we achieved a harmonious, responsive layout that adapts to different screen sizes.
With the visual foundation in place, we added interactive charts using ApexCharts. Three small radial-bar charts display key server metrics, a large donut chart summarizes traffic categories, and a combination of bar and line charts show trends over time. Each chart is initialized in JavaScript on DOMContentLoaded, pulling static data for demo purposes and styled with gradients and custom color schemes to match the dark theme.
To secure the dashboard, we integrated Amazon Cognito’s Hosted UI. A snippet in the HTML <head> hides the body until authentication is verified: it parses the OAuth token from the URL hash, stores it in localStorage, and redirects to login if no token is found. This approach prevents unauthorized access and keeps all dashboard content behind a login wall.
Next, we exposed a “Generate Reports” button in the sidebar that triggers our serverless backend. Clicking the button calls an API Gateway endpoint (/reporting), implemented using Lambda Proxy Integration. To satisfy browser preflight checks, we configured CORS headers—Access-Control-Allow-Origin, Access-Control-Allow-Methods, and Access-Control-Allow-Headers—in both the OPTIONS and POST integration responses in the API Gateway console.
On the backend, our first Lambda function, testSecretToDynamo, performs four key steps:
- Secrets retrieval: It uses the AWS SDK’s
SecretsManagerClientto fetch test credentials from Secrets Manager, ensuring sensitive values never live in code. - Data ingestion: It parses the incoming
event.bodyfor aticketsarray; if none are provided, it falls back to a hard-coded pair of sample tickets. Each ticket object is then written to DynamoDB with attributesid,ticketKey,date,description, andoutcome. - Chaining: After a deliberate 10-second pause (implemented via
setTimeoutin an async function), the Lambda invokes a second function,formalizeRecentTickets, asynchronously using the AWS SDK’sLambdaClient. - Response: It returns a JSON payload confirming how many tickets were written.
The second Lambda, formalizeRecentTickets, orchestrates the transformation and notification steps. It scans the DynamoDB table for all items, determines the most recent date value, and filters tickets to that date. A simple helper function converts present-tense verbs in the description and outcome fields to past tense (e.g. “is” → “was”), producing a clean, narrative format. The function returns this array of formatted tickets to the caller and, as an administrative notification, sends an email via Amazon SES. By constructing both HTML and plain-text bodies in a SendEmailCommand, we ensure deliverability to the verified address jrmcd15@outlook.com.
Throughout development, we verified each component in isolation:
- Lambda test events in the console, using both direct JSON input and API-proxy shapes.
- CloudWatch Logs streams for both functions, confirming secret loads, DynamoDB writes, date calculations, and SES invocations.
- DynamoDB console to inspect table contents and ensure ticket items conform to the expected schema.
- API Gateway’s Test feature and browser console for the front-end fetch, watching for CORS errors and JSON responses.
From the moment we decided to push our formalized ticket data into a Google Slides deck, we embarked on a lightweight, serverless integration that required no additional Google Cloud console setup. First, we authored a Google Apps Script “web app” under our existing Workspace account. In a few lines of JavaScript, that web app exposes a simple HTTP endpoint (doPost) which accepts a JSON payload of tickets, opens a specified Slides presentation by ID, finds or creates a text box on the target slide, and replaces its contents with a neatly bulleted list of ticket keys, dates, descriptions, and outcomes.
Once our Apps Script was written, we deployed it as a public “Web app” with the access level set to “Anyone, even anonymous.” This configuration was crucial: it allowed our AWS Lambda function to POST updates directly to the script’s /exec URL without running afoul of CORS restrictions or requiring OAuth tokens. We then shared the Slides deck with our Workspace identity so that the script could edit the presentation under the appropriate permissions.
Next, we enhanced our formalizeRecentTickets Lambda. After scanning DynamoDB for the most recent tickets and converting their text to past tense, the function now issues a server-side HTTPS request to our Apps Script webhook. In less than a second, our deck is updated with the latest two tickets as a bulleted list. Only after the Slides update completes do we send out an Amazon SES confirmation email, ensuring that every step of our pipeline—from data retrieval through to visual presentation and notification—is fully orchestrated and logged.
By leveraging Apps Script instead of the full Google Slides REST API, we avoided the overhead of managing service accounts and IAM credentials in Google Cloud. Our entire workflow remains firmly within AWS’s serverless ecosystem—API Gateway to trigger Lambdas, DynamoDB for storage, Lambda for compute, and SES for email—yet seamlessly reaches out to Google Slides for real-time presentation updates. This hybrid approach combines the best of both worlds: rapid, zero-infrastructure scripting in Workspace and robust, scalable back-end processing in AWS.