I have a sheet that has more than 30 charts and my employees need to download all charts every hour, around 24/7 which takes much time for them. so my question is, is there is any way to download all charts in the sheets with one click or in an automated way?
CodePudding user response：
I believe your goal is as follows.
You want to retrieve all charts from all sheets in a Google Spreadsheet.
You want to download all chart images as a PDF file.
From your following replying, I understood like above
> the current issue is that my team is working on hourly performance for the agents and we have many teams and we have charts for performance and quality so the total charts that need to be downloaded and sent to each team are around 30 charts which translates to 30 downloads to each chart image. so this is happening every hour for 24 hours for the whole day which is 24 hours across week, month, year. so imagine if each hour takes 5 minutes to just download this will result in 120 mins or 2 hours a day, in a month this will cost us 14 hours per week, 60 hours per month. > I know that I can add the charts to Google slides and export them as pdf, but the managers wouldn't prefer that idea that much
But, from your question and replying, I couldn't understand where you want to download the PDF file to? So in this answer, I would like to propose the following 2 patterns.
- Download chart images as a PDF to Google Drive.
- Download chart images as a PDF to a local PC.
Pattern 1:
In this pattern, the chart images are downloaded as a PDF to Google Drive. Please copy and paste the following script to the script editor of Google Spreadsheet. In this case, when myFunction() is run, the PDF file is downloaded to Google Drive.
function myFunction() {
const outputFilename = "sample.pdf";
// 1. Retrieve all charts in a Google Spreadsheet.
const charts = SpreadsheetApp.getActiveSpreadsheet().getSheets().flatMap(s => s.getCharts());
// 2. Create new Google Slides as a temporal file.
const s = SlidesApp.create("temp");
// 3. Put the charts to each slide.
let slide = s.getSlides()[0];
slide.getShapes().forEach(e => e.remove());
charts.forEach((c, i, a) => {
slide.insertSheetsChart(c).alignOnPage(SlidesApp.AlignmentPosition.CENTER);
if (i < a.length - 1) slide = s.appendSlide();
});
s.saveAndClose();
// 4. Output Google Slides as a PDF data.
const file = DriveApp.getFileById(s.getId());
DriveApp.createFile(file.getBlob().setName(outputFilename));
// 5. Remove the temporal file of Google Slides.
file.setTrashed(true);
}
Pattern 2:
In this pattern, the chart images are downloaded as a PDF to a local PC. This method is from this answer. In this case, when download() is run, the PDF file is downloaded to the local PC using Javascript on the opened dialog.
But, in this pattern, it is required to run the script by the user on browser. Because Javascript is used on the browser. Please be careful this.
Code.gs
Please copy and paste the following script to the script editor of Google Spreadsheet as a script.
function download() {
const html = HtmlService.createHtmlOutputFromFile("index");
SpreadsheetApp.getUi().showModalDialog(html, "sample");
}
function downloadFile() {
const outputFilename = "sample.pdf";
// 1. Retrieve all charts in a Google Spreadsheet.
const charts = SpreadsheetApp.getActiveSpreadsheet().getSheets().flatMap(s => s.getCharts());
// 2. Create new Google Slides as a temporal file.
const s = SlidesApp.create("temp");
// 3. Put the charts to each slide.
let slide = s.getSlides()[0];
slide.getShapes().forEach(e => e.remove());
charts.forEach((c, i, a) => {
slide.insertSheetsChart(c).alignOnPage(SlidesApp.AlignmentPosition.CENTER);
if (i < a.length - 1) slide = s.appendSlide();
});
s.saveAndClose();
// 4. Output Google Slides as a blob of PDF data.
const file = DriveApp.getFileById(s.getId());
const blob = file.getBlob().setName(outputFilename);
// 5. Remove the temporal file of Google Slides.
file.setTrashed(true);
// 6. Return data as base64.
return {data: `data:${MimeType.PDF};base64,${Utilities.base64Encode(blob.getBytes())}`, filename: outputFilename};
}
index.html
Please copy and paste the following script to the script editor of Google Spreadsheet as HTML.
<script>
google.script.run
.withSuccessHandler(({ data, filename }) => {
if (data && filename) {
const a = document.createElement("a");
document.body.appendChild(a);
a.download = filename;
a.href = data;
a.click();
}
google.script.host.close();
})
.downloadFile();
</script>
Note:
- The above pattenrs are the simple sample scripts. So please modify them for your actual situation.