I am trying to make a script that takes an email within a certain label and puts it into Google Sheets. I'm using this function at the moment:
function getMessagesWithLabel() {
var destArray = new Array();
var threads = GmailApp.getUserLabelByName('Facebook').getThreads(0,10);
for(var n in threads){
var msg = threads[n].getMessages();
var destArrayRow = new Array();
destArrayRow.push('thread has '+threads[n].getMessageCount()+' messages');
for(var m in msg){
destArrayRow.push(msg[m].getSubject());
}
destArray.push(destArrayRow);
}
Logger.log(destArray);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
if(ss.getLastRow()==0){sh.getRange(1,1).setValue('getMessagesWithLabel() RESULTS')};
sh.getRange(ss.getLastRow()+1,1,destArray.length,destArray[0].length).setValues(destArray)
}
This works at grabbing all the emails in the 'Facebook' label but grabs all the emails every time, creating duplicates every time it runs. I would like for it to just create a new line every time a new one is put into the 'Facebook' label.
Four thoughts for you to explore.
GmailMessage.getDate()
to filter out messages you've already handled. Store the time that the script runs inScriptProperties
and reload it as your threshold next time.ScriptProperties
.Use
search()
to match label and time, again keeping track of when the script has run inScriptProperties
.That will give threads with any messages added after the given date, but the threads will also have messages prior to that date. You'll probably still have to use one of the earlier options to filter those out. (Unless, as you've indicated in comments, your thread will have no replies and thus only one message.)
Once a thread is processed, remove the label you used to categorize it.