Skip to content
Advertisement

Pass Google Apps Script (GAS) – Charts Service chart to HTML Template to create within GAS to create Modeless Dialog Box

User: Tanaike kindly answered my question here:

Pass Google Apps Script (GAS) – Charts Service chart to HTML Template within GAS

However, I realized that I’d like to also edit my template and use a modeless dialog box to do some formatting prior to sending an email; I find it helpful to preview my work in the modeless dialog box.

While I can now send charts within an email, which is great, after trying and not succeeding, I realized I’m not aware if it’s possible to pass the chart image to a modeless dialog box. I continue to receive broken links.

The chart data is all boilerplate code from https://developers.google.com/apps-script/reference/charts

////////////////////////////////////// GAS code .gs ↓↓↓

function sample() {
  var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, 'Month')
    .addColumn(Charts.ColumnType.NUMBER, 'In Store')
    .addColumn(Charts.ColumnType.NUMBER, 'Online')
    .addRow(['January', 10, 1])
    .addRow(['February', 12, 1])
    .addRow(['March', 20, 2])
    .addRow(['April', 25, 3])
    .addRow(['May', 30, 4])
    .build();
  var chart = Charts.newAreaChart()
    .setDataTable(data)
    .setStacked()
    .setRange(0, 40)
    .setTitle('Sales per Month')
    .build();


  var htmlOutput = HtmlService.createHtmlOutput().setTitle('My Chart').setWidth(1000).setHeight(1000);

  var blob = chart.getAs('image/png'); 
  var imageData = Utilities.base64Encode(blob.getBytes()); 

  var imageUrl = "data:image/png;base64," + encodeURI(imageData);

  var templ = HtmlService.createTemplateFromFile('html'); // HTML template to add 
  var message = templ.evaluate().getContent();
  htmlOutput.append(message)
  var info = "I can generate the chart in this Modeless Dialog Box"
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, info); // this generates dialog
  
}

////////////////////////////////////// HTML ↓↓↓

<!DOCTYPE html>
<HTML>
<head>
  <base target="_top">
  <style type="text/css">
    div {
      text-align: center;
    }
  </style>
</head>

<body>
  <h2>I would like the generated Chart below here in an modal dialog box↓↓↓:</h2>

  <img src="imageUrl"> <!-- ?????? This is my primary issue --> 

  <p>
    I would like the generated Chart to be above here in the modeless dialog box ↑↑↑
  </p>
</body>
</html>

Answer

Unfortunately, the value of message doesn’t include the image data. So, in order to achieve your goal, how about modifying it as follows.

Modified script:

Google Apps Script side:

function sample() {
  var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, 'Month')
    .addColumn(Charts.ColumnType.NUMBER, 'In Store')
    .addColumn(Charts.ColumnType.NUMBER, 'Online')
    .addRow(['January', 10, 1])
    .addRow(['February', 12, 1])
    .addRow(['March', 20, 2])
    .addRow(['April', 25, 3])
    .addRow(['May', 30, 4])
    .build();
  var chart = Charts.newAreaChart()
    .setDataTable(data)
    .setStacked()
    .setRange(0, 40)
    .setTitle('Sales per Month')
    .build();

  var htmlOutput = HtmlService.createHtmlOutput().setTitle('My Chart').setWidth(1000).setHeight(1000);

  var blob = chart.getAs('image/png');
  var imageData = Utilities.base64Encode(blob.getBytes());
  var imageUrl = "data:image/png;base64," + imageData;

  var templ = HtmlService.createTemplateFromFile('html'); // HTML template to add 
  var message = templ.evaluate().getContent();
  htmlOutput.append(message.replace("cid:sampleImage", imageUrl));

  var info = "I can generate the chart in this Modeless Dialog Box"
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, info);

  // If you want to send an email. You can use the following script.
  // MailApp.sendEmail({to: "###", subject: "###", htmlBody: message, inlineImages: { sampleImage: blob }});
}

HTML side:

<!DOCTYPE html>
<HTML>
<head>
  <base target="_top">
  <style type="text/css">
    div {
      text-align: center;
    }
  </style>
</head>

<body>
  <h2>I would like the generated Chart below here in an modal dialog box↓↓↓:</h2>

  <img src="cid:sampleImage">

  <p>
    I would like the generated Chart to be above here in the modeless dialog box ↑↑↑
  </p>
</body>
</html>
  • In this modification, when a dialog is opened, cid:sampleImage is replaced with the data of imageUrl. And, when an email is sent, cid:sampleImage is used. Namely, the image data is replaced for the dialog and the email.
Advertisement