Home > Software design >  How to get the selected chart in a Google spreadsheet with Apps Script
How to get the selected chart in a Google spreadsheet with Apps Script

Time:11-22

I am trying to change the color of charts in Google spreadsheet. This is working for all charts in one sheet.

function colorChart() {
  let $Sheet = SpreadsheetApp.getActiveSheet();
  let $Charts = $Sheet.getCharts();
  let $Colors = {
    'One': '#6E6E6E',
    'Two': '#FFED00',
    'Three': '#238C96',
  };

  for (let $i in $Charts) {
    let $Chart = $Charts[$i].modify()
      .setOption('series.0.color', $Farben['One'])
      .setOption('series.1.color', $Farben['Two'])
      .setOption('series.2.color', $Farben['Three'])
      .build();
    $Sheet.updateChart($Chart);
  }
}

In the documentation I can only find getCharts() which returns an array of all charts in the active sheet.

Is it possible to get the selected chart only?

Unfortunately the question was not answered here: enter image description here

  • Sample drop-down menu on the HTML page (due to limitations of drop-down on screen capture)

enter image description here

Sample apps script file & HMTL file:

NOTE: Community members don't code for you but you can use these sample codes (derived from several sample codes scoured over the internet) below to get you started.

Code.gs

/**Creates a custom menu to load an edit chart sidebar.*/
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .createMenu('Edit Chart')
    .addItem('Start', 'showSidebar')
    .addToUi();
}

/**Shows the custom html sidebar.*/
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Page')
    .setTitle('Edit Charts');
  SpreadsheetApp.getUi()
    .showSidebar(html);
}

/**Function to return the current charts on a sheet.*/
function getCurrentChartsOnSheet() {
  var charts = buildMenu();
  return charts;
}

/**Function to get the current charts on a sheet.*/
function buildMenu() {
  var getChartsMenu = [];
  let $Sheet = SpreadsheetApp.getActiveSheet();
  let $Charts = $Sheet.getCharts();

  for (let $i in $Charts) {
    var currentChart = $Charts[$i].getOptions().get('title');
    getChartsMenu.push([$i   ' - '   (currentChart.length < 1 ?
      $Charts[$i].modify().getChartType().toString() : currentChart)])
  }

  return getChartsMenu; //Structure of the array will be: [chart index #] - [Chart type]
}

/**Function to edit the selected chart.*/
function editSelectedChart(selection) {
  let $Sheet = SpreadsheetApp.getActiveSheet();
  let $Charts = $Sheet.getCharts();
  let $Colors = {
    'One': '#6E6E6E',
    'Two': '#FFED00',
    'Three': '#238C96',
  };

  let $Chart = $Charts[selection.match(/\d /g)].modify()
    .setOption('series.0.color', $Colors['One'])
    .setOption('series.1.color', $Colors['Two'])
    .setOption('series.2.color', $Colors['Three'])
    .build();
  $Sheet.updateChart($Chart);
}

Page.html

<!DOCTYPE html>
<html>
<title>Edit Charts</title>

<head>
  <style>
    .space {
      width: 4px;
      height: auto;
      display: inline-block;
    }
  </style>
  <script>
    google.script.run.withSuccessHandler(onSuccess).getCurrentChartsOnSheet();

    function onSuccess(data) {
      var select = document.getElementById("selectChart");
      var options = data;
      for(var i = 0; i < options.length; i  ) {
          var opt = options[i];
          var el = document.createElement("option");
          el.textContent = opt;
          el.value = opt;
          select.appendChild(el);
      }
    }

    function passSelectedChart(){
      var e = document.getElementById("selectChart");
      google.script.run.editSelectedChart(e.value); //<-- PASS the selected drop-down data to GOOGLE SCRIPT function named editSelectedChart()
    }
          
  </script>
</head>

<body>

  <h3>Choose a chart to edit:</h3>
  <select id="selectChart">
    <option>No Selection</option>
  </select>

  <div>
    <p><input type="button" value="Apply" onclick="passSelectedChart()"></p>
  </div>

  <div>
    <p><input type="button" value="Close" onclick="google.script.host.close()"></p>
  </div>

</body>

</html>

References

  • Related