Introduction
Summary
The AppSheet no-code development platform allows for the rapid creation of business applications. However, within the context of automated document generation (PDF), certain technical limitations can arise: complex layout management, table sizing, page numbering, or the inability to generate an editable format (Google Docs).
This article presents a solution using Google Apps Script to design a dedicated document rendering engine. This approach enables the production of PDFs that comply with templates (including headers and footers) or editable Google Docs, all driven directly from AppSheet automation.
The Problem: Native AppSheet Generation Limitations
Summary
PDF document generation via AppSheet bots generally relies on a Google Docs template. Despite the flexibility of the latter, the final rendering in PDF can present discrepancies for professional use:
-
Formatting Management: Headers, footers, and page numbering defined in the Google Docs template are not always preserved during PDF conversion by AppSheet.
-
Table Rendering: Adherence to table size and style constraints is sometimes inconsistent.
-
Single Output Format: AppSheet generates PDF by default, preventing the production of documents in Google Docs format for subsequent editing by the user.
These constraints can impact the quality of the documents produced (invoices, reports, contracts) and require post-processing steps.
Example of a PDF document generated via Apps Script, preserving template headers, footers, and styles.
The Solution: A Rendering Engine via Apps Script
Summary
To meet these needs, I developed a specific rendering engine in Google Apps Script.
This solution functions as an interpreter. It dynamically analyzes your AppSheet data structure, queries the AppSheet API to retrieve information, and uses the Google Drive API to perform data merge with the template.
Features of this approach:
-
Template Fidelity: The generated document (PDF or Docs) respects the formatting of the source Google Docs template.
-
Full Support: Support for headers, footers, numbering, and complex styles.
-
Format Choice: Dynamic selection of the output format (PDF or Google Docs).
Practical Guide: Implementation in 3 Steps
This script was designed to be portable from one application to another without modifying the source code. Here are the setup steps.
1. Structure Specification (Google Sheet)
Summary
The first step is to define your AppSheet application’s data model for the script. Create a tab in a Google Sheet with the following columns: table, column, type, source.
It is necessary to list tables, their relationships (Ref, Ref_List), and specify Image type columns (including Photo, Signature, Drawing, Thumbnail).
Structure Sheet configuration: essential for data model mapping
2. Credential Collection
Summary
The script requires configuration to authenticate with the AppSheet API and access Drive files.
-
AppSheet Side: Note the App ID and the App Access Key (available in Settings > Integrations > IN).
-
Drive Side: Identify the IDs for the Google Docs Template, the Destination Folder, and the previously created Structure Sheet (these IDs are visible in the files’ URL).
Press enter or click to view image in full size
Location of the App ID and App Access Key in the AppSheet configuration interface
3. Automation Configuration
Summary
In your AppSheet application’s automation, create a “Call a script” task. Select the main function (main_call_template) and fill in the parameters collected in the previous step.
The call is made in context on a specific row, analogously to a standard document generation task.
Press enter or click to view image in full size
Configuration of the ‘Call a script’ task in AppSheet Automation, linking the event to the generation script.
Future Outlook
This script constitutes a functional solution for palliating certain limitations of AppSheet.
This tool is intended to evolve. A planned evolution concerns integrating a function interpreter directly into the script code, in order to extend possibilities during data merge.
Please note I built this script with the help of Google AI Studio, which you may recognize some pieces of syntax 
Find the full source code, detailed documentation, and installation instructions on the GitHub repository: https://github.com/AurelienMoyenCodergo/file_generation