GSC brand unbrand script

GSC brand unbrand script

function calculateAndCreatePieChart() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Queries');
  var chartSheetName = 'brand';
  var brandstring = "radar";
  
  // Remove old chart sheet if exists
  var oldChartSheet = spreadsheet.getSheetByName(chartSheetName);
  if (oldChartSheet) spreadsheet.deleteSheet(oldChartSheet);

  var data = sheet.getDataRange().getValues();
  
  var brandedClicks = 0;
  var unbrandedClicks = 0;
  var totalClicks = 0;
  for (var i = 1; i < data.length; i++) {
    var query = data[i][0];
    var clicks = data[i][1];
    if (query.includes(brandstring)) {
      brandedClicks += clicks;
    } else {
      unbrandedClicks += clicks;
    }
    totalClicks += clicks;
  }

  var approxBrandedQueries = brandedClicks;
  var approxUnbrandedQueries = unbrandedClicks;
  
  var maxMarginOfError = totalClicks / (brandedClicks + unbrandedClicks);

  var chartSheet = spreadsheet.insertSheet(chartSheetName);
  var debugData = [
    ['Type', 'Approximate Query Numbers', 'Approximate Maximum Margin of Error'],
    ['Branded', approxBrandedQueries, maxMarginOfError],
    ['Unbranded', approxUnbrandedQueries, maxMarginOfError]
  ];

  var debugRange = chartSheet.getRange(1, 1, debugData.length, debugData[0].length);
  debugRange.setValues(debugData);
  
  var brandedPercentage = (brandedClicks / totalClicks) * 100;
  var unbrandedPercentage = (unbrandedClicks / totalClicks) * 100;

  var chartData = [
    ['Type', 'Percentage'],
    ['Branded ('+brandedClicks+')', brandedPercentage],
    ['Unbranded ('+unbrandedClicks+')', unbrandedPercentage]
  ];

  var range = chartSheet.getRange(5, 1, chartData.length, chartData[0].length);
  range.setValues(chartData);

  var chartBuilder = chartSheet.newChart();
  chartBuilder.setChartType(Charts.ChartType.PIE)
              .addRange(range)
              .setPosition(7, 5, 0, 0)
              .setOption('title', 'Branded vs Unbranded Clicks')
              .setOption('pieSliceText', 'percentage');

  chartSheet.insertChart(chartBuilder.build());
}