LinkedIn
Twitter
We always desire to add the charts with the latest updated data from the app in the email PDF reports created through automation.
However the current issues with the charts inclusion options in emails are
 | Charts included through SNAPSHOT() function are real time but not very legible due to small fonts and screenshot being of the entire app screen. Also at times the SNAPSHOT() function misbehaves. |
 | Google sheets Charts If these are included in the email templates they will not update unless you click on the update button on the top right,. You cannot do this button tapping every time by going inside the email template before sending the email. |
The solution :
The approach below overcomes the above issues and updates the Google sheets charts embedded in the email template with the latest app data without the need to manually update the charts in the template. The solution needs a small GAS script and is possible due to new GAS integration in AppSheet. The GAS script basically updates the email template with the latest updated G sheet charts.
The Google sheet charts look neat and clean in the template and emails received.
The setup steps are
A) Create the required charts in the Google Sheet based on the app data and embed the charts in the email template
| The charts created in the Google sheets based on app data |
| The G sheet charts embedded in the email template. |
B)The automation bot setup is as follows
| 1. Update the app record: This step updates the table record that triggers the bot on a data change event. As soon as record is updated, the Google sheet based charts based on those records are updated. |
| 2. This step runs the GAS script. The most important step of this solution. The GAS script basically deletes the old chart images from the email template and adds the new latest charts that have been updated due to updates in app records in step 1 |
| 3. This is a precautionary wait step of 5 minutes that is added to allow the changes of "deletion of earlier chart images and addition of latest chart images" in step 2 to settle down |
| 4. This final step basically sends the email through the bot based on the latest template that has been updated with the latest updated chart images in the GAS script step. |
The GAS script is as follows : The G sheet chart images in the G sheet update as soon as the app records are updated. The GAS script basically deletes the old G sheet based chart images in the template and replaces with the latest chart images. So every time the bot email is sent, the Gsheet based charts pasted in the template and hence in email body and PDF are with the latest data.
function getChart() {
//Get the Google doc email template by ID
var templateGdoc=DocumentApp.openById('Google Doc Template File ID');
var templatebody = templateGdoc.getBody();
// Get the spreadsheet where the Google sheet chart is created
var chartsSheet = SpreadsheetApp.openById('Google Sheet ID Where Charts are created');
//Get the sheets containing charts. The sheets containing charts are titled "ProductCharts" in the back end Google sheet
var sheet = chartsSheet.getSheetByName("ProductCharts");
//Remove the earlier chart images from the Goohgle doc email template
var deletecharts = templatebody.getImages();
for (var i = 0; i < deletecharts.length; i++) {
deletecharts[i].removeFromParent();
};
//Append the latest Chart images from the "ProductCharts" and "Charts" sheets
var appendcharts =sheet.getCharts();
for (var i = 0; i < appendcharts.length; i++) {
templatebody.appendImage(appendcharts[i]);
Logger.log(appendcharts[i].getBlob().getName());
}
}
This setup ensure the automation emails are sent with charts having the latest app data.
Finally the result
SNAPSHOT() based emailed PDF screenshot below where the axes letters are illegible and there are unnecessary headers and footers from the app screen.
The GAS script solution based emailed PDF below where the axes letters are much more prominent and there is no other unnecessary clutter of app header footers, allowing more area for the chart itself.
Hope this helps.
