Extract Gmail Content

If you find this free website useful – why don’t you support this with a donation? It is easy…. read more ….

function onOpen() {
  const spreadsheet = SpreadsheetApp.getActive();
  let menuItems = [
    { name: 'Gather emails', functionName: 'gather' },
  ];
  spreadsheet.addMenu('Extract Gmail', menuItems);
}
function gather() {
  getGmail();
}
function getGmail() {
  let ss = SpreadsheetApp.getActive();
  const query = "is:sent subject:(VFXRIO Live Ticket Number)";


  var max = 100;
  var offset = 0;
  var threads = [];

  while (true) {
    var searchThreads = GmailApp.search(query, offset, max);
    threads = threads.concat(searchThreads);
    if (threads.length < max) {
      break;
    }

    offset += max;
  }

  let label = GmailApp.getUserLabelByName("done");
  if (!label) { label = GmailApp.createLabel("done") }
  let ticket;

  for (i = 0; i < threads.length; i++) {
    ticketline = threads[i].getMessages()[0].getPlainBody().match(/Your Ticket number is: (.*)/gm);
    ticket = ticketline[0].split(": ");
    emailline = threads[i].getMessages()[0].getTo();
    emailsplit = emailline.split(' <');
    name = emailsplit[0];
    email = emailsplit[1].replace(">", "");
    ss.getRange('A' + (i + 1)).setValue(threads[i].getMessages()[0].getDate());
    ss.getRange('B' + (i + 1)).setValue(name);
    ss.getRange('C' + (i + 1)).setValue(email);
    ss.getRange('D' + (i + 1)).setValue(ticket[1]);
  }
}


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *