2

I am new to google apps scripts and coding but I created a Sankey diagram in google apps script using the google visualization HTML code. I am using the data from a google sheet because it is dynamic data. Currently I successfully deployed the diagram as a web app that updates when refreshed. However, I am wondering if it's possible to put the sheet IN the google sheet - maybe as a separate tab? I want to be able to share the google sheet and have the data and Sankey diagram immediately available. Here is my code for grabbing the spreadsheet data and publishing it as a web app:

function doGet(e) {

  return HtmlService
    .createTemplateFromFile("Index")
    .evaluate()
    .setTitle("Google Spreadsheet Chart")
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getSpreadsheetData() {

  var ssID = "XXXXXXXXXXXXXXX",
    sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
    data = sheet.getDataRange().getValues();

  return data;

}
Caroline
  • 31
  • 3
  • Consider hiding critical information like `ssID`. You can use `var ssID = "xxxxxxxxxx"` :) –  May 14 '20 at 17:11
  • 1
    `Embedded Chart Class` is different from JavaScript html chart api and conversion is not possible between the two. You should recreate the chart using embedded chart class or insert the html chart as plain image. – TheMaster May 14 '20 at 18:08
  • Take a look at [EmbeddedChart](https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart) and [EmbeddedChartBuilder](https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart-builder). – Iamblichus May 15 '20 at 07:33
  • 1
    Thank you for your answers and help! I hid my critical information. And I just used a menu and openDiagram that opens a box with the Sankey diagram. – Caroline May 21 '20 at 00:00
  • Would you consider posting an answer explaining in more detail what you did? – Iamblichus May 21 '20 at 07:55
  • 1
    @Iamblichus sure! – Caroline May 26 '20 at 19:32

1 Answers1

1

This is for many people to view so I had an alert pop up onOpen that says "Look at the diagram menu" and then a menu pop up onOpen to click on the diagram.

function onOpen() {
  alert();
  menu();
  getSpreadsheetData();
  openDiagram();

}

function alert() {
  var ui = SpreadsheetApp.getUi();
  ui.alert('To see diagrams for different brands, click on the "Diagram" menu.');
}

function menu() {
  SpreadsheetApp.getUi()
      .createMenu('Diagram')
      .addItem('Diagram', 'openDiagram')
      .addToUi();
}

function getSpreadsheetData() {

  var 
    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data'),
    data = sheet.getDataRange().getValues();

  return data;

}

function openDiagram() {
  var html = HtmlService.createHtmlOutputFromFile('Index')
    .setWidth(1000)
    .setHeight(800);
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'Diagram');
}

Then when I click on the diagram under the menu a window opens with my diagram.

Caroline
  • 31
  • 3