Is there a way to get some desired range of a tabl

问题:

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. But with this one, I really have no clue to "show some tried code". Please see my description below.

1. I'd love to have a script that runs thru column VENDOR and STATUS to decide what VENDOR to send email to MAIL(i,6) by information from [ ITEM NAME (i, 1): UNIT(i,5) ]

2. I'd love to send email only to rows that have STATUS value = false ( unchecked ), and after sending an email I will let the script change the cell value from false → true (so next time if I run the script again, it won't duplicate the information to the receiver)

3. And the hard part to me is I do not know how to collect information by VENDOR name. So with the google sheet table above, I would love to send 3 emails by order:

   a. [e-mail] ( ONE STAR ) → send row 21+row24+row26 ( only column A to E + then check the cell )

   b. [e-mail] ( YKK ) → send row 22+row25 ( only column A to E + then check the cell )

   c. [e-mail] ( DUCKSAN ) → send row 27 only ( only column A to E + then check the cell ) ( because row 23 is checked - means I've already sent or I do not want to send now )

PHOTOS:

I put pictures in Google Photo here to show you guys:

Table:

a:

b:

c :

---

QUESTION UPDATE With my first information for the question, the Tedinoz's codes belows works great to me.

But what if in "HTS" sheet, the email column is replaced by the material code column, and then I will include all the suppliers information in the "dev" sheet which I use to monitor all the brands everyday or by creating a sheet just for monitor the suppliers ( please advise on this ). Please check the stackoverflow spreadsheet again, I included the "dev" sheet for reference ( scroll down to Row 42 where the suppliers information row starts)

---

回答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);

}