Add Pie Charts to Google Sheets JavaScript App Script

add_pie_charts.js

function AddColumnChart(spreadsheet, sheet) {
  chart = sheet.newChart()
  .asColumnChart()
  .addRange(spreadsheet.getRange('A1:M11'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(-1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('bubble.stroke', '#000000')
  .setOption('isStacked', 'absolute')
  .setOption('annotations.domain.textStyle.color', '#808080')
  .setOption('textStyle.color', '#000000')
  .setOption('legend.textStyle.color', '#1a1a1a')
  .setOption('titleTextStyle.color', '#757575')
  .setOption('annotations.total.textStyle.color', '#808080')
  .setOption('hAxis.textStyle.color', '#000000')
  .setOption('vAxes.0.minorGridlines.count', 2)
  .setOption('vAxes.0.textStyle.color', '#000000')
  .setOption('height', 600)
  .setOption('width', 1500)
  .setPosition(15, 1, 40, 7)
  .build();
  sheet.insertChart(chart);
}

function AddPieMetricForName(spreadsheet, name, idx, currentRowPos, currentColPos, offsetX) {
  spreadsheet.getRange('A1:M11').activate();
  var sheet = spreadsheet.getActiveSheet();

  var chart = sheet.newChart()
  .asPieChart()
  .addRange(spreadsheet.getRange('A1:M1'))
  .addRange(spreadsheet.getRange(`A${idx + 2}:M${idx + 2}`))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
  .setTransposeRowsAndColumns(true)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('bubble.stroke', '#000000')
  .setOption('title', name)
  .setOption('annotations.domain.textStyle.color', '#191919')
  .setOption('textStyle.color', '#000000')
  .setOption('legend.textStyle.color', '#191919')
  .setOption('pieSliceTextStyle.color', '#000000')
  .setOption('titleTextStyle.color', '#191919')
  .setOption('annotations.total.textStyle.color', '#191919')
  .setPosition(currentRowPos, offsetX, currentColPos, 1)
  .build();
  sheet.insertChart(chart);
}

function AddPieMetricsImproved() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  AddColumnChart(spreadsheet, sheet);

  var dataRange = spreadsheet.getRange("Dashboard!A2:A").getValues();
  var currentRowPos = 45;
  var currentColPos = 46;
  var offsetX = 1;
  var counter = 1;
  dataRange.forEach(([name], i) => {
    if (name.length > 0) {
      AddPieMetricForName(spreadsheet, name, i, currentRowPos, currentColPos, offsetX);
      currentColPos += 625;
      
      if (counter == 4) {
        currentRowPos += 18;
        currentColPos = 47;
        counter = 0;
      }
      
      counter += 1;
    }
  });
};