ProcessPhonebook_AppScript

function processPhonebook() {
  var doc = DocumentApp.openById('1jM4fwIg3u8bptOk0CVgOo0d8fr2TI978LFsQQCM3epg');
  var text = doc.getBody().getText();
  var lines = text.split('\n');
  
  Logger.log('Document Loaded. # of lines is: ' + lines.length);

  var sheet = SpreadsheetApp.openById('1sQLj73TD24iHIQFUQNup6OqL5h7VRDwWg9mXG1o-57k');
  var formattedSheet = sheet.getSheetByName('Formatted Entries');
  var unformattedSheet = sheet.getSheetByName('Unformatted Entries');
  var statisticsSheet = sheet.getSheetByName('Statistics');
  var errorsSheet = sheet.getSheetByName('Errors') || sheet.insertSheet('Errors');
  
  // Clear sheets and set headers without overwriting headers in Row 1
  clearAndSetHeaders(formattedSheet, ['Name', 'Company', 'Phone Number', 'Email']);
  clearAndSetHeaders(unformattedSheet, ['Unformatted Data']);
  clearAndSetHeaders(statisticsSheet, ['Statistic', 'Value']);
  clearAndSetHeaders(errorsSheet, ['Name', 'Company', 'Phone Number', 'Email']);

  var formattedData = [];
  var unformattedData = [];
  var errorsData = [];
  var companyCount = {};
  var entryPattern = /^(.+)\s+w\/(.+)\s+([0-9\-\+\(\) ]+)\s+(.+)$/;
  
  lines.slice(1).forEach(function(line) {
    var patternToUse = entryPattern.test(line) ? entryPattern : entryPattern; // Adjusted for clarity
    if (patternToUse.test(line)) {
      var matches = line.match(patternToUse);
      var name = matches[1].trim();
      var company = matches[2].trim();
      var phone = matches[3].trim();
      var email = matches[4].trim();

      var plusIndex = company.indexOf('+');
      if (plusIndex !== -1) {
        phone = company.substring(plusIndex) + " " + phone;
        company = company.substring(0, plusIndex).trim();
      }

      if (!name || !company || !phone || !email) {
        errorsData.push([name, company, phone, email]);
      } else {
        formattedData.push([name, company, phone, email]);
        if (companyCount[company]) {
          companyCount[company]++;
        } else {
          companyCount[company] = 1;
        }
      }
    } else {
      unformattedData.push([line]);
    }
  });

  // Populate sheets with data
  populateSheetWithData(formattedSheet, formattedData, 2);
  populateSheetWithData(unformattedSheet, unformattedData, 2);
  populateSheetWithData(errorsSheet, errorsData, 2);

  // Add Statistics
  addStatistics(statisticsSheet, lines, formattedData, unformattedData, companyCount);

  // Sort the formattedSheet by Name (Column A) after populating it
  formattedSheet.getRange(2, 1, formattedSheet.getLastRow() - 1, formattedSheet.getLastColumn()).sort(1);

  Logger.log('Script Done.');
}

function clearAndSetHeaders(sheet, headers) {
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.deleteRows(2, lastRow - 1);
  }
  var currentHeaders = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
  var isSameHeader = currentHeaders.every((value, index) => value === headers[index]);
  if (!isSameHeader || lastRow === 1) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }
}

function populateSheetWithData(sheet, data, startRow) {
  if (data.length > 0) {
    sheet.getRange(startRow, 1, data.length, data[0].length).setValues(data);
  }
}

function addStatistics(statisticsSheet, lines, formattedData, unformattedData, companyCount) {
  var errorsData = []; // Ensure errorsData is defined for statistics calculation
  var stats = [
    ['Total Entries', lines.length - 1],
    ['Formatted Entries', formattedData.length],
    ['Unformatted Entries', unformattedData.length],
    ['Entries with Errors', errorsData.length]
  ];
  statisticsSheet.getRange(2, 1, stats.length, 2).setValues(stats);

  var companyStats = Object.keys(companyCount).map(function(key) {
    return [key, companyCount[key]];
  }).sort(function(a, b) {
    return b[1] - a[1];
  });

  var top10CompanyStats = companyStats.slice(0, 10);
  var startRowForTop10 = stats.length + 4;
  statisticsSheet.getRange(startRowForTop10, 1, top10CompanyStats.length, 2).setValues(top10CompanyStats.map(function(item, index) {
    return [`#${index + 1} ${item[0]}`, item[1]];
  }));
}