Is There A Way To Get Some Desired Range Of A Table To Send Email Based On The Value Of A Column?

Part of my daily work is to send a range of cells of google sheet to the suppliers to request material samples to make a backpack. I've written some scripts that are really helpful

Solution 1:

The OP's scenario was, to an extent, unique because it required batching of email to vendors, and compiling an HTML email for items relating to each vendor (based on the value of a checkbox.

This code:

• Takes data from a product group sheet (the sheet name is a variable, so the code can be further automated),
• Creates a temporary list of vendors,
• Loops through the vendors, one at a time
  • Loops through the data and captures any item where the vendor name is a match AND the checkbox is un-ticked (false).
  • the vendor item data is progressively written to an array, and when complete the array is written to a temporary sheet (though perhaps this can be further fine-tuned)
  • An html message is created from the temporary sheet data.
  • The message is sent to the vendor using Gmail.sendEmail.
  • Clears the temporary take info from the temporary output sheet
• Re-sets the unticked checkboxes back to "checked"

---

function so5582181508() {

  //setup spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var datasheetname = "HTS";
  var datasheet = ss.getSheetByName(datasheetname);
  var messagesheetname = "MessageOutput";
  var messagesheet = ss.getSheetByName(messagesheetname);
  var templatesheetname = "Email Template";
  var templatesheet = ss.getSheetByName(templatesheetname);  
  messagesheet.clear();

  // get the number of rows on the data sheet
  var Avals = datasheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log("Alast = "+Alast);
  var htslast = datasheet.getLastRow();
  //Logger.log("htslast = "+htslast);



  // get the supplier column
  var supplierRange = datasheet.getRange(3,7,Alast-2,1);
  //Logger.log("the supplier range  = "+supplierRange.getA1Notation());
  //get the supplier data
  var supplierData = supplierRange.getValues();

  //get the status column
  var statusRange = datasheet.getRange(3,9,Alast-2,1);
  //Logger.log("the status range  = "+statusRange.getA1Notation());
  // get the status data
  var statusData = statusRange.getValues();

  var transCount = supplierData.length;
  var supplierList = [];
  var transData = datasheet.getDataRange().getValues();

  // supplierList contains the unique supplier list
  supplierData.forEach(function(x){
    if(supplierList.indexOf(x[0]) === -1 && x[0]!="" ){
        supplierList.push(x[0]);
    }                   
  });
  var supplierCount = supplierList.length;


  var itemCount = 0;
  var mailMessage = [];
  var mailItem = [];

  //build the mail item header
  var mailItemHeader = [];
  mailItemHeader.push(transData[0][0]);
  mailItemHeader.push(transData[0][1]);
  mailItemHeader.push(transData[0][2]);
  mailItemHeader.push(transData[0][3]);
  mailItemHeader.push(transData[0][4]);
  //mailItemHeader.push(transData[0][6]);

  //Logger.log("length of new array = "+supplierCount);
  //Logger.log("Number of items in table = "+transCount);


  // loop through the data, once for every supplier
  for (supplier = 0; supplier<supplierCount; supplier++){
    mailMessage=[];
    itemCount = 0;
    //Logger.log("supplier = "+supplier);
    //Logger.log("supplier = "+supplierList[supplier]);

    // now loop through the data
    // start i = 2 to allow for header
    for (var i = 2; i < transCount+2; i++) {
      mailItem=[];
      //Logger.log("i = "+i+", SupplierList: "+supplierList[supplier]+", supplier: "+transData[i][6]+", status:"+transData[i][8])

      // the suplier matches and if the checkbox is false
      if (supplierList[supplier] == transData[i][6] && transData[i][8] == false){

        // this this is the first item then push the mail header 
        if (itemCount ==0){
          mailMessage.push(mailItemHeader);
          // get the email address
          var emailAddress = transData[i][5];
          var subject = "Purchase order";

        }

        // this is a match
        var emailAddress = transData[i][5];
        //Logger.log("send email to "+supplierList[supplier]+", at "+transData[i][5]);
        //Logger.log("Item: "+transData[i][0]+", Spec: "+transData[i][1]+", color: "+transData[i][3]+", quantity: "+transData[i][4]+", Unit: "+transData[i][5]);

        // push the transation values for this row onto the mailitem array
        mailItem.push(transData[i][0]);
        mailItem.push(transData[i][1]);
        mailItem.push(transData[i][2]);
        mailItem.push(transData[i][3]);
        mailItem.push(transData[i][4]);
        //mailItem.push(transData[i][6]);

        // push the row onto the rest of the mail message data
        mailMessage.push(mailItem);
        itemCount=itemCount+1

        //update the status value to true
        statusData[i-2] = [true];
      }
      else
      {
      //Logger.log("no match");
      }

    } // end of the transaction loop for this supplier

    // define the temporary output range
    var messageRange = messagesheet.getRange(1, 1, mailMessage.length, 5);
    // paste the items details to the temporary output range
    var messageupdate = messageRange.setValues(mailMessage);
    // get the values for the items only (no header)
    var messagedata = messagesheet.getRange(2, 1, mailMessage.length-1, 5).getValues();
    //Logger.log("ROW#1 col1="+messagedata[0][0]+", column 2: "+messagedata[0][1]);
    //Logger.log("ROW#1 col1="+messagedata[1][0]+", column 2: "+messagedata[1][1]);
    //Logger.log("message data length"+messagedata.length);  
    var messageitemcount = messagedata.length;
    //Logger.log("send email to "+supplierList[supplier]+", at "+emailAddress+", message: "+mailMessage);

    // create a subject
    var emailSubject = "Purchase Order: StackOverflow Test";
    // get the email address
    var emailaddress = emailAddress;

    // message
    var messagePrefix = "Attention: "+supplierList[supplier];

    // start the build of the html message
    var columns = 5;
    var columncount=1;
    var message = 'Please supply the following products:<br><br><table style="border-collapse:collapse;" border = 1 cellpadding = 5>';
    // get the headers
    for (h=0; h<columns;h++){

      if (columncount ==1){
        var header = '<tr>';
      }

      header+='<th style="background-color:#ffeb3b">'+mailItemHeader[h]+'</th>';

      if (columncount ==5){
        header+='</tr>';
      }
      columncount=columncount+1
    }
    //Logger.log("header:"+header);

    // add the header to the mesage
    message+=header;

    // loop through the items on the temporary output and get the item values
    for(c=0;c<messageitemcount;c++){

      // increment message
      message+='<tr><td>'+messagedata[c][0]+'</td>'+'<td>'+messagedata[c][1]+'</td>'+'<td>'+messagedata[c][2]+'</td>'+'<td>'+messagedata[c][3]+'</td>'+'<td>'+messagedata[c][4]+'</td></tr>';

    }

    // finalise the message
    message+='</table>';  
    // Logger.log("DEBUG: message: "+message);//DEBUG

    // send the email
    GmailApp.sendEmail(emailaddress, emailSubject,  messagePrefix, {htmlBody: message,  });

    // clear the state from the temporary outsheet sheet
    messagesheet.clear();

  }
  //update the status range - return all to ticked (true)
  statusRange.setValues(statusData);

}