CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmail
Allister Lobo
Allister Lobo

Reputation: 33

Exporting Gmail Labels to Google Sheets

I have an apps script to pull data from my gmail inbox to a google sheet to analyze the emails based on which label. I created an array to pull From, Date, Subject, and the Body of the email but I want to add the correlating label(s) to each row to the first column as shown in line 24.

How can I add the labels to the first column?

var ui = SpreadsheetApp.getUi();
function onOpen(e){

ui.createMenu("Gmail Manager").addItem("Get Emails by Label", "getGmailEmails").addToUi();

}

function getGmailEmails() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var label = sheet.getRange(1,3).getValue();
 var after = sheet.getRange(2,3).getDisplayValue();
 var before = sheet.getRange(3,3).getDisplayValue();
 var threads = GmailApp.search ("label:"+label + " AND " + "after:"+after + " AND " + "before:"+before);

//console.log("label:"+label + " AND " + "after:"+after + " AND " + "before:"+before);

 for (var i = 0; i<threads.length; i++){

  let messages = threads[i].getMessages();

  for (var j = 0; j<messages.length; j++){

  //sheet.getRange(i+6,1).setValue(messages[j].getLabels());
  sheet.getRange(i+6,2).setValue(messages[j].getFrom());
  sheet.getRange(i+6,3).setValue(messages[j].getFrom().replace(/^,+<([^>]+)>$/, "$1"));
  sheet.getRange(i+6,4).setValue(messages[j].getDate());
  sheet.getRange(i+6,5).setValue(messages[j].getSubject());
  sheet.getRange(i+6,6).setValue(messages[j].getPlainBody());
    }
   }
  }

Upvotes: 1

Views: 656

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I thought that the modification points of your script is as follows.

  • Method of getLabels() is for Class GmailThread.
  • When setValue is used in a loop, the process cost will be high.

When the above modification points are reflected to your script, it becomes as follows.

Modified script:

function getGmailEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var label = sheet.getRange(1, 3).getValue();
  var after = sheet.getRange(2, 3).getDisplayValue();
  var before = sheet.getRange(3, 3).getDisplayValue();
  var threads = GmailApp.search("label:" + label + " AND " + "after:" + after + " AND " + "before:" + before);

  // I modified below script.
  var values = [];
  for (var i = 0; i < threads.length; i++) {
    var temp = [];
    var label = threads[i].getLabels().map(e => e.getName()).join(",");
    let messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      temp.push([
        label,
        messages[j].getFrom(),
        messages[j].getFrom().replace(/^,+<([^>]+)>$/, "$1"),
        messages[j].getDate(),
        messages[j].getSubject(),
        messages[j].getPlainBody()
      ]);
    }
    values = values.concat(temp);
  }
  sheet.getRange(6, 1, values.length, values[0].length).setValues(values);
}

Note:

  • In this modification, when the multiple labels are used, those are used as a text by separating with ,. About this, please modify it for your actual situation.

References:

Upvotes: 2

Related Questions