3

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?

3

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
  • Thanks for the level of detail; this should really be helpful for anyone looking to automate this, in addition to this GAS first-timer. – mfg Jan 13 '16 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.