Embedding a Chart to a Cell using Custom Function on Spreadsheet

GasTips / GoogleChartTips
Google Apps Script / spreadsheet / chart

This sample script embeds a chart to a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

When you want to create a chart and embed it to a cell using custom functions, you notice that insertChart() cannot be used. There are some limitations for using custom functions. But insertChart() creates floating charts. So in order to embed a chart to a cell, the function =IMAGE() is suitable for this situation. Here, setFormula() for setting =IMAGE() and DriveApp.createFile() for creating images from charts also cannot be used for custom functions.

Solution

In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.

To use this sample script, please deploy Web Apps as follows.

On the Script Editor,

  • File
    • -> Manage Versions
    • -> Save New Version
  • Publish
    • -> Deploy as Web App
    • -> At Execute the app as, select “your account”
    • -> At Who has access to the app, select “Anyone, even anonymous”
    • -> Click “Deploy”
    • -> Copy “Current web app URL”
    • -> Click “OK”

When it deploys Web Apps, the approval required authorization can be done, simultaneously.

Sample Script :

Please copy and paste this script to a bound script of spreadsheet.

var folderId = "### Folder ID ###";
var webappsurl = "https://script.google.com/macros/s/######/exec";

function embedChart(range) {
  var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
  var q1 = "?datarange=" + range;
  var q2 = "&row=" + ac.getRow();
  var q3 = "&col=" + ac.getColumn();
  var url = webappsurl + q1 + q2 + q3;
  UrlFetchApp.fetch(url);
}

function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange(e.parameters.datarange))
    .setOption('height', 280)
    .setOption('width', 480)
    .setOption('title', 'Sample chart')
    .build();
  var file = DriveApp.getFolderById(folderId).createFile(
    chart.getAs('image/png').setName("chart_image.png")
  );
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  sheet.getRange(e.parameters.row, e.parameters.col).setFormula(
    '=IMAGE("' + "http://drive.google.com/uc?id=" + file.getId() + '")'
  );
}

Flow of Script :

embedChart()

  1. Input =embedChart("a2:a6") in cell B7.
  2. Using fetch(), sends data of a2:a6 and the inputted coordinate to doGet().

doGet()

  1. Using doGet(), get the data.
  2. Creates a chart using inputted range a2:a6. (in this case, creates a pie chart)
  3. Saves a chart as an image. (in this case, saves as PNG)
  4. Updates a permission of the image file to use for =IMAGE().
  5. Embeds the image using =IMAGE() which was imported by setFormula().

Result :

By inputting =embedChart("a2:a6") in cell B7 as a custom function, following result can be obtained.

Note :

  1. When the custom function embedChart() is used, loading time is about 40 seconds. (I don’t know whether this occurs only my environment.)
  2. Permissions of the created image are ANYONE_WITH_LINK, VIEW.
  3. embedChart() is overwritten by =IMAGE(). So when the spreadsheet is reopened, the response of =IMAGE() is much faster than that of embedChart().

 Share!