✓ build@food4education.org
Skip to main content

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:

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: