In Gmail inbox, Find sender with most mail threads

gmail-stats.gs

// Original script: https://gist.github.com/leodevbro/2987e8874a18b2086ea6cc1aa3c494e8

// v2.5
// Google Apps Script is a coding language based on JavaScript.
// This Apps Script code helps us to sort addresses by most threads.
// A thread is a group of messages, as a conversation.

const modes = {
  inbox: "inbox", // to analyze threads in the "Inbox" folder
  outbox: "outbox", // to analyze threads in the "Sent" folder
  archive: "archive", // not working
  all_threads: "all_threads", // This is equivalent to the "All Mail" folder. It includes archived threads.
};

const currentMode = modes.inbox; // type 'outbox' if you want to analyze threads in the "Sent" folder.
                                 // type 'all_threads' if you want to analyze "All Mail" folder

const glo = {
  notes: {
    legend: {
      head: {
        range: [1, 1, 1, 1],
        filler: [["Legend"]],
      },
      body: {
        range: [2, 2, 7, 2],
        filler: [
          ["E", "Main numbering"],
          ["F", "Ids of threads"],
          ["G", "Message count for each thread"],
          ["H", "Address of first message of each thread"],
          ["I", "Address sorted by count"],
          ["J", "Thread count for the address"],
          ["K", "Percentage"],
        ],
      }
    },

    stats: {
      head: {
        range: [15, 1, 1, 1],
        filler: [["Stats"]],
      },
      body: {
        range: [16, 2, 3, 1],
        filler: [
          ["Threads count"],
          ["Fetched threads count"],
          ["Unique address count"],
        ],

        rowIndOne: {
          threadsCount: 16,
          fetchedThreadsCount: 17,
          uniqueAddressCount: 18,
        },

        colIndOne_num: 2,
      }
    }
  },

  source: {
    rowIndOne: 1,

    colIndOne: {
      mainNumbering: 5,
      threadId: 6,
      messageCount: 7,
      addressOfFirstMessage: 8,
      addressSortedByThreadCount: 9,
      threadCountOfAddress: 10,
      percent: 11,
    },
  },
};

function getMyEmail () {
  const myMail = Session.getActiveUser().getEmail();
  return myMail;
}


function getJobSheet () {
  const mySS = SpreadsheetApp.getActiveSpreadsheet();
  const mySheet = mySS.getSheetByName(currentMode);

  if (!mySheet) {
    throw new Error(`Sheet not found with the name "${currentMode}"`);
  }
  
  return mySheet;
}

function populateStructure () {
  const mySheet = getJobSheet();
  Logger.log(mySheet.getName());

  const { notes } = glo;

  const jobArray = [notes.legend.head, notes.legend.body, notes.stats.head, notes.stats.body];

  for (const job of jobArray) {
    const theRange = mySheet.getRange(...job.range);
    theRange.setValues(job.filler);
  }
}


function getSpecificThreads (mode, currStartIndexZero, maxN) {
  let arr = [];
  if (mode === modes.inbox) {
    arr = GmailApp.getInboxThreads(currStartIndexZero, maxN);
  } else if (mode === modes.outbox) {
    arr = GmailApp.search("in:sent", currStartIndexZero, maxN);
  } else if (mode === modes.archive) {
    arr = GmailApp.search("in:archive", currStartIndexZero, maxN);
  } else if (mode === modes.all_threads) {
    arr = GmailApp.search("is:read OR is:unread", currStartIndexZero, maxN);
  }
  return arr;
}

function getAllThreadsCountRange (sh) {
  const statsBody = glo.notes.stats.body;
  const cellRange = sh.getRange(statsBody.rowIndOne.threadsCount, statsBody.colIndOne_num + 1, 1, 1);
  return cellRange;
}

function getRangeOfTempStarterIndex (sh) {
  const { body } = glo.notes.stats;
  const cellRange = sh.getRange(body.rowIndOne.fetchedThreadsCount, body.colIndOne_num + 1, 1, 1);
  return cellRange;
}


function simplifyAddress (str) {
  if (str === "") {
    return "NO_ADDRESS";
  }

  const indexesOfStartEnd = [-1, -1];

  for (let i = 0; i < str.length; i += 1) {
    const thisChar = str[i];
    if (thisChar === "<") {
      if (indexesOfStartEnd[0] >= 0) {
        // throw new Error(`detected more than one '<' symbol - - - ${str}`);
        return str;
      } else {
        indexesOfStartEnd[0] = i;
      }
    } else if (thisChar === ">") {
      if (indexesOfStartEnd[1] >= 0) {
        // throw new Error("detected more than one '>' symbol");
        return str;
      } else {
        indexesOfStartEnd[1] = i;
      }
    }
  }

  if (indexesOfStartEnd[0] === -1 && indexesOfStartEnd[1] === -1) {
    return str;
  } else if (indexesOfStartEnd[0] === -1 && indexesOfStartEnd[1] >= 0) {
    // throw new Error(`indexesOfStartEnd[0] === -1 && indexesOfStartEnd[1] >= 0`);
    Logger.log(`Strange value for: indexesOfStartEnd[0] === -1 && indexesOfStartEnd[1] >= 0, value: ${str}`);
    return str;
  } else if (indexesOfStartEnd[0] >= 0 && indexesOfStartEnd[1] === -1) {
    // throw new Error(`indexesOfStartEnd[0] >= 0 && indexesOfStartEnd[1] === -1`);
    Logger.log(`Strange value for: indexesOfStartEnd[0] >= 0 && indexesOfStartEnd[1] === -1, value: ${str}`);
    return str;
  } else if (indexesOfStartEnd[0] >= indexesOfStartEnd[1] - 1) {
    // throw new Error(`indexesOfStartEnd[0] >= indexesOfStartEnd[1] - 1`);
    Logger.log(`Strange value for: indexesOfStartEnd[0] >= indexesOfStartEnd[1] - 1, value: ${str}`);
    return str;
  }

  const simpleStr = str.slice(indexesOfStartEnd[0] + 1, indexesOfStartEnd[1]);
  return simpleStr;
}


function optimized_n1_getIdsOfAllThreads() {
  const STEP = 7000;

  const mySheet = getJobSheet();

  const sheetName = mySheet.getName();

  // --------

  const rangeOfCurrentOptimizedCount = getAllThreadsCountRange(mySheet);
  const currentOptimizedCount = rangeOfCurrentOptimizedCount.getValues()[0][0] || 0;

  if (currentOptimizedCount === 0) {
    populateStructure(); //
  }


  const maxN = 500;
  let currStartIndex = currentOptimizedCount;

  const myThreads = [];

  // console.log("aaaaaa");
  let firstArr = getSpecificThreads(sheetName, currStartIndex, maxN);

  let faLen = firstArr.length;

  if (faLen === 0) {
    Logger.log(`No more threads to fetch, total number is ${currentOptimizedCount}`);
    return;
  }


  myThreads.push(...firstArr);

  // console.log("bbbbbbb");
  while (faLen > 0 && myThreads.length < STEP) {
    currStartIndex += maxN;
    let newArr = getSpecificThreads(sheetName, currStartIndex, maxN);
    
    faLen = newArr.length;
    myThreads.push(...newArr);
  }


  // console.log("bbbbbbb");

  const myThreadsLeee = myThreads.length;


  Logger.log("myThreadsCount:");
  Logger.log(myThreadsLeee);

  const allIds = myThreads.map((x) => x.getId());

  const { source } = glo;
  const myRangeOfIds = mySheet.getRange(currentOptimizedCount + 1, source.colIndOne.threadId, myThreadsLeee, 1);

  myRangeOfIds.setValues(allIds.map((id) => ["id_" + id]));


  const myRangeOfNumbering = mySheet.getRange(currentOptimizedCount + 1, source.colIndOne.mainNumbering, myThreadsLeee, 1);
  const allNumbering = [];
  for (let i = currentOptimizedCount + 1; i <= currentOptimizedCount + myThreadsLeee; i += 1) {
    allNumbering.push(i);
  }

  myRangeOfNumbering.setValues(allNumbering.map((num) => [num]));


  const counterCellRange = getAllThreadsCountRange(mySheet);
  counterCellRange.setValues([[myThreadsLeee + currentOptimizedCount]]);

  if (currentOptimizedCount === 0) {
    const fetchedCountCell = getRangeOfTempStarterIndex(mySheet);
    fetchedCountCell.setValues([[0]]);
  }

  Logger.log(`previous count was ${currentOptimizedCount}, added ${myThreadsLeee}, new count is ${myThreadsLeee + currentOptimizedCount}`);

}


function n1_getIdsOfAllThreads() {
  populateStructure();

  const mySheet = getJobSheet();

  const sheetName = mySheet.getName();

  const maxN = 500;
  let currStartIndex = 0;

  const myThreads = [];

  // console.log("aaaaaa");
  let firstArr = getSpecificThreads(sheetName, currStartIndex, maxN);

  let faLen = firstArr.length;


  myThreads.push(...firstArr);

  // console.log("bbbbbbb");
  while (faLen > 0) {
    currStartIndex += maxN;
    let newArr = getSpecificThreads(sheetName, currStartIndex, maxN);
    
    faLen = newArr.length;
    myThreads.push(...newArr);
  }


  // console.log("bbbbbbb");

  const myThreadsLeee = myThreads.length;


  Logger.log("myThreadsCount:");
  Logger.log(myThreadsLeee);

  const allIds = myThreads.map((x) => x.getId());

  const { source } = glo;
  const myRangeOfIds = mySheet.getRange(source.rowIndOne, source.colIndOne.threadId, myThreadsLeee, 1);

  myRangeOfIds.setValues(allIds.map((id) => ["id_" + id]));


  const myRangeOfNumbering = mySheet.getRange(source.rowIndOne, source.colIndOne.mainNumbering, myThreadsLeee, 1);
  const allNumbering = [];
  for (let i = 1; i <= myThreadsLeee; i += 1) {
    allNumbering.push(i);
  }

  myRangeOfNumbering.setValues(allNumbering.map((num) => [num]));


  

  const counterCellRange = getAllThreadsCountRange(mySheet);
  counterCellRange.setValues([[myThreadsLeee]]);

  const fetchedCountCell = getRangeOfTempStarterIndex(mySheet);
  fetchedCountCell.setValues([[0]]);
}

function checkThatEachIdIsUnique () {
  const mySheet = getJobSheet();

  const rangeOfIdsCount = getAllThreadsCountRange(mySheet);
  const idsCount = rangeOfIdsCount.getValues()[0][0] || 0;

  const { source } = glo;
  const myRangeOfIds = mySheet.getRange(source.rowIndOne, source.colIndOne.threadId, idsCount, 1);
  const myIds = myRangeOfIds.getValues().map((x) => x[0]);

  Logger.log(`Full count: ${idsCount}`);

  const zMap = new Map();

  let nonUniqueCount = 0;

  for (const id of myIds) {
    if (typeof id !== "string" || id === "") {
      throw new Error(`id must be string, current id is ${typeof id}`);
    }

    const inZMap = zMap.get(id);
    if (typeof inZMap !== "number") {
      zMap.set(id, 1);
    } else {
      nonUniqueCount += 1;
      // Logger.log(`found duplicate: ${id}`);
      zMap.set(id, inZMap + 1);
    }
  }

  if (nonUniqueCount === 0) {
    Logger.log("All ids are unique.");
  } else {
    const uArr = [];

    for (const [k, v] of [...zMap]) {
      if (v !== 1) {
        uArr.push([k, v]);
      }
    }

    Logger.log(`${uArr.length} Ids are not unique:`);

    Logger.log(JSON.stringify(uArr, null, 2));
  }
}


// =======================================


function optimized_n2_fetchAddresses() {
  const myAddress = getMyEmail();
  const mySheet = getJobSheet();

  const STEP = 100000;
  const counterCellRange = getAllThreadsCountRange(mySheet);
  const theAllCount = Number(counterCellRange.getValues()[0][0]);
  Logger.log(theAllCount);

  

  const rangeOfLastIndexOne = getRangeOfTempStarterIndex(mySheet);

  const getStarterIndexOne = () => Number(rangeOfLastIndexOne.getValues()[0][0]) + 1;

  let starterIndexOne = getStarterIndexOne();

  const firstStarterIndOne = starterIndexOne;

  if (starterIndexOne > theAllCount) {
    Logger.log("Already fetched all the threads");
    return;
  }

  const darchenili = theAllCount - (starterIndexOne - 1);

  const smartSTEP = Math.min(darchenili, STEP);

  const { source } = glo;

  const rangeOfIdsOfThreads = mySheet.getRange(starterIndexOne, source.colIndOne.threadId, smartSTEP, 1);
  const rValues = rangeOfIdsOfThreads.getValues();
  const theIds = rangeOfIdsOfThreads.getValues().map((x) => x[0].slice(3));


  let addresses_allFirstMessages = [];
  let messageCountForEachThread = [];

  const safeStep = 100;

  const alreadyDone = starterIndexOne > theAllCount;

  if (!alreadyDone) {
    Logger.log(`done ${0}, done globally: ${starterIndexOne - 1}, now started from: ${starterIndexOne}`);
  } else {
    Logger.log(`done ${0}, done globally: ${starterIndexOne - 1}, All done.`);
  }

  

  for (let i = 0; i < theIds.length; i += 1) {
    const id = theIds[i];
    
    const theThread = GmailApp.getThreadById(id);

    if (!theThread) {
      throw new Error(`No thread found for the id: "${id}"`);
    } else {
      const messages = theThread.getMessages();
      messageCountForEachThread.push(messages.length);

      const firstMessage = messages[0];
      let wantedAddress = simplifyAddress("");

      if (currentMode === modes.inbox) {
        wantedAddress = simplifyAddress(firstMessage.getFrom());
      } else if (currentMode === modes.outbox) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        if (addressFrom === myAddress) {
          const addressTo = simplifyAddress(firstMessage.getTo());
          wantedAddress = addressTo;
        } else {
          wantedAddress = addressFrom;
        }
      } else if (currentMode === modes.archive) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        const addressTo = simplifyAddress(firstMessage.getTo());
        wantedAddress = `from_${addressFrom}_to_${addressTo}`;
      } else if (currentMode === modes.all_threads) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        const addressTo = simplifyAddress(firstMessage.getTo());
        wantedAddress = `from_${addressFrom}_to_${addressTo}`;
      } else {
        throw new Error("no mode found");
      }

      addresses_allFirstMessages.push(wantedAddress);
    }

    const isLast = (i + firstStarterIndOne) === theAllCount;

    if ((i + 1) % safeStep === 0 || isLast) {
      if (addresses_allFirstMessages.length !== safeStep && !isLast) {
        Logger.log(`${addresses_allFirstMessages.length}`);
        throw new Error("Error at: addresses_allFirstMessages.length !== safeStep");
      }

      const logicalStep = Math.min(safeStep, theAllCount - starterIndexOne + 1);

      const coolRangeForAddresses = mySheet.getRange(starterIndexOne, glo.source.colIndOne.addressOfFirstMessage, logicalStep, 1);
      coolRangeForAddresses.setValues(addresses_allFirstMessages.map((x) => [x]));

      const coolRangeForMessageCount = mySheet.getRange(starterIndexOne, glo.source.colIndOne.messageCount, logicalStep, 1);
      coolRangeForMessageCount.setValues(messageCountForEachThread.map((x) => [x]));

      starterIndexOne = starterIndexOne + logicalStep;

      rangeOfLastIndexOne.setValues([[starterIndexOne - 1]]);

      addresses_allFirstMessages = [];
      messageCountForEachThread = [];


      if (!isLast) {
        Logger.log(`done: ${i + 1}, done globally: ${starterIndexOne - 1}, now started from: ${starterIndexOne}`);
      } else {
        Logger.log(`done: ${i + 1}, done globally: ${starterIndexOne - 1}, All done.`);
      }
      
    }
  }
}




function n2_fetchAddresses() {
  const myAddress = getMyEmail();
  const mySheet = getJobSheet();

  const STEP = 1000;
  const counterCellRange = getAllThreadsCountRange(mySheet);
  const theAllCount = Number(counterCellRange.getValues()[0][0]);
  Logger.log(theAllCount);

  

  const rangeOfStarterIndexOne = getRangeOfTempStarterIndex(mySheet);

  const starterIndexOne = Number(rangeOfStarterIndexOne.getValues()[0][0]) + 1;

  if (starterIndexOne > theAllCount) {
    Logger.log("Already fetched all the threads");
    return;
  }

  const darchenili = theAllCount - (starterIndexOne - 1);

  const smartSTEP = Math.min(darchenili, STEP);

  const {source} = glo;

  const rangeOfIdsOfThreads = mySheet.getRange(starterIndexOne, source.colIndOne.threadId, smartSTEP, 1);
  const rValues = rangeOfIdsOfThreads.getValues();
  const theIds = rangeOfIdsOfThreads.getValues().map((x) => x[0].slice(3));


  const addresses_allFirstMessages = [];
  const messageCountForEachThread = [];

  for (let i = 0; i < theIds.length; i += 1) {
    const id = theIds[i];
    if (i % 100 === 0) {
      Logger.log(`iiiii: ${i}`);
    }
    const theThread = GmailApp.getThreadById(id);

    if (!theThread) {
      throw new Error(`No thread found for the id: "${id}"`);
    } else {
      const messages = theThread.getMessages();
      messageCountForEachThread.push(messages.length);

      const firstMessage = messages[0];
      let wantedAddress = "";

      if (currentMode === modes.inbox) {
        wantedAddress = simplifyAddress(firstMessage.getFrom());
      } else if (currentMode === modes.outbox) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        if (addressFrom === myAddress) {
          const addressTo = simplifyAddress(firstMessage.getTo());
          wantedAddress = addressTo;
        } else {
          wantedAddress = addressFrom;
        }
      } else if (currentMode === modes.archive) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        const addressTo = simplifyAddress(firstMessage.getTo());
        wantedAddress = `from_${addressFrom}_to_${addressTo}`;
      } else if (currentMode === modes.all_threads) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        const addressTo = simplifyAddress(firstMessage.getTo());
        wantedAddress = `from_${addressFrom}_to_${addressTo}`;
      } else {
        throw new Error("no mode found");
      }

      addresses_allFirstMessages.push(wantedAddress);
    }
  }

  
  // console.log(`addresses_allFirstMessages.length: ${addresses_allFirstMessages.length} - ${starterIndexOne}`);

  const coolRange = mySheet.getRange(starterIndexOne, glo.source.colIndOne.addressOfFirstMessage, smartSTEP, 1);
  coolRange.setValues(addresses_allFirstMessages.map((x) => [x]));

  const coolRange2 = mySheet.getRange(starterIndexOne, glo.source.colIndOne.messageCount, smartSTEP, 1);
  coolRange2.setValues(messageCountForEachThread.map((x) => [x]));

  const newStarterIndexOne = (starterIndexOne + smartSTEP) - 1;
  console.log(`new st: ${newStarterIndexOne}`);
  rangeOfStarterIndexOne.setValues([[newStarterIndexOne]]);
}




function n3_calcUniqueAddressesSortedByCount () {
  const mySheet = getJobSheet();


  const counterCellRange = getAllThreadsCountRange(mySheet);
  const theAllCount = Number(counterCellRange.getValues()[0][0]);

  const fetchedThreadsCountCell = getRangeOfTempStarterIndex(mySheet);
  const fetchedThreadsCount = Number(fetchedThreadsCountCell.getValues()[0][0]);

  if (fetchedThreadsCount !== theAllCount) {
    throw new Error("Not all threads are fetched");
  }

  const { source } = glo;

  const addressesRange = mySheet.getRange(source.rowIndOne, source.colIndOne.addressOfFirstMessage, fetchedThreadsCount, 1);

  const addressesArr = addressesRange.getValues().map((x) => x[0]);
  // console.log(addressesArr.slice(0, 5));

  const superMap = new Map();

  for (const address of addressesArr) {
    const inlowCase = address.toLowerCase();
    const currVal = superMap.get(inlowCase);

    if (typeof currVal === "number") {
      superMap.set(inlowCase, currVal + 1);
    } else {
      superMap.set(inlowCase, 1);
    }
  }

  const sorted = [...superMap].sort((a, b) => b[1] - a[1]);

  const lenOfSorted = sorted.length;


  console.log(lenOfSorted);
  // console.log(sorted);

  const { body } = glo.notes.stats;

  const rangeCellOfCountOfUniAddresses = mySheet.getRange(body.rowIndOne.uniqueAddressCount, body.colIndOne_num + 1, 1, 1);
  rangeCellOfCountOfUniAddresses.setValues([[lenOfSorted]]);

  const rangeOfCoolAddresses = mySheet.getRange(source.rowIndOne, source.colIndOne.addressSortedByThreadCount, lenOfSorted, 3);


  // const withPercent = sorted.map((x) => [...x, x[1] * 100 / fetchedThreadsCount]);

  const withPercent = sorted.map((x) => {
    const percent = x[1] * 100 / fetchedThreadsCount;
    return([...x, percent]);
  });

  rangeOfCoolAddresses.setValues(withPercent);

  const lastRowIndexOne = mySheet.getLastRow();
  const incr = lastRowIndexOne - (source.rowIndOne - 1);

  const percentRange = mySheet.getRange(source.rowIndOne, source.colIndOne.percent, incr, 1);
  percentRange.setNumberFormat("#,##0.00");
}




//