Assigning auto-created Gmail labels to respective threads - google apps script

Question

I'm trying to assign auto-created label to Gmail threads. The script is working, but the problem is every labels were assigned to every email threads instead of just one respective thread.

For example, if there were 7 emails retrieved and 7 labels were created, for every of those emails there will be 7 labels assigned. However, the labels should only be assigned to their respective threads.

Here's the script:

//call cell value by column (Sheet: "SupplierNumber")
//label created based on the "Label" column in Sheet: "SupplierNumber"
//Label should be applied to related threads

function callLabelcolumn() 
{
  var ss = SpreadsheetApp.getActive();
  
  //get sheet: VendorNumber from cell A2 to the rest of column A
  var sh = ss.getSheetByName('SupplierNumber'); 
  var rg=sh.getRange('A2:A');
  var vA=rg.getValues();

  
  //get sheet3: SearchParameter (cell A2 only)
  var sh3 = ss.getSheetByName("SearchParameter")
  var rg3=sh3.getRange('A2');
  var searchPara =rg3.getValues();  

  var threads = GmailApp.search(searchPara); //Get gmail threads - within search parameter only



  vA.forEach(function(row) {
  if(row != "" && row != "#N/A"){
   
   Logger.log('Label: %s', row); //Log script to debug - check get values
    var label = GmailApp.createLabel(row); 
    
   Logger.log("label: " + label); //Log script to debug - check GmailLabel
            
    }

   for (var i = 0; i < threads.length; i++){
    var msg = threads[i].getMessages();
    for (var j = 0; j < msg.length; j++){ 
    var msgid = msg[j].getId();

    var thread = GmailApp.getMessageById(msgid).getThread();
    var name = GmailApp.getUserLabelByName(label.getName());
    console.log("So far so good. Let's add label");
    thread.addLabel(name);

    }
  }

 });
    
}

This is part of Log during the execution

Labelling Process flow:

1. Script refers to SearchParameter Sheet to retrieve specific emails

2. Emails are retrieved into RetrieveEmail Sheet while at the same time SupplierNumber Sheet is also automatically updated based on the list in RetrieveEmail.

[Note: Column A (Label) has the =ArrayFormula() within the google sheet (as seen in the SupplierNumber image) to extract Supplier number from email subject in the RetrieveEmail sheet list - column Label is the referral for .createLabel(row)]

For example: Email subject is "Supplier A (Supplier No.: 12345)", hence output for Label is 12345.

3. Script will refer to Label column in SupplierNumber sheet to create gmail labels. While at the same time, var threads will retrieve emails based on the same search parameter in sheet SearchParameter, which is used to get msg and then msgid. Then each label created should be assigned to each email retrieved. (This is assuming number of emails will equal to number of labels)

4. The desired result should be, each label assigned to its designated email as in this example.

---

I've tried different loops and changing positions of the loops, but I would get either TypeError or similar things happened where every emails got assigned to every labels created.

So is there a way to have the newly created Gmail labels be assigned to their respective thread accordingly?

Answer 1

Your current code

(formatted and simplified with my comments)

vA.forEach(function (row) {

  // for each vendor number create a label and..

  if (row != "" && row != "#N/A") {
    var label = GmailApp.createLabel(row);
  }

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

    // for each thread assign the created label

    var msg = threads[i].getMessages();
    for (var j = 0; j < msg.length; j++) {=
      var msgid = msg[j].getId();

      var thread = GmailApp.getMessageById(msgid).getThread();
      var name = GmailApp.getUserLabelByName(label.getName());
      thread.addLabel(name);
    }
  }
});

You are creating every label, and you are then going through every thread to assign the newly created label to the thread. The end result of this will be that all the threads will have all the labels.

You need to add some kind of conditional statement to this to check if the label is in the subject name (I assume this is the check you need to do).

Modified script

vA.forEach(function (labelName) {
  // for each vendor number create a label and..
  let label;
  if (labelName != "" && labelName != "#N/A") {
    label = GmailApp.createLabel(labelName);
  } else break; // adding a break statement because if there is no label it doens't make sense to continue

  for (var i = 0; i < threads.length; i++) {
    // Get all the messages in the threads
    var msg = threads[i].getMessages();
    for (var j = 0; j < msg.length; j++) {
      // Get the subject of the message
      var msgSubject = msg[j].getSubject()

      // Check if the subject contains the name of the label
      if (msgSubject.match(label)) {
        // Add the label
        threads[i].addLabel(label)
        // Break out of the message for loop since you have already added a label.
        break;
      }
    }
  }
});

Disclaimer

• I can't test this because I don't have your data.
• You will probably end up with duplicate labels, I think you'll need to first check if the label is already created, and create it if its not created. (But that belongs in another question.)
• I would personally approach this in a different way.
  • Make a script that manages the labels that runs separately (maybe it runs every time there is a change to the vendor list).
  • Add a label "to process", to all the mails coming in that match your query with a filter. Then get the script to go through all the threads and check if any of the message subjects contain any of the vendor numbers.
  • Though I do not know the context of your project and this is my own opinion ¯\_(ツ)_/¯

Let me know if any of this is unclear and I will clarify in an edit.

Reference

• Gmail Message Methods Reference