I need a to build a chart/graph with Apps Script that looks like one I hand made in googe sheets

Question

So below is a dynamic graph this I made directly in google sheets. It updates automatically. So when I want to create a report, I use Apps Script to convert it to an image and copy it into a google doc.

Here’s my problem: The graph above represents a cummulative graph based on date ranges that the user picks.

What I now need is to creted daily graphs within that range to break these statistics down by day. Because I never know how many days will be in the range that they specify, I have to build these graphs programmatically then insert them into the google sheet.

I have figured out how to do this, but the closest I’ve been able to get in making the daily graphs look like the one above is shown below:

I simply cannot find the information I need to make the second chart look similar to the first one. Anyone have advice that might help?

question from:https://stackoverflow.com/questions/65905309/i-need-a-to-build-a-chart-graph-with-apps-script-that-looks-like-one-i-hand-made

Answer 1

You can copy your existing chart and modify its properties to generate a new chart.

Sample Code:

function insertChart(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet5");

  var charts = sheet.getCharts();

  for(var i in charts){

    if(charts[i].getOptions().get('title')== 'Types of Incidents Over Time'){
      var chart = charts[i];
      var modifiedChart = chart
          .modify()
          .clearRanges()
          .addRange(sheet.getRange("A25:B29"))
          .setOption('title','New Chart')
          .setOption('vAxis.gridlines', {minSpacing: 2})
          .setOption('vAxis.minorGridlines.count', 1)
          .setPosition(25,5,0,0)
          .build();
        
      sheet.insertChart(modifiedChart);
    }
    
  }
}

Output:

---

What it does?

1. Get all the existing chart in a specific sheet using Sheet.getCharts()

2. Select your desired chart based on its chart title using EmbeddedChart.getOptions() and ChartOptions.get(option). For a list of available options for Column Chart, please refer here.

3. Once your desired chart was selected, you can modify the properties of the selected chart using EmbeddedChart.modify(). It will return an EmbeddedChartBuilder where you can configure your chart's properties. See EmbeddedChartBuilder Methods for additional information

In the sample code, I cleared the existing range first before setting a new data range. After that, I configured some chart options such as the Title, Vertical Axis Gridlines Minspacing, Position, etc.

Note: I had to configured the vAxis.gridlines minspacing manually since when I tried to get the vAxis.gridlines of the existing chart it returned a null value. Chart Options details are discussed here.

4. Once you have configured your chart, you need to build chart to reflect all changes made to it using EmbeddedChartBuilder.build() which will create an EmbeddedChart

5. Insert your newly created EmbeddedChart using Sheet.insertChart(chart)