Google-sheets – How should I automate graph generation and image output in Google Sheets

automationgoogle sheetsgoogle-apps-scriptmail-merge

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?

Best Answer

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);
  1. 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.

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