Google App Script Only Pull Rows With Data

Question

I have a google sheet that is populated with changing data daily based off of today's date and what I would like to do is push every line that has data into a Slack channel daily on a time trigger. As you can see I started building this out making each cell its own variant, but when Row 3 doesn't have data it will still push a blank update into Slack like this:

I'm guessing that I need to add an IF somewhere in here, but not sure where. Here is the code:

 function createMeetingMessage(sheet) {
 var porow1 = "Bulk!A2";
 var OrderDaterow1 = "Bulk!K2";
 var OrderReqrow1 = "Bulk!J2";
 var Desrow1 = "Bulk!D2";
 var Vendorrow1 = "Bulk!E2";
 var ETArow1 = "Bulk!L2";
  var porow2 = "Bulk!A3";
 var OrderDaterow2 = "Bulk!K3";
 var OrderReqrow2 = "Bulk!J3";
 var Desrow2 = "Bulk!D3";
 var Vendorrow2 = "Bulk!E3";
 var ETArow2 = "Bulk!L3";
   var porow3 = "Bulk!A4";
 var OrderDaterow3 = "Bulk!K4";
 var OrderReqrow3 = "Bulk!J4";
 var Desrow3 = "Bulk!D4";
 var Vendorrow3 = "Bulk!E4";
 var ETArow3 = "Bulk!L4";

var message = "> *Today's Purchases*" +"\n"
+ "> *PO:* " +sheet.getRange(porow1).getValue() +"\n"
+ "> *Order Date:* " +sheet.getRange(OrderDaterow1).getValue() +"\n"
+ "> *Requested By:* " +sheet.getRange(OrderReqrow1).getValue() +"\n"
+ "> *Description:* " +sheet.getRange(Desrow1).getValue() +"\n"
+ "> *Vendor:* " +sheet.getRange(Vendorrow1).getValue() +"\n"
+ "> *ETA:* " +sheet.getRange(ETArow1).getValue() +"\n"
+"\n"
+ "> *PO:* " +sheet.getRange(porow2).getValue() +"\n"
+ "> *Order Date:* " +sheet.getRange(OrderDaterow2).getValue() +"\n"
+ "> *Requested By:* " +sheet.getRange(OrderReqrow2).getValue() +"\n"
+ "> *Description:* " +sheet.getRange(Desrow2).getValue() +"\n"
+ "> *Vendor:* " +sheet.getRange(Vendorrow2).getValue() +"\n"
+ "> *ETA:* " +sheet.getRange(ETArow2).getValue() +"\n"
+"\n"
+ "> *PO:* " +sheet.getRange(porow3).getValue() +"\n"
+ "> *Order Date:* " +sheet.getRange(OrderDaterow3).getValue() +"\n"
+ "> *Requested By:* " +sheet.getRange(OrderReqrow3).getValue() +"\n"
+ "> *Description:* " +sheet.getRange(Desrow3).getValue() +"\n"
+ "> *Vendor:* " +sheet.getRange(Vendorrow3).getValue() +"\n"
+ "> *ETA:* " +sheet.getRange(ETArow3).getValue() +"\n";

 return message;
}

function triggerSlackRequestPD(channel, msg) {
 var slackWebhook = "[SLACK WEBHOOK]";
  var payload = { "channel": channel, "text": msg, "link_names": 1, "username": "Procurementron", "icon_emoji": ":robot_face:" };
 var options = { "method": "post", "contentType": "application/json", "muteHttpExceptions": true, "payload": JSON.stringify(payload) };

Logger.log(UrlFetchApp.fetch(slackWebhook, options));
}

function notifySlackChannelOfFacilitatorAndNoteTaker() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Bulk");
 if (!sheet.getRange("B2").getValue()) return;
var channel = getChannel();

var msg = createMeetingMessage(sheet);
 triggerSlackRequestPD(channel, msg);
}

function isTest() {
 return false;
}

function getChannel() {
 if(isTest()) {
 return "[SLACK CHANNEL]";
 } else {
 return "[SLACK CHANNEL]";
 }
}

Open to any and all suggestions. Ideally, there was a like var get.alldata in a column so that I don't have to make a variant for each cell. Thanks!

Answer 1

• When porow3, OrderDaterow3, OrderReqrow3, Desrow3, Vendorrow3, ETArow3 are empty, you don't want to including them in message.

If my understanding is correct, how about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points:

• I think that in your script, it is required to be modified createMeetingMessage().
• At createMeetingMessage(), all values are the constant ranges, and all labels of each message are also the same.
  • I think that these situation can be used to modify your script.
• In your script, you retrieve the values using getValue().
  • Retrieve all values using getValues().

When above points are reflected to your script, the script is as follows.

Modified script:

Please modify your createMeetingMessage() to the following script, and try it.

function createMeetingMessage(sheet) {
  var values = sheet.getRange("Bulk!A2:L4").getValues(); // Please modify this range, if you want to add rows.
  var message = values.reduce(function(s, e) {
    var porow, OrderDaterow, OrderReqrow, Desrow, Vendorrow, ETArow, _,
    [porow, _, _, Desrow, Vendorrow, _, _, _, _, OrderReqrow, OrderDaterow, ETArow] = e;
    if (porow && OrderDaterow && OrderReqrow && Desrow && Vendorrow && ETArow) {
      s += "> *PO:* " + porow +"\n"
      + "> *Order Date:* " + OrderDaterow +"\n"
      + "> *Requested By:* " + OrderReqrow +"\n"
      + "> *Description:* " + Desrow +"\n"
      + "> *Vendor:* " + Vendorrow +"\n"
      + "> *ETA:* " + ETArow +"\n"
      +"\n";
    }
    return s;
  }, "> *Today's Purchases*" +"\n");
  return message;
}

Note:

• In this modified script, the all values of porow, OrderDaterow, OrderReqrow, Desrow, Vendorrow, ETArow of each row are existing, they are included in the message.
  • If you want to modify it, please modigy this.
• If you want to add rows, please modify Bulk!A2:L4 of var values = sheet.getRange("Bulk!A2:L4").getValues().

References:

• getValue()
• getValues()
• reduce()