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 spreadsheetvarss= SpreadsheetApp.getActiveSpreadsheet();
  vardatasheetname="HTS";
  vardatasheet= ss.getSheetByName(datasheetname);
  varmessagesheetname="MessageOutput";
  varmessagesheet= ss.getSheetByName(messagesheetname);
  vartemplatesheetname="Email Template";
  vartemplatesheet= ss.getSheetByName(templatesheetname);  
  messagesheet.clear();

  // get the number of rows on the data sheetvarAvals= datasheet.getRange("A1:A").getValues();
  varAlast= Avals.filter(String).length;
  //Logger.log("Alast = "+Alast);varhtslast= datasheet.getLastRow();
  //Logger.log("htslast = "+htslast);// get the supplier columnvarsupplierRange= datasheet.getRange(3,7,Alast-2,1);
  //Logger.log("the supplier range  = "+supplierRange.getA1Notation());//get the supplier datavarsupplierData= supplierRange.getValues();

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

  vartransCount= supplierData.length;
  varsupplierList= [];
  vartransData= 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]);
    }                   
  });
  varsupplierCount= supplierList.length;


  varitemCount=0;
  varmailMessage= [];
  varmailItem= [];

  //build the mail item headervarmailItemHeader= [];
  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 supplierfor (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 headerfor (vari=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 falseif (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 addressvaremailAddress= transData[i][5];
          varsubject="Purchase order";

        }

        // this is a matchvaremailAddress= 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 rangevarmessageRange= messagesheet.getRange(1, 1, mailMessage.length, 5);
    // paste the items details to the temporary output rangevarmessageupdate= messageRange.setValues(mailMessage);
    // get the values for the items only (no header)varmessagedata= 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);  varmessageitemcount= messagedata.length;
    //Logger.log("send email to "+supplierList[supplier]+", at "+emailAddress+", message: "+mailMessage);// create a subjectvaremailSubject="Purchase Order: StackOverflow Test";
    // get the email addressvaremailaddress= emailAddress;

    // messagevarmessagePrefix="Attention: "+supplierList[supplier];

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

      if (columncount ==1){
        varheader='<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 valuesfor(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);

}