LinkedIn
Twitter
This tip is mainly extension of a few tips that make use of Google Apps Script(GAS) for various reporting options earlier shared and referred in the post below.
Overview:
The tip basically uses Google Apps Script to create the PDF report. The approach in tip demonstrates two main benefits listed below
Show More
1. To dynamically change (based on a condition) the background colors of cells in a table: In AppSheet's native reporting engine that converts a G Doc template to a PDF, there is no provision to dynamically change the colors of the background cells in a table. So one needs to rely on changing only the text font colors. With this tip, one can dynamically change the background cell colors for say statuses.
2. To add page headers, footers, numbers: There is no native support to create headers, footers with page numbers with the native AppSheet PDF creation engine.. With GAS if the report extends to any number of pages, the page numbers are automatically allocated as the page numbers option used in G docs itself is used to create the page number for subsequent report pages.
Salient Point of implementation
Show More
1. The tip basically uses setBackgroundColor("Hex code") and setForegroundColor("Hex code") in GAS Script to set the colors of the background and font colors of cells in a table in a G Doc.
2. The app creator can set the headers , footers and page numbers in the G doc template itself using Gdoc options. Since GAS populates the template, the headers footers and page numbers are retained in the PDF unlike in the native AppSheet template created PDF that removes the headers footers and page numbers in succeeding pages.
Report Images
GAS Based report
We can see that the backgrounds of the table cells are colored as per status of that attribute. So also the page number and header footer.
The page numbers and headers footers even extend to the subsequent pages as the page number 2 of the report below shows.
The report produced through native AppSheet engine looks like below. As we can see it does not have multi color background cells but only multi color texts based on condition.
Reference Tips:
Show More
The following tips also make use of GAS to create various options in the reports. So tthis tip is basically an extension of those tips of using still more options of G docs or sheets through GAS to create elegant reports.
Export to Google Docs from AppSheet - Google Cloud Community
Tip to ALWAYS AUTOMATICALLY include The LATEST UPD... - Google Cloud Community
Create elegant "in app" reports using Google Apps... - Google Cloud Community
The report templates
AppSheet Native report G Doc template as below
Show More
As the template shows, there are conditional expressions to show various statuses in different font colors but there is no background cell coloring possible.
G DoC report template used in GAS based report creatios as below
Show More
The background cell color changes conditionally are taken care by the GAS and hence the template itself is simpler.
The GAS Script:
The GAs Script is run through an AppSheet automation bot. The GAS Script receives several AppSheet record fields to include in the report. These Appsheet record fields are passed the GAS script as function arguments. It is definitely not a very efficient script and can be much further optimized. But the main idea was to demonstrate extending G doc options into reports.
Show More
function reportDocFill(projectName , reportMonth , projectManager , overallProjectStatus , statusNotes , budget , budgetNotes , schedule , scheduleNotes , quality , qualityNotes , scope , scopeNotes , risk , riskNotes, docTitle) {
// Google Doc template ID
let templateFileID = "INSERT G DOC TEMPLATE FILE ID HERE";
var templateFile = DriveApp.getFileById(templateFileID);
// Mention the folder Id in G Drive where you would like to store the PDF
var destinationFolderID = 'INSERT DESTINATION FOLDER ID HERE';
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 = docTitle;
var intermediateFile = templateFile.makeCopy(fileName, destinationFolder);
var intermediateFileToEdit = DocumentApp.openById(intermediateFile.getId());
body = intermediateFileToEdit.getBody();
// Paste the content into the Doc at the defined locations
// Populate data in the opy of the template that was created
body.replaceText("{{Project_Name}}", projectName);
body.replaceText("{{Project_Manager}}", projectManager);
body.replaceText("{{Report_Month}}", reportMonth);
body.replaceText("{{Status_Notes}}", statusNotes);
body.replaceText("{{Budget_Notes}}", budgetNotes);
body.replaceText("{{Schedule_Notes}}", scheduleNotes);
body.replaceText("{{Quality_Notes}}", qualityNotes);
body.replaceText("{{Scope_Notes}}", scopeNotes);
body.replaceText("{{Risk_Notes}}", riskNotes);
body.replaceText("{{Overall_Status}}", overallProjectStatus);
body.replaceText("{{Schedule}}", schedule);
body.replaceText("{{Budget}}", budget);
body.replaceText("{{Quality}}", quality);
body.replaceText("{{Risk}}", risk);
body.replaceText("{{Scope}}", scope);
// Gets the second table in the document template to populate with various statuses and status notes
const table = body.getTables()[1];
// Get the cell of the table's second row and second and third column to highlight the overall status cells with appropriate status background colors
var celloverallStatus = table.getCell(1, 2);
var celloverallStatusNotes = table.getCell(1, 1);
if (overallProjectStatus == "Excellent") {
celloverallStatus.asTableCell().setBackgroundColor("#CCFFCC");
celloverallStatus.asTableCell().setForegroundColor("#404040");
celloverallStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
celloverallStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (overallProjectStatus == "Good") {
celloverallStatus.asTableCell().setBackgroundColor("#FFFF99");
celloverallStatus.asTableCell().setForegroundColor("#404040");
celloverallStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
celloverallStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (overallProjectStatus == "Unsatisfactory") {
celloverallStatus.asTableCell().setBackgroundColor("#FFCCFF");
celloverallStatus.asTableCell().setForegroundColor("#404040");
celloverallStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
celloverallStatusNotes.asTableCell().setForegroundColor("#404040");
};
// Get the cell of the table's third row and second and third column to highlight the budget status cells with appropriate status background colors
var cellbudgetStatus = table.getCell(2, 2);
var cellbudgetStatusNotes = table.getCell(2, 1);
if (budget == "On Budget") {
cellbudgetStatus.asTableCell().setBackgroundColor("#CCFFCC");
cellbudgetStatus.asTableCell().setForegroundColor("#404040");
cellbudgetStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
cellbudgetStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (budget == "Under Budget") {
cellbudgetStatus.asTableCell().setBackgroundColor("#FFFF99");
cellbudgetStatus.asTableCell().setForegroundColor("#404040");
cellbudgetStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
cellbudgetStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (budget == "Over Budget") {
cellbudgetStatus.asTableCell().setBackgroundColor("#FFCCFF");
cellbudgetStatus.asTableCell().setForegroundColor("#404040");
cellbudgetStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
cellbudgetStatusNotes.asTableCell().setForegroundColor("#404040");
};
// Get the cell of the table's fourth row and second and third column to highlight the schedule status cells with various status background colors
var cellscheduleStatus = table.getCell(3, 2);
var cellscheduleStatusNotes = table.getCell(3, 1);
if (schedule == "On Schedule") {
cellscheduleStatus.asTableCell().setBackgroundColor("#CCFFCC");
cellscheduleStatus.asTableCell().setForegroundColor("#404040");
cellscheduleStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
cellscheduleStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (schedule == "Ahead of Schedule") {
cellscheduleStatus.asTableCell().setBackgroundColor("#FFFF99");
cellscheduleStatus.asTableCell().setForegroundColor("#404040");
cellscheduleStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
cellscheduleStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (schedule == "Behind Schedule") {
cellscheduleStatus.asTableCell().setBackgroundColor("#FFCCFF");
cellscheduleStatus.asTableCell().setForegroundColor("#404040");
cellscheduleStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
cellscheduleStatusNotes.asTableCell().setForegroundColor("#404040");
};
// Get the cell of the table's fifth row and second and third column to highlight the quality status cells with appropriate status background colors
var cellqualityStatus = table.getCell(4, 2);
var cellqualityStatusNotes = table.getCell(4, 1);
if (quality == "Excellent") {
cellqualityStatus.asTableCell().setBackgroundColor("#CCFFCC");
cellqualityStatus.asTableCell().setForegroundColor("#404040");
cellqualityStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
cellqualityStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (quality == "Good") {
cellqualityStatus.asTableCell().setBackgroundColor("#FFFF99");
cellqualityStatus.asTableCell().setForegroundColor("#404040");
cellqualityStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
cellqualityStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (quality == "Unsatisfactory") {
cellqualityStatus.asTableCell().setBackgroundColor("#FFCCFF");
cellqualityStatus.asTableCell().setForegroundColor("#404040");
cellqualityStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
cellqualityStatusNotes.asTableCell().setForegroundColor("#404040");
};
// Get the cell of the table's sixth row and second and third column to highlight the scope status cells with appropriate status background colors
var cellscopeStatus = table.getCell(5, 2);
var cellscopeStatusNotes = table.getCell(5, 1);
if (scope == "On Scope") {
cellscopeStatus.asTableCell().setBackgroundColor("#CCFFCC");
cellscopeStatus.asTableCell().setForegroundColor("#404040");
cellscopeStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
cellscopeStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (scope == "Minor Scope Creep") {
cellscopeStatus.asTableCell().setBackgroundColor("#FFFF99");
cellscopeStatus.asTableCell().setForegroundColor("#404040");
cellscopeStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
cellscopeStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (scope == "Major Scope Creep") {
cellscopeStatus.asTableCell().setBackgroundColor("#FFCCFF");
cellscopeStatus.asTableCell().setForegroundColor("#404040");
cellscopeStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
cellscopeStatusNotes.asTableCell().setForegroundColor("#404040");
};
// Get the cell of the table's seventh row and second and third column to highlight the risk status cells with appropriate status background colors
var cellriskStatus = table.getCell(6,2);
var cellriskStatusNotes = table.getCell(6,1);
if (risk == "No Risks") {
cellriskStatus.asTableCell().setBackgroundColor("#CCFFCC");
cellriskStatus.asTableCell().setForegroundColor("#404040");
cellriskStatusNotes.asTableCell().setBackgroundColor("#CCFFCC");
cellriskStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (risk == "Minor") {
cellriskStatus.asTableCell().setBackgroundColor("#FFFF99");
cellriskStatus.asTableCell().setForegroundColor("#404040");
cellriskStatusNotes.asTableCell().setBackgroundColor("#FFFF99");
cellriskStatusNotes.asTableCell().setForegroundColor("#404040");
} ;
if (risk == "Major") {
cellriskStatus.asTableCell().setBackgroundColor("#FFCCFF");
cellriskStatus.asTableCell().setForegroundColor("#404040");
cellriskStatusNotes.asTableCell().setBackgroundColor("#FFCCFF");
cellriskStatusNotes.asTableCell().setForegroundColor("#404040");
};
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(docTitle+'.pdf');
//Delete the inermediate Gdoc file because a PDF has been created now.
DriveApp.getFileById(fileID).setTrashed(true);
}
