How should I automate graph generation and image output in Google Sheets?

Question

I am trying to send graphs of data to email recipients.

I would like to output images of row-specific graphs based on cells in a Google Sheets document. In combination with Google Sheets/Drive, I am using a mail merge add-on (YAMM) that can insert row-specific image files and need the graph images output to a directory with a public URL.

This is the most concise series of actions required to output the image file and make it accessible to the mail merge application.

Action to Automate:

• Select a range of cells in a row
• Create a graph based on that range of cells including key and labels
• Save image file of that graph in a public location
• Insert link to image file in sheet where cells are located
• Repeat on next row until blank

I am having difficulty tracking down resources on what tool to use to automate this series of actions. I would like to know if Google Apps Script is what I should be using to perform this automation. If not, is an alternative - including other add-ons or web apps - for this use case? Is there a different tack I should be taking with the data outside Sheets?

Answer 1

Yes, you can do all of this with Google Apps Script, though it's a pretty large project that arguably should not be someone's first project with GAS. Here is an overview of methods involved, with pointers to documentation.

1. Select a range of cells in a row: getRange.

2. Create a graph based on that range of cells including key and labels: see Embedded Chart creation, Setting chart type, and customizations such as EmbeddedBarChartBuilder.

3. Save image file of that graph in a public location. The only place you can save is Google Drive, as follows:

 var sheet = SpreadsheetApp.getActiveSheet();  // grabs active sheet
 var chart = sheet.getCharts()[0];   // grabs the first chart on the sheet
 var file = DriveApp.createFile(chart.getBlob());   // saves it as a file

You get a PNG file named chart.png; this file can be renamed with setName and made public as follows:

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

4. Insert link to image file in sheet where cells are located. Use getUrl to get the URL of the file. Add it to the sheet with setValue. Example:

sheet.getRange(5, 3).setValue(file.getUrl());   // automatically linked

You can make the link look fancy (with custom text) using setFormula('=hyperlink('+file.getUrl()+','+ your_text + ')') instead of setValue.

5. Repeat on next row until blank. Sure, that's what for and while loops are for, among other things such as getDataRange