Line Chart Visualisation of Google Search Console Data in App Script

createLineChartWithQuartiles(sheetName)

function runAnalyses() {
  createLineChartWithQuartiles('Queries');
  createLineChartWithQuartiles('Pages');
  createLineChartWithQuartiles('Countries');
  createLineChartWithQuartiles('Devices');
  createLineChartWithQuartiles('Dates');
}

function createLineChartWithQuartiles(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var totalItems = sheet.getLastRow() - 1; // Subtract 1 for the header row
  
  // Calculate total clicks
  var clicksArray = sheet.getRange("B2:B" + sheet.getLastRow()).getValues();
  var totalClicks = 0;
  clicksArray.forEach(function(row) {
    totalClicks += row[0];
  });

  // Add new columns for quartiles and percentages
  sheet.getRange("F1").setValue("Quartiles");
  sheet.getRange("G1").setValue("Percentage of Total Clicks");
  sheet.getRange("H1").setValue("Percentage of Total Clicks Sum");

  var clickSum = 0;
  var gValues = [];
  var hValues = [];
  var fValues = [];
  
  for (var i = 1; i <= totalItems; i++) {
    var clickValue = clicksArray[i-1][0];
    clickSum += clickValue;
    gValues.push([(clickValue / totalClicks * 100).toFixed(2) + '%']);
    hValues.push([(clickSum / totalClicks * 100).toFixed(2) + '%']);
    fValues.push(['']);

    if(i % Math.round(totalItems / 5) == 0) {
      fValues[i-1] = [(i / totalItems * 100) + '%'];
    }
  }

  sheet.getRange("G2:G" + (totalItems + 1)).setValues(gValues);
  sheet.getRange("H2:H" + (totalItems + 1)).setValues(hValues);
  sheet.getRange("F2:F" + (totalItems + 1)).setValues(fValues);

  // Create the charts
  createChart(sheet, "B", sheetName + ' vs Clicks', 'blue', 5);
  createChart(sheet, "G", sheetName + ' vs Percentage of Total Clicks', 'green', 25);
  createChart(sheet, "H", sheetName + ' vs Percentage of Total Clicks Sum', 'purple', 45);
}

function createChart(sheet, column, title, color, position) {
  var chartBuilder = sheet.newChart();
  var lastRow = sheet.getLastRow();
  chartBuilder
    .addRange(sheet.getRange("A1:A" + lastRow)) // Query data (always from column A)
    .addRange(sheet.getRange(column + "1:" + column + lastRow)) // The data to visualize
    .setChartType(Charts.ChartType.LINE)
    .setOption('title', title)
    .setOption('hAxis.title', sheet.getName()) // Adjusted to use the name of the sheet
    .setOption('vAxis.title', title.split(' ')[2])
    .setOption('series', {0: {color: color, visibleInLegend: true}})
    .setPosition(position, 10, 0, 0);

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