Extract Email Threads from Gmail Labels to Google Sheet-CodePudding

as continued from my earlier post Extracting Emails from Gmail Label to Google Sheet

Im trying to extract emails from a Single Gmail Label to Google sheet, using Google App Script, the data in Google sheet to have timestamp, from email, to email, subject, and email body, the script below credits @Cooper

The below script works and i could extract 527 emails from the Gmail label, but i am unable to to extract all emails from this label as i have approximately 5000 emails in it, can Google Appscript do this in a single run?

function ExtractingEmails(query="Label:Test") {
  let o = [["TimeStamp", "From", "To", "Subject", "Body"]];
  const ts = GmailApp.search(query);
  ts.forEach(t => {
    let ms = t.getMessages();
    ms.forEach(m => {
      o.push([m.getDate(), m.getFrom(), m.getTo(), m.getSubject(), m.getPlainBody()])
    })
  })
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  if (o && o.length > 1) {
    sh.clearContents();
    sh.getRange(1, 1, o.length, o[0].length).setValues(o);
    ss.toast(`${o.length - 1} messages found for Query: ${query}`)
  } else {
    ss.toast(`No messages found for Query: ${query}`)
  }
}

CodePudding user response：

As stated in the documentation for GmailApp.search it does not work well when there are lots of threads to retrieve.

So you need to iterate over them until there are no more with the variant of search.

Adapting your function, that would do

function ExtractingEmails(query="Label:Test") {
  let o = [["TimeStamp", "From", "To", "Subject", "Body"]];
  const PAGE_SIZE = 500;
  let index = 0;
  console.log(`Getting threads from ${index} to ${index   PAGE_SIZE}`)
  let ts = GmailApp.search(query, index, PAGE_SIZE);
  while(ts.length > 0) {
    console.log(`Retrieved ${ts.length} threads`)
    ts.forEach(t => {
      let ms = t.getMessages();
      ms.forEach(m => {
        o.push([m.getDate(), m.getFrom(), m.getTo(), m.getSubject(), m.getPlainBody()])
      })
    })
    index  = PAGE_SIZE
    console.log(`Getting threads from ${index} to ${index   PAGE_SIZE}`)
    ts = GmailApp.search(query, index, PAGE_SIZE);
  }
  
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  if (o && o.length > 1) {
    sh.clearContents();
    sh.getRange(1, 1, o.length, o[0].length).setValues(o);
    console.log(`${o.length - 1} messages found for Query: ${query}`)
  } else {
    console.log(`No messages found for Query: ${query}`)
  }
}