Original gmail stats code

gmail_threads_stats_original_code.gs

// 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
};

const currentMode = modes.inbox; // type 'outbox' if you want to analyze threads in the "sent" 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, currStartIndex, maxN) {
  let arr = [];
  if (mode === modes.inbox) {
    arr = GmailApp.getInboxThreads(currStartIndex, maxN);
  } else if (mode === modes.outbox) {
    arr = GmailApp.search("in:sent", currStartIndex, 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 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(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 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`);
  } else if (indexesOfStartEnd[0] >= 0 && indexesOfStartEnd[1] === -1) {
    throw new Error(`indexesOfStartEnd[0] >= 0 && indexesOfStartEnd[1] === -1`);
  } else if (indexesOfStartEnd[0] >= indexesOfStartEnd[1] - 1) {
    throw new Error(`indexesOfStartEnd[0] >= indexesOfStartEnd[1] - 1`);
  }

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




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 = firstMessage.getFrom();
      } else if (currentMode === modes.outbox) {
        const addressFrom = simplifyAddress(firstMessage.getFrom());
        if (addressFrom === myAddress) {
          const addressTo = firstMessage.getTo();
          wantedAddress = addressTo;
        } else {
          wantedAddress = addressFrom;
        }
      } else {
        throw new Error("no mode found");
      }

      addresses_allFirstMessages.push(simplifyAddress(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");
}




//