Author: Daniel Rodellar, PMP

Case Study: Streamlining Workflow Automation Through AI inside PMI Switzerland Chapter Newsletter team

Manual processes are inherently fragile because they rely heavily on fallible human memory. It is a curious thing, the human memory. One moment, an important detail is there, bright and sharp; the next, it has slipped through the mesh, lost in the mists of distraction. Consider how often your team misses a deadline simply because an email was forgotten, or how many cumulative hours your staff loses to repetitive data entry. It begs the question: why do we persist with these manual protocols when sophisticated tools to automate them are readily available?

The Newsletter team faced these exact questions while operating on our strict monthly cadence within the Google Suite ecosystem. For years, we relied on a simple Google Form to collect submissions, backed by a legacy script that managed the data flow. While this process worked for a time, the parameters eventually changed, rendering the underlying code obsolete. We recognized the need for a complete overhaul, yet we lacked the dedicated time required to write the new code from scratch.

The solution was powered by Artificial Intelligence.

The Methodology

One must always have a method. Ours was simple: decomposition. One cannot look at the mountain entirely; one must look at the stone. We broke the great, monolithic objective into fragments, into moments of being. We asked the AI to polish these fragments, to refine the logic, asking again and again until the shape of the thing was true.

We began by defining the objectives clearly, which proved to be the critical first step. Rather than writing the code ourselves, we employed AI assistants, specifically the free versions of ChatGPT, Claude, Gemini, and Perplexity, to handle the heavy lifting. We called upon them not as masters, but as silent collaborators in the dark. Jumping from one to the other, the code gets better and better, as a team work experience (even if sometimes it diverges a bit, and focus is required).

Our strategy focused on decomposition. We broke the project into manageable subtasks and asked the AI to refine specific functions, one by one. Through this iterative approach, we achieved a robust automated process that effectively eliminated the manual bottleneck.

The Architecture

The workflow follows a cyclical pattern that begins with our monthly planning meeting. Once authors draft and submit their articles via a form, the automation takes over to execute a precise sequence of events.

1. Submission and Acknowledgment: When the system records the data, it immediately sends a confirmation to the author to acknowledge receipt. Silence is suspicious; confirmation is reassuring.
2. Team Notification: Simultaneously, the system alerts the entire team. In the past, only the Copy Editor received the file, meaning the process stalled if they were absent. Now, the information is decentralized, ensuring absolute visibility.
3. Review Protocol: When the Copy Editor uploads the content to the Joomla platform, the system tracks this action automatically.
4. Deadline Management: Finally, at the content deadline, the automation generates a comprehensive table of all targeted articles and emails it to the team. This allows us to check the list thoroughly. We miss nothing.

To ensure necessary redundancy, we integrated Slack into the communication loop. The system now pushes notifications to both email and Slack channels, providing alerts that clearly answer the three critical questions: Who, What, and When.

Technical Implementation

We required specific logic to handle complex tasks such as HTML formatting, date parsing, and various triggers. Consequently, the AI generated the following functional architecture:

The Result

While the setup appears complex, it is necessary given that our process involves many stakeholders. Previously, time pressure frequently led to human error, causing us to forget attachments or miss notifications. By removing these variables, the automation has stabilized our workflow.

These automations are a net that catches us when we fall.

The system is not perfect. It will evolve. However, the operational pressure is significantly lower, and the fundamental errors are gone. We can now focus entirely on the content rather than the mechanics of delivery.

Discussion

We integrated these tools specifically to solve a logistical problem that was hindering our productivity.

We are also curious to know how you use the AI assistants to improve some of your automations.

• What are you using the AI assistants for? What objectives or goals do you look for?
• How are you utilizing AI agents in your current workflows? Or not at all?
• Do you employ other languages for coding, e.g. Python, for similar objectives?
• What specific metrics are you trying to improve?
• What ghosts in the machine are you setting free to guard your time?

We invite you to share your own data and experiences with us via the article published on LinkedIn (so we can gather your replies and comments).

>>> Link to LinkedIn article : 🔗

The prompt that you can use to obtain similar results

Here is a prompt you can use to generate the full code in one shot. Some added information that you could provide as extras, to refine your prompt:

• Do you have any existing Apps Script code, form structures, spreadsheet layouts, or previous automation scripts from your workflow that you can share? We did that, as we had the previous code already running.
• What specific data fields does your form collect from authors (e.g., author name, email, article title, submission date, edition target)? List all fields that need to be tracked.
• Provide the exact email addresses for team members alias, Slack webhook URL, and specify when each notification should trigger (e.g., immediately on submission, specific time for ToC digest).

