LinkedIn
Twitter
There are several requests in the community to create a PDF report with watermark. The below post has relevant discussion about watermarks in PDF reports.
Watermark and logo in pdf template - Google Cloud Community
@SkrOYC has already shared a nice tip of creating a PDF report with watermark using the HTML/CSS approach.
The approach below uses GAS script along with the option in the Google Docs to set a watermark.
Overview
Salient Points of new approach
The approach also uses Google Docs as a template. However the GDoc template does not use the usual AppSheet template expressions. But rather the template uses the dual curly brackets on each side as variables in the template. Most importantly the watermark is added in the template using the watermark option available in Google docs.
As compared to AppSheet templates that remove any watermark when the report is created, this Google apps script based approach retains the watermark in the final pdf report.
Current limitations of new approach
Currently I have tested the template by creating PDF report based on only single AppSheet table record. I have not tested it for multiple records, multi page reports or reports with child table. However I believe those options should also be possible.
The Steps
1. Create a Google Docs template:
The GDocss template looks like below. As mentioned it has variables with double curly brackets. It also has a watermark. The watermark is inserted using option Insert -> Watermark available in GDocs. I have used text based watermark, even though GDocs also has option for image as watermark.
The GDocs template and watermark options image as below
2. Create the bot:
Created a data change bot with "call a script" option as follows. The bot runs on updates in a Products record.
3. Create a GAS Script
The main steps in the GAS script are as follows
The GAS script looks like below
function watermarkPDFLatest(productname, price, inventory, requested, available, productdescription) {
// Mention the folder Id in G Drive where you would like to store the PDF
var destinationFolderID = 'Please enter the folder ID here';
//Mention the GDoc template file ID in G drive
var templateFileID = 'Please enter the template file ID here';
var templateFile = DriveApp.getFileById(templateFileID);
var destinationFolder = DriveApp.getFolderById(destinationFolderID);
// Create an intermediate copy of the template GDoc file called "intermediateFile"
// This copy will also be a GDoc file
// We create copy of the template file because original template will be continuously reused.
// Assign a unique file name to the new intermediate file because in multiuser system the file name needs
// to be unique. So assign a date time stamp to the intermediate file name or some better unique qualifier such as unique ID.
var fileName = productname + (new Date());
var intermediateFile = templateFile.makeCopy(fileName, destinationFolder);
var intermediateFileToEdit = DocumentApp.openById(intermediateFile.getId());
//Populate the data fetched from the AppSheet record by the GAS as an argument to the GAS function.
var doc= intermediateFileToEdit.getBody();
doc.replaceText("{{Product_Name}}", productname);
doc.replaceText("{{Price}}", price);
doc.replaceText("{{Inventory}}", inventory);
doc.replaceText("{{Requested}}", requested);
doc.replaceText("{{Available}}", available);
doc.replaceText("{{Product_Description}}", productdescription);
// Save the intermdiate G doc file after changes have been made to it by populating the data fetched by the
// AppScript from the AppSheet record.
intermediateFileToEdit.saveAndClose();
// Do necessary housekeeping to convert the intermediate G doc file to final PDF file used as report
const folder = DriveApp.getFolderById(destinationFolderID);
const fileID = intermediateFileToEdit.getId();
const docFile = DriveApp.getFileById(fileID);
//Give a name to the PDF file and save it in the same destination folder
folder.createFile(docFile.getBlob()).setName('productname'+ (new Date())+'.pdf');
//Delete the inermediate Gdoc file because a PDF has been created now.
DriveApp.getFileById(fileID).setTrashed(true);
}
Acknowledgements for Google ApsS Script references: I must mention here that I referred YouTube videos and Stack Overflow posts regarding GAS scripts for populating GDOCs with data from Google sheets and creating PDF from files saved in G Drive. I found videos and posts by About Me - Jeff Everhart Jeff Everhart and User Tanaike - Stack Overflow and https://www.youtube.com/@LaurenceSvekisCourses/videos as very useful references on the said GAS requirement.
Of course I have created the GAS above as per our requirement to get variables from an AppSheet record and other such relevant requirements for our purpose.
4. The image of the created report
The PDF looks like below when the bot runs. As we can see, it retains the watermark.🙂
5. The File Creation and Deletion in G Drive
I captured a small GIF of how the backend files in G Drive create and delete, when the GAS runs. The GAS creates an intermediate GDOC file by copying the GDoc template , populates it with AppSheet record values , converts it to PDF and then deletes the intermediate DDOC file. This intermediate GDO creation step is required in GAS because we wish to retain the main GDOC template for reuse in bot.
Hope this helps.
With the same approach, one can create beautiful certificates or survey or inspection reports with background images. I believe having background images is not natively possible currently with AppSheet templates.
Here is a certificate PDF created using exactly the same approach described above.
Template as below
Certificate PDF Created as below after running the bot with GAS
