AppSheet Document Generation: Integrating Apps Script to Overcome Native Limitations (PDF & Google Docs)

[Aurelien]

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:

1. Template Fidelity: The generated document (PDF or Docs) respects the formatting of the source Google Docs template.

2. Full Support: Support for headers, footers, numbering, and complex styles.

3. 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

[Denzil_Snyman]

@Aurelien Thank you for making the effort on this, I sincerely wish the team would have got this done by now within their current framework, but hey, is what it is. @Jose_Arteaga @Adam-google

Can you confirm, our existing templates can be used, we must only get the ID across into script. We can use this configuration on any table as long as it’s structure is in the data model tab.

[Suvrutt_Gurjar]

Good one @Aurelien. You tip extracts best out of Google apps script, Google docs and AppSheet to get documents, reports with professional look.

[Aurelien]

@Denzil_Snyman

Can you confirm, our existing templates can be used, we must only get the ID across into script. We can use this configuration on any table as long as it’s structure is in the data model tab.

Hi

II confirm existing templates can be used! I made it on purpose.

In the data model tab you only need to indicate specific columns such as key-columns, ref-type or enum of ref, and images. All the other columns don’t need to be specified.

I will take time to build a video to explain this further - my challenge: 3 minutes max for final video rendering.

Let me know if you have further questions in the meantime, I will be happy to answer it!

[eric_niedergang]

Hi Aurelien,

Nice jobs

I’ve started a test. It works, but I can’t seem to retrieve the values from a virtual column (REF_ROWS value)

My application has two tables: Campagne and Annonces (a very basic structure).

Table “Campagnes”

With a virtual column “Related Annonces” : REF_ROWS(“Annonces”, “campagneID”)

Table “Annonces”

I created this database structure in a separate Google Sheets file

In my google docs template for displaying a campaign, I write this loop:

<<Start: [Related Annonces]>><<[titre]>>
Prix : <<[prix]>> € HT
<>

I obtain this. The loop runs 3 times (OK), but I’m not getting the values?

Did I make a mistake when defining the database structure?

[Aurelien]

Hello @eric_niedergang

Thanks for giving a try and a feedback!

I don’t think you made a mistake, it’s just that I didn’t think to handle this kind of situation, as I commonly don’t use a Start/End out of a table.

It gives the same on my side.

I will give it a fix when I have time somedays. I will ping you when I do!

In the meantime, can you confirm it works properly when using a start syntax in a table such as this one?

[eric_niedergang]

Yes, it works with an array structure
**
Template**

PDF

Another question: is it possible to attach the generated PDF to an email in a second step of the bot? (or a link to the Google Docs) ?

[Aurelien]

This is so imaginative!

I guess you could, provided you give the correct file path in the second step of your bot.

I would suggest to add a “wait” step, maybe 1 minute, just to let the script enough time for running till the end before saying to AppSheet “get this file”, which would not exist yet otherwise.

[eric_niedergang]

Thank you for your reply @Aurelien

I’m not sure if I’ll send it by email after all, as the minimum waiting time is 5 minutes for a “wait” step !

I haven’t been able to retrieve the URL of a file that has just been created in Appsheet? Could you give me some information?

Would it also be possible to pass the file name as a variable in order to customize the file name?

[Syamsudin]

hi @eric_niedergang

Try this link, maybe it will be what you expect https://youtu.be/syc8TgbqZKM?si=ar0cYN0dmofIeLYV

[Aurelien]

Hi @eric_niedergang

@eric_niedergang

I’m not sure if I’ll send it by email after all, as the minimum waiting time is 5 minutes for a “wait” step !

If you are sure you would like to send it immediately afterward file generation, you may want to add a piece of code in the main script, to send the file with MailApp service.

For reference: Class MailApp  |  Apps Script  |  Google for Developers

@eric_niedergang

Would it also be possible to pass the file name as a variable in order to customize the file name?

You may change it in the code of the file “main”.

By now I just reused the template name with “copy”, but you can name it as you want, even make it a parameter for the function to call

Are you familiar with Apps Script?

[eric_niedergang]

I’m not familiar with Apps Script but I understand coding in other languages (JS, PHP).

I’m going to go through the code in detail. I’m going to try a couple of things: sending an email using MailApp (I’ve used this class before). I’m also going to look into returning the file name using a RETURN statement. By reading the documentation, I noticed that a bot waits for the return value before moving on to the next step. It can help to send an email with the a bot step

[Aurelien]

That’s correct! Good idea

[kennysz]

Apps Script is essentially JS. You’ll pick it up right away without needing to learn a new programming language.

[eric_niedergang]

Yes, you are right. But I’m not familiar with the Apps Script classes, authorization, deployment…

[eric_niedergang]

Yes, you are right.
If the generated file is a PDF, I was also thinking of returning the path to the PDF file created in My Drive, then adding a “Run a data action” step to store the link in the database, and finally adding this PDF file as an attachment using the “Other attachment” function when sending the email