LinkedIn
Twitter
Overview of The Tip
Show More
- Describes the approach to create less frequency reports such as daily/weekly/monthly reports.
- Users can view the report in the app only, eliminating the need to browse emails or open attachments therein. The app need not send the reports in email with attachment.
- Uses Google Slides as template. Advantage: Better formatting of visually appealing, elegant reports for viewing in the app.
- The reports incorporate the charts created in the Google sheets. Advantage: Better looking charts with more charting options than natively available in AppSheet.
Approach
Show More
A Google Apps Script(GAS) runs as a data change bot and executes the following steps
- Read the AppSheet record
- Populates the Google Slides template with values read from the AppSheet record
- Copies and populates the Google Slides template with the latest updated Google Sheets charts. The charts are based on various tables in the app.
- Creates PNG files from the updated Google slides and stores in the G drive folder within the app folder. These PNG images are then shown as reports in the app record in show type/image category columns.
- The GAS deletes the slides filled with data so that the template is ready for the next iteration of the report creation
The Reports Image in detail view from the App
A GIF of the report in the app
Show More
Part -1
Part 2
A Sample Slide of the Google Slides Template
Show More
This slide template gets populated with the AppSheet record data. The sample report is displayed as 4 images in the app and so the template has 4 slides ( one for each image)
The GAS Code
GAS1 To Update the Google slide template with values from AppSheet record and insert charts from the Google sheet into the template
Show More
function reportImagesFill(date, sales, orders, highestProduct, topOrderID,toporderCustomer, toporderValue, toporderStatus, averageorderValue, achievedTarget, loewstProduct) {
// Google Slides template ID
let templateID = "Template_ID";
//Get the Google sheet ID where the charts to be used in the report are created
const chartsheetID = "ChartsSheet_ID";
// Open the presentation and get the slides in it.
let deck = SlidesApp.openById(templateID);
let slides = deck.getSlides();
//Open the spreadsheet that contains charts
// The name of the sheet containing the charts is" Report_Charts"
const chartsSheet = SpreadsheetApp.openById(chartsheetID).getSheetByName("Report_Charts");
// Retrive the Orders chart and products chart
const ordersChart = chartsSheet.getCharts()[0];
const productsChart = chartsSheet.getCharts()[1];
// Assign the 1st slide is the template that will be duplicated as fifth slide in the deck
let masterSlide = slides[0];
// Assign the 2nd slide is the template that will be duplicated as 6th slide in teh deck
let masterSlide2 = slides[1];
// Assign the 3rd slide is the template that will be duplicated as the 7th slide in the deck
let masterSlide3 = slides[2];
// Assihn the 4th slide is the template that will be duplicated as the 8th slide in the deck
let masterSlide4 = slides[3];
//Duplicate the 4 new assigned slides
let slide5 = masterSlide.duplicate();
let slide6 = masterSlide2.duplicate();
let slide7 = masterSlide3.duplicate();
let slide8 = masterSlide4.duplicate();
// Populate data in the slide that was created
slide5.replaceAllText("{{Date}}", date);
slide5.replaceAllText("{{Sales}}", sales);
slide5.replaceAllText("{{Orders}}", orders);
slide5.replaceAllText("{{High_Prod}}", highestProduct);
slide5.replaceAllText("{{Low_Prod}}", loewstProduct);
// Populate data in the slide that was created
slide6.replaceAllText("{{Highest_Product}}", highestProduct);
slide6.replaceAllText("{{Order_ID}}", topOrderID);
slide6.replaceAllText("{{Customer}}", toporderCustomer);
slide6.replaceAllText("{{Order_Value}}", toporderValue);
slide6.replaceAllText("{{Order_Status}}", toporderStatus);
slide6.replaceAllText("{{Ave_Val}}", averageorderValue);
slide6.replaceAllText("{{Target}}", achievedTarget);
//Insrt the Order chart as an image
slide7.insertSheetsChartAsImage(ordersChart);
//Insert the Products chart
slide8.insertSheetsChartAsImage(productsChart);
}
GAS 2 to convert the Google slides into PNG images and store the images in the G drive in a folder under the app's root folder. This allows the images to be easily accessed in the app.
Show More
function slidtoPNGLatest(dateFormatted) {
// Export slides as PNG images
// Designate the folder ID you want to put the exported PNG files.
const folderId = "Folder_ID";
// Retrieve all slides from the source Google Slides.
// const slide = SlidesApp.getActivePresentation();
// Get Google Slides template ID
let slideDeckID = "Template_ID";
// Open the presentation and get the slides in it.
let deckLatest = SlidesApp.openById(slideDeckID);
const id = deckLatest.getId();
var someSlides=deckLatest.getSlides();
var srcSlides= deckLatest.getSlides();
// Export each page as a PNG file.
const folder = DriveApp.getFolderById(folderId);
srcSlides.forEach((s, i) => {
var theDate = dateFormatted;
const url = Slides.Presentations.Pages.getThumbnail(id, s.getObjectId(), {"thumbnailProperties.mimeType": "PNG"}).contentUrl;
const blob = UrlFetchApp.fetch(url).getAs(MimeType.PNG);
folder.createFile(blob.setName(`page_${(theDate + "-")+(i + 1)}.png`));
});
// Delete the slides that have been convrted to the PNG images
someSlides[1].remove();
someSlides[3].remove();
someSlides[5].remove();
someSlides[7].remove();
var theDate = dateFormatted;
//Delete the PNG files that are not required. These are the PNG files of template variables slides
DriveApp.getFilesByName("page_"+ theDate+"-1.png").next().setTrashed(true);
DriveApp.getFilesByName("page_"+ theDate+"-3.png").next().setTrashed(true);
DriveApp.getFilesByName("page_"+ theDate+"-5.png").next().setTrashed(true);
DriveApp.getFilesByName("page_"+ theDate+"-7.png").next().setTrashed(true);
}
Acknowledgents
Show More
Acknowledgements
The GAS scripts documentation by AppSheet team has been helpful at various stages for integrating AppSheet and GAS.
I have referred several posts by User Tanaike - Stack Overflow on StackOveflow site to get ideas on GAS Google slides to PNG conversion. I have also followed YouTube videos by Laurence Svekis on relevant GAS scripts for inserting G Sheet charts into Google slides
The GAS scripts documentation by AppSheet team has been helpful at various stages for integrating AppSheet and GAS.
I have referred several posts by User Tanaike - Stack Overflow on StackOveflow site to get ideas on GAS Google slides to PNG conversion. I have also followed YouTube videos by Laurence Svekis on relevant GAS scripts for inserting G Sheet charts into Google slides
Points to note
Show More
1. The approach maybe unsuitable for very high frequency reports because Google Workspace has limits on how many slides can be generated on a daily basis. Each report runs requires 4 PNG images to be created. Also the entire process of running GAS and creating and storing images etc, takes some time ( 30sec to 1 min) That is why suggestion to use the approach for less frequency reports.
2. The same could be achieved with SVG files. The choice is individual. As per my understanding the GAS approach has better charting options but lacks in very frequent or instantaneous updates that SVG can do, Will request insights /comments from community colleagues.
