AppSheet Automation & Bots

The F4E AppSheet system leverages AppSheet's powerful automation capabilities through bots that handle critical business processes, document generation, and notifications. This document provides a comprehensive overview of all automated processes in the system.

Overview

AppSheet bots are automated processes that respond to specific events or data changes in the system. They ensure timely communication, document generation, and workflow automation without manual intervention.

Bot Categories

1. Document Generation Bots

These bots automatically generate PDF documents based on system events.

Plan to Active - Generate PDF Bot

Purpose: Automatically generates PDF documents when meal plans are activated.

Configuration:

• Trigger Event: When plan.status changes from "Open" to "Active"
• Table: plan
• Condition: [_THISROW].[status] <> "Open"

Document Settings:

• Content Type: PDF
• Template: Google Doc (DocId: 1e5igEZvAdoSlATJ0jAq2fy-H2bKD4BtmcuE77bBLs_A)
• File Store: appsheet-recipes
• File Naming:

  SUBSTITUTE(LOOKUP([_THISROW].[kitchen_id], "public.kitchen", "id", "name"), " ", "_")& 
  "_Plan"& SUBSTITUTE([_THISROW].[id], "-", "_")& "_"& 
  TEXT([_THISROW].[date], "YYYYMMDD")& "_"& 
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([_THISROW].[meal], " ", "_"), "&", "_"), "(", ""), ")", ""), "-", "_")& "_"

• Page Settings: A4 Portrait with custom margins

Example Output: Kitchen_Name_Plan_PlanID_20241201_Breakfast_

Generate Delivery Note Bot

Purpose: Creates delivery notes for kitchen orders.

Configuration:

• Trigger Event: DNote Generation
• Table: request
• Content Type: PDF
• Template: Google Doc (DocId: 1H4nlXJIbC8stY7vcDWaoZsVyLiPJS-qzIgQlRX5_aCA)

File Settings:

• File Store: Default
• Folder Path: /appsheet/data/kitchen_ops_training-357142272/Files/orders
• File Naming:

  LOOKUP([_THISROW].[kitchen_id], "public.kitchen", "id", "name") & 
  "- Order"&[_THISROW].[order_no]& " - " & 
  TEXT([_THISROW].[order_date], "YYYYMMDD")

• Page Settings: A4 Portrait

Example Output: Kitchen_Name- Order12345 - 20241201

2. Notification Bots

These bots send automated email notifications to relevant stakeholders.

Notify Warehouse Bot

Purpose: Notifies warehouse managers when new orders are posted.

Configuration:

• Trigger Event: order_posted
• Table: request
• Email Type: Send email

Email Settings:

• Recipients:

  SELECT(kitchen_mapping[manager_email], [role] = "WHSE")

• Subject: Order <<[order_no]>> Created
• Body Template: HTML format with order details

Email Content:

Hello,

Please take note that an order has been posted by <strong><u>Kitchen - <<LOOKUP([kitchen_id], "public.kitchen", "id", "name")>></u></strong>. Details are as below:

Order: <<order_no>>
Date: <<posting_date>>

<strong><em>Rice: <<rice_requested>></em></strong>
<strong><em>Wairimu Beans: <<beans_requested>></em></strong>
<strong><em>Mwitemania Beans: <<mwitemania_requested>></em></strong>
<strong><em>Western Ndengu: <<w_ndengu_requested>></em></strong>
<strong><em>Green Grams: <<gg_requested>></em></strong>
<strong><em>Salt: <<salt_requested>></em></strong>
<strong><em>Cooking Oil: <<cooking_oil_requested>></em></strong>
<strong><em>Maize: <<maize_requested>></em></strong>

<strong><em>Regards,</em></strong>
<strong><em> <<requested_by>> </em></strong>

Notify Kitchen Bot

Purpose: Notifies kitchen managers when orders are issued by warehouse.

Configuration:

• Trigger Event: order_issued
• Table: request
• Email Type: Send email

Email Settings:

• Recipients:

  SELECT(kitchen_mapping[manager_email], [kitchen_id] = [_THISROW].[kitchen_id])

• Subject: Order <<[order_no]>> Issued
• Body Template: HTML format with issued quantities

Email Content:

Hello,

Please take note that an order has been Issued by <strong><u> Warehouse </u></strong>. Details are as below:

Order: <<order_no>>
Date Issued : <<issued_date>>

<strong><em>Rice: <<rice_issued>></em></strong>
<strong><em>Wairimu Beans: <<beans_issued>></em></strong>
<strong><em>Mwitemania Beans: <<mwitemania_issued>></em></strong>
<strong><em>Western Ndengu: <<w_ndengu_issued>></em></strong>
<strong><em>Green Grams: <<gg_issued>></em></strong>
<strong><em>Salt: <<salt_issued>></em></strong>
<strong><em>Cooking Oil: <<cooking_oil_issued>></em></strong>
<strong><em>Maize: <<maize_issued>></em></strong>

<strong><em> <<request_comment>> </em>

<strong><em>Regards,</em></strong>
<strong><em> <<issued_by>> </em></strong>

Email Configuration & Management

Centralized Email System

All bot-generated emails and support communications are managed through a centralized email system:

• Primary Support Email: appsheet@food4education.org
• Auto-Forward Target: appsheet@food4education.org

Email Flow

Email Templates

All bot emails use consistent formatting and include:

• Clear subject lines with order/plan identifiers
• HTML formatting for better readability
• Dynamic data insertion using AppSheet expressions
• Professional signatures with user attribution
• Structured ingredient/quantity information

Bot Security & Permissions

Security Settings

• Bypass Security Filters: Enabled for system operations
• Data Access: Appropriate permissions for required tables
• Audit Trail: All bot actions logged with timestamps
• User Attribution: Bot actions tracked with updated_by field

Error Handling

• Failed Operations: Logged for retry
• Template Errors: Fallback to default formatting
• Email Failures: Retry mechanism with exponential backoff
• Data Validation: Pre-execution validation of required fields

Bot Monitoring & Maintenance

Performance Monitoring

• Execution Time: Track bot performance
• Success Rate: Monitor successful executions
• Error Logs: Review and address failures
• Resource Usage: Monitor system resource consumption

Maintenance Tasks

• Template Updates: Regular review of email templates
• Permission Audits: Quarterly security reviews
• Performance Optimization: Monthly performance analysis
• Error Resolution: Weekly error log review

Troubleshooting Bot Issues

Common Issues

1. Bot Not Triggering

   • Check trigger conditions
   • Verify data changes meet criteria
   • Review security permissions

2. Email Delivery Failures

   • Verify recipient email addresses
   • Check email template syntax
   • Review AppSheet email quotas

3. Document Generation Errors

   • Validate template IDs
   • Check file store permissions
   • Verify data field availability

4. Performance Issues

   • Review bot execution logs
   • Check database performance
   • Monitor system resources

Support Contacts

For bot-related issues:

• Technical Support: appsheet@food4education.org
• System Administrator: Contact your area lead
• Emergency Issues: Escalate through standard support channels

---