1

I'm using Google Sheets and a triggered Apps Script function to create a waterfall chart. Basically this involves breaking apart each value into a base (either visible or invisible), a falling value (one color) and a rising value (another color). I'm then using Apps Script to dynamically adjust the minimum and maximum to properly show the range.

This all works great, however sometimes the data labels start duplicating. I have no idea what might be causing this - I'm not touching labels in apps script at all.

Here's what the chart looks like with duplications: chart with duplications

Here's my script code:

function modifyAxis() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var charts = sheet.getCharts();

  for (var i in charts) {
    var row = charts[i].getContainerInfo().getAnchorRow();
    var chart = charts[i].modify()
    .setOption('title', sheet.getRange("R" + row).getValue())
    .setOption('hAxis.slantedTextAngle','45')
    .setOption('vAxis.viewWindowMode','explicit')
    .setOption('vAxis.viewWindow.min',sheet.getRange("W" + (row+15)).getValue())
    .setOption('vAxis.viewWindow.max',sheet.getRange("W" + (row+16)).getValue())
    .setOption('bar.groupWidth','90%')
    .build();
    Logger.log("Min=" + sheet.getRange("W" + (row+15)).getValue());
    sheet.updateChart(chart);
  }
}

Any thoughts?

The chart certainly didn't start out this way, and it's pretty unusable as automation to allow a customer to use it.

  • Hard to say anything for sure without having a reproducible example. It may be that viewWindow adjustments cause the repainting of the chart, with the label placed elsewhere. I'd try removing some of the methods to find out which one causes duplication. – user135384 Dec 10 '16 at 23:48

Your Answer

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

Browse other questions tagged or ask your own question.