A Google Apps Script(GAS) runs as a data change bot and executes the following steps
A GIF of the report in the app
Part -1
Part 2
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)
GAS1 To Update the Google slide template with values from AppSheet record and insert charts from the Google sheet into the template
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);
}
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);
}
Nice work!
Neat! Super nice @Suvrutt_Gurjar , thanks for sharing!
Good Job
@Suvrutt_Gurjar Great tip! Thank you for taking the time to post it.
Amazing! Learning a lot of app scripts use-cases from this community. Thanks @Suvrutt_Gurjar !