CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscharts
jjones150
jjones150

Reputation: 302

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

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.

enter image description here

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:

enter image description here

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?

Upvotes: 0

Views: 854

Answers (1)

Kristkun
Kristkun

Reputation: 5963

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:

enter image description here

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.

  1. 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

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

Upvotes: 1

Related Questions