Example prompt

# Goal

Generate production-ready Google Apps Script code that automates the editorial workflow for article submissions, from initial form submission through content deadline management, with dual notification channels (email and Slack) and comprehensive tracking across all stages.

# Return format

Provide complete, executable Google Apps Script code organized into the nine specified functions with proper error handling, logging, and comments. Include:

- All function implementations with their exact purposes as defined

- Trigger setup instructions as inline comments

- Configuration variables for email addresses, Slack webhook URLs, spreadsheet IDs, and Joomla tracking

- HTML email templates for each notification type

- Slack message payload formatting

- Date parsing logic compatible with the schedule format

- Table generation logic for the content deadline digest

Structure the code with clear section headers for each function, configuration constants at the top, and a setup guide at the end.

# The 9 Core Functions

•     onFormSubmit: The main controller trigger.
•     sendAuthorConfirmation: Handles external communication to the writer.
•     sendTeamNotification: Handles immediate internal broadcasting.
•     postToSlack: a robust utility for Slack API calls with graceful failure.
•     sendToC: The "Table of Contents" digest generator (deadline management).
•     checkJoomlaStatus: specific logic to verify upload status.
•     generateDigestTable: HTML generator for the digest email.
•     formatSheets: Maintenance utility for sheet hygiene.
•     parseDateSafe: Centralized date handling to prevent format crashes.

# Warnings

- The system must ensure team-wide visibility rather than single-person dependency

- Date parsing must account for various date formats in the schedule data and handle invalid dates gracefully

- Email validation must prevent system failures from malformed addresses

- HTML sanitization must prevent injection errors while preserving necessary formatting

- The sendToC function must only trigger within the active edition window to avoid sending premature or outdated digests

- Slack notifications must fail gracefully if the webhook is unavailable, allowing email notifications to proceed

- The system must handle missing or incomplete form submissions without crashing

- Attachment handling must verify file presence before attempting to include in notifications

- Time-based triggers (sendReviewRequest, sendToC) must account for timezone differences between the script execution environment and the team's location

- The formatSheets maintenance function must not interfere with active data entry or trigger executions

# Context

The editorial team manages article submissions through a multi-stage workflow involving authors, a Copy Editor, and a Technical Editor as the Newsletter team. The previous used system created single points of failure, leading to workflow stalls and missed deadlines.

The workflow stages are:

1. Authors submit articles via a Google Form

2. System sends immediate confirmation to the author

3. System alerts the entire team simultaneously (not just Copy Editor)

4. Copy Editor uploads content to Joomla platform (tracked on a special column)

5. At content deadline, system generates and emails a comprehensive table of all targeted articles

The system must answer three critical questions in every notification: Who (submitted/uploaded), What (article title/details), and When (submission time/deadline).

The team uses both email and Slack for communications, requiring redundant notification channels to ensure no alerts are missed.

The schedule data contains edition windows with specific deadlines that must be parsed to determine when to send the content digest (sendToC function).

Form submissions capture author information, article details, and potentially file attachments that must be included in notifications.

The Joomla upload tracking likely involves monitoring a specific spreadsheet column where the Copy Editor marks completion.

All functions must include appropriate error handling and logging to diagnose issues without exposing the system to failures from individual component problems.

Setup Instructions

1. Script Creation:
   • Open your Google Sheet.
   • Go to Extensions > Apps Script.
   • Paste the code generated into Code.gs.
2. Configuration:
   • Find the SPREADSHEET_ID (found in your browser URL bar between /d/ and /edit) if you use SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID) or use SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().
   • Find the SLACK_WEBHOOK_URL (create an Incoming Webhook in your Slack App settings).
   • Use TEAM_EMAIL with your actual alias distribution list.
3. Triggers (MUST):
   • In the Apps Script sidebar, click on Triggers (it has an alarm clock icon).
   • Trigger 1 (Form Submission):
     • Function: onFormSubmit
     • Event Source: From spreadsheet
     • Event Type: On form submit
   • Trigger 2 (Digest):
     • Function: sendToC
     • Event Source: Time-driven
     • Type: Day timer (e.g., 10pm to 11pm, to capture submissions during the current day).
   • Trigger 3 (Maintenance - Optional):
     • Function: formatSheets
     • Event Source: Time-driven
     • Type: Weekly.

Once the first code is generated, you can improve each function by being more specific about the expected outputs, adding more details to the way you prefer to have the functions to perform. Or inspired by the prompt you can iterate through all the functions one by one, to compose the final procedure.

Note: images of this article have been generated by AI.