Define recipient depending on cell content

Question

I made a script that forwards emails to different people when clicking on a card button. This is the main part of the code:

function forwardtest() {
var threadsa = GmailApp.search('label: Inbox label: product-a');
for (var i = 0; i < threadsa.length; i++) {
var recipient = '[email protected]';
var messages = threadsa[i].getMessages();
for (var j = 0; j < messages.length; j++) {
  var body = messages[j].getBody();
  messages[j].forward(recipient,{htmlBody: body});
}
GmailApp.moveThreadToArchive(threadsa[i]);
}
var threadsb = GmailApp.search('label: Inbox label: product-b');
for (var i = 0; i < threadsb.length; i++) {
var recipient = '[email protected]';
var messages = threadsb[i].getMessages();
for (var j = 0; j < messages.length; j++) {
  var body = messages[j].getBody();
  messages[j].forward(recipient,{htmlBody: body});
}
GmailApp.moveThreadToArchive(threadsb[i]);
}
}

Sometimes, we'll need to change the forwarding addresses. Since I don't want people to change it directly in the code, I made a Google Sheets in which the forwarding addresses are listed depending on the email labels:

Product label	Forward address
product-a	[email protected]
product-b	[email protected]

Is there a way to replace the var recipient = '[email protected]'; with "Look for the cell containing "product-a" content and define recipient as the content in the cell which is next to it"?

Thank you very much in advance!

Answer 1

Solution:

Since you will be referencing a Google Sheet to fetch the data, you would need to use Sheets API starting from the SpreadsheetApp class. Also, since this script is not attached to the sheet from your use case, you would need to open the sheet by it's ID, which is in the URL link:

https://docs.google.com/spreadsheets/d/<SHEET-ID>/edit#gid=0

Take note of the SHEET-ID since we will use it in the code.

For the code itself, I made a separate functions for finding the recipient and sending the emails for modularity:

EDIT: Rearranged the code, moved the function into a for loop instead.

function forwardtest() {
  var sheet = SpreadsheetApp.openById("SHEET-ID").getSheetByName("Sheet1");
  var prodList = sheet.getRange(2,1,sheet.getLastRow()-1,2).getValues();

  for (var k = 0; k < prodList.length; k++) {
    var prod = prodList[k][0];
    var recipient = prodList[k][1];
    var threads = GmailApp.search('label: Inbox label: '+prod);
    for (var i = 0; i < threads.length; i++) {
      var messages = threads[i].getMessages();
      for (var j = 0; j < messages.length; j++) {
        var body = messages[j].getBody();
        messages[j].forward(recipient,{htmlBody: body});
      }
    GmailApp.moveThreadToArchive(threads[i]);
    }
  }
}

Note: Replace the SHEET-ID with the sheet ID from the URL.

Sample Output:

Using a sample table with a variable number of products:

References:

Class SpreadsheetApp

Class Sheet

Class Range