currently for TR.client

Google-Apps-Script-URL-Analysis

function analyzeURLs() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

    deleteSheets(['Page Type Analysis', 'Language Namespace Analysis', 'Parameters Analysis', 'Copied & Analyzed']);

  // Create a copy of the original sheet
  const newSheet = sheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  newSheet.setName('Copied & Analyzed');

  const pageTypeCount = calculatePageTypeCount(data);
  const langCount = calculateLangNamespace(data);
  const paramCount = calculateParamCount(data);

  // Add the analyzed data to the new sheet
  newSheet.getRange(1, 3).setValue('Page Type');
  newSheet.getRange(1, 4).setValue('Language Namespace');
  newSheet.getRange(1, 5).setValue('Parameters');

  data.slice(1).forEach((row, index) => {
    const url = row[0];
    const pathParts = getPathname(url).split('/');
    let pageType;
    if (['de', 'nl'].includes(pathParts[1])) {
      pageType = pathParts.length > 3 ? pathParts[2] : "no page type";
    } else {
      pageType = pathParts.length > 2 ? pathParts[1] : "no page type";
    }
    const langNamespace = ['de', 'nl'].includes(pathParts[1]) ? pathParts[1] : "no namespace";
    const hasParam = getQuery(url) ? 'withParam' : 'withoutParam';

    newSheet.getRange(index + 2, 3).setValue(pageType);
    newSheet.getRange(index + 2, 4).setValue(langNamespace);
    newSheet.getRange(index + 2, 5).setValue(hasParam);
  });



  appendDataAndChart('Page Type Analysis', pageTypeCount, data.length);
  appendDataAndChart('Language Namespace Analysis', langCount, data.length);
  appendDataAndChart('Parameters Analysis', paramCount, data.length);
}


function deleteSheets(sheetNames) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet) spreadsheet.deleteSheet(sheet);
  });
}

function appendDataAndChart(sheetName, data, total) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  appendData(sheet, data, total);
  createChart(sheet, sheetName, 2, Object.keys(data).length + 1);
}

function calculatePageTypeCount(data) {
  return data.slice(1).reduce((count, row) => {
    const pathParts = getPathname(row[0]).split('/');
    let element;
    if (['de', 'nl'].includes(pathParts[1])) {
      element = pathParts.length > 3 ? pathParts[2] : "no page type";
    } else {
      element = pathParts.length > 2 ? pathParts[1] : "no page type";
    }
    count[element] = (count[element] || 0) + 1;
    return count;
  }, {});
}


function calculateLangNamespace(data) {
  return data.slice(1).reduce((count, row) => {
    const element = getPathname(row[0]).split('/')[1];
    const langNamespace = ['de', 'nl'].includes(element) ? element : "no namespace";
    count[langNamespace] = (count[langNamespace] || 0) + 1;
    return count;
  }, {});
}

function calculateParamCount(data) {
  return data.slice(1).reduce((count, row) => {
    const hasParam = getQuery(row[0]) ? 'withParam' : 'withoutParam';
    count[hasParam] = (count[hasParam] || 0) + 1;
    return count;
  }, {});
}

function appendData(sheet, count, total) {
  sheet.appendRow(['Type', 'Count', 'Percentage']);
  for (const [key, value] of Object.entries(count)) {
    const percentage = (value / total) * 100;
    sheet.appendRow([key, value, percentage.toFixed(2) + '%']);
  }
}

function createChart(sheet, title, startRow, endRow) {
  const chartBuilder = sheet.newChart();
  chartBuilder
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange(startRow, 1, endRow-startRow+1, 3)) // include all three columns in the chart's data
    .setPosition(2, 5, 0, 0)
    .setOption('title', title)
    .setOption('pieSliceText', 'percentage') // Show percentage in the pie slices
    .setOption('tooltip.text', 'value') // Show count in the tooltip
    .setOption('sliceVisibilityThreshold', 0); // Include all slices, even if their value is
// Include all slices, even if their value is 0
  const chart = chartBuilder.build();
  sheet.insertChart(chart);
}

// Custom function to extract the pathname from a URL
function getPathname(url) {
  var link = url.split('?')[0];
  var pathname = link.replace(/^.*\/\/[^\/]+/, '');
  return pathname;
}

// Custom function to extract the query parameters from a URL
function getQuery(url) {
  var query = url.split('?')[1];
  return query;
}