Get Chart Image from Google Sheet

INTRO

We have a google sheet with some data, and from that data we have made a chart on the sheet. What would be good is if we could get that exact chart appearing on our app. With thanks to the supreme master of all things Google Apps Script - Tanaike, who has devised a method to save out an image of the chart correctly, and a close second in the form of Amit Agarwal, who provides a method for overwriting a file, we can combine their methods to create an image of our chart and provide the image ID for use in our app.

This is not necessarily the best or quickest method for getting a chart into App Inventor, it is just another method for doing that job, and has its options and uses....

SCRIPT

The script combines Tanaikes and Amits work. We open the spreadsheet, and get the correct sheet and the correct chart. Then a google slides presentation is created to accept the image, then an imageBlob is created from this. We then discard the google slides presentation. Then we check if our file, pie.png, exists. If it doesn't, then we create it from the blob, if it does we overwrite the existing file with a new version, retaining the fileID, which, in both scenarios is then returned as output.

It probably makes sense to create the web app "bound to the spreadsheet" for this application, but the script is written so that it can be used as a standalone web app as well.

//make or overwrite Chart Image

function doGet() {

var fileID = '';

const chart = SpreadsheetApp.openById("1qTMy_uGz5_5h9...RMli30EcZr-Y").getSheets()[0].getCharts()[0];

const slides = SlidesApp.create("temp");

const imageBlob = slides.getSlides()[0].insertSheetsChartAsImage(chart).getAs("image/png");

DriveApp.getFileById(slides.getId()).setTrashed(true);

var folder = DriveApp.getFolderById('0B5al230K...Z2pOVWM');

var filename = 'pie.png';

var existing = folder.getFilesByName(filename);

if (existing.hasNext()) {

var file = existing.next();

if (file.getName() === filename) {

fileID = file.getId();

Drive.Files.update({title: file.getName(), mimeType: file.getMimeType()}, file.getId(), imageBlob);

}

} else {

fileID = folder.createFile(imageBlob.setName("pie.png")).getId();

}

return ContentService.createTextOutput("Chart created with ID: " + fileID);

}

When creating the script, you will need to add the Advanced Drive Service. With the legacy editor you do this by going to Resources > Advanced Google Services, then setting "Drive API" to "On".

"MAGIC NUMBERS/TEXTS"

There are several magic numbers or texts in the script, which leave it open for further development and /or integration with an App Inventor app. These can all be replaced with variables, and the web app can then be fed with parameters for these variables:

• The spreadsheet ID - SpreadsheetApp.openById("1qTMy_uGz5_5h9...RMli30EcZr-Y")

• The sheet number - getSheets()[0]

• The chart number - getCharts()[0]

• The folder ID - DriveApp.getFolderById('0B5al230K...Z2pOVWM')

• The filename - twice - 'pie.png'

• The ContentService return - ("Chart created with ID: " + fileID)

BLOCKS

It takes a little while for the google end of things to complete, so we add a progress dialog to keep the user company. There is a partial error check for the response Content, but we probably need to show a message if the fetching of the image ID fails.

VIDEO