Exporting Gmail Labels to Google Sheets-CodePudding

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());
    }
   }
  }

CodePudding user response：

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.

Modified script:

Note:

References: