1

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:enter image description here

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!

Ricky Adams
  • 175
  • 1
  • 11
  • Or perhaps I need to add in a get last row and then work backward from there once the range of rows has been established? – Ricky Adams Oct 23 '18 at 22:39
  • Can I ask you about your question? About ``Row 3``, when ``porow3, OrderDaterow3, OrderReqrow3, Desrow3, Vendorrow3, ETArow3`` are empty, you don't want to including them in ``message``. Is my understanding correct? – Tanaike Oct 23 '18 at 22:52
  • @Tanaike Correct! I only want to post rows that have data in them, and some days there will be data in rows 1-20 and then other days it might only rows 1-5. So I can build out the format with a var for each cell for each row, but I only want it to push to slack if that row has data in it. – Ricky Adams Oct 23 '18 at 22:54
  • Thank you for replying. For example, when ``Row 2`` is not existing, you also don't want to include them? – Tanaike Oct 23 '18 at 22:57
  • @Tanaike Correct! If I continue to build this out for rows 1-20, so like `porow20` and then there's only data in rows 1-17, then I would only want to post those rows into slack and not have it post the headers (PO,Order Date, Requested By, etc.) For the empty data 18-20. – Ricky Adams Oct 23 '18 at 23:01
  • Thank you for replying. I thought that I could understand correctly your situation. So I posted an answer. Could you please confirm it? If that was not what you want, please tell me. At that time, can you explain the detail? I would like to modify it. – Tanaike Oct 23 '18 at 23:02
  • @Tanaike Amazing! This worked perfectly! – Ricky Adams Oct 23 '18 at 23:09
  • Thank you for replying. I'm glad your issue was solved. Thank you, too. – Tanaike Oct 23 '18 at 23:09

1 Answers1

2
  • 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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165