Google Sheets Extract to Slack Message

sheets_to_slack.gs

/* 
-----------------------------------------------------------------------------------------------------------------------------
|  This script sends a reminder message to a Slack channel for rows with blank data and tagging the owner of that data.      |
|  Currently, it is only pulling from the last 5 days, but this can be changed with the daysToSubtract variable.             |
|  Google Apps lets you send this out based on a number of triggers. The original script went out daily, so employees could  | |fill in their data before EOD.                                                                                              |
|  You will need your chosen channel's Webhook URL.                                                                          |
|                                                                                                                            |
|  The original use for this was for a sales team filling out information about their new meetings.                          |
|  We were having an issue with employees forgetting to fill out if the meeting was attended, so this bot reminded them & the| |team at large by tagging them in slack.                                                                                     |
------------------------------------------------------------------------------------------------------------------------------
*/


// Setup of the sheet
const sheet = SpreadsheetApp.getActiveSheet();

// Setup of the date range to compare. Currently, it is using 5 days until Present.
var now = new Date();
var formatNow = Utilities.formatDate(now, 'America/New_York', 'MM/dd/yyyy'); // Today
var daysToSubtract = 5;
var withinWeek = new Date(now.getTime()-daysToSubtract*(3600*24*1000));
var formatWeek = Utilities.formatDate(withinWeek, 'America/New_York', 'MM/dd/yyyy'); // 5 Days ago

// Counter for formatting the final list
var meetingCount = 0;

// Other empty Globals
var finalList;
var tagUsers;

/* 
  This function will create two empty arrays, one for the list of missed entries and one for tagging users in Slack
  First, the function gets the data ranges in spreadsheed and adds those columns to an array index.
  Second, the for loop cycles through the super long array (4 results per line). Based on the if statement,
  the loop removes all arrays that don't fit the statement. 
*/
function findMeetings() { // Setting up data range and empty arrays
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();
  var personListOne = [];
  var tagList = [];

  for (i in data) { // For a data row within the entire data range
    var row = data[i];
    let attended = row[6];    // Column G
    var name = row[1];        // Column - B
    var date = Utilities.formatDate(row[3],'America/New_York','MM/dd/yyyy');  // Column - D
    var company = row[4];   // Column - E
    let missedEntries = [name, date, company, attended];
    /* 
      Adding a For Loop will pull a result for EACH element, aka 4 results per line. 
      This pulls one for each group of missedEntries
      This then removes the last value (attended, since we already know it is a blank), converts to a string,
      and adds to a new array. The counter will compare if there is more than one entry in the array. 
    */ 
    if ((missedEntries[3] == "") && (missedEntries[1] >= formatWeek) && (missedEntries[1] <= formatNow)) {
        meetingCount += 1;
        missedEntries.pop();
        missedEntries.toString();
        personListOne.push(missedEntries);
        //Logger.log(personListOne);
      };
    };
  /* Now outside of the if statement: 
    the array is built, and we want each group to be on a new line, remove the commas and add a hyphen.
    The counter counts if there is 1 or more meetings, or not. If 0, it sends a certain message, not tagging anyone.
    If it is one or more, it splices by the first array (index 0), adds a new line, and replaces commans with hyphens.  
   */
  if (meetingCount >= 1) {
    var personListTwo = personListOne.splice(0).join('\n');
    var finalList = personListTwo.replace(/,/g, ' - ');
  /* 
      This if statement is going to only tag those who appear in the previous list.
      No need to tag people who have done their work and contribute to clean data.
      You can find a person's slack UUID by clicking their name, clicking the three dots to the right on the sidebar, and           clicking "Copy member ID"
   */
    if (finalList.includes('name 1')) {
      tagList.push('<@slack_id_number>');
    }
    if (finalList.includes('name 2')) {
      tagList.push('<@slack_id_number>');
    }
    if (finalList.includes('name 3')) {
      tagList.push('<@slack_id_number>');
    }
    if (finalList.includes('name 4')) {
      tagList.push('<@slack_id_number>');
    }
    /* 
      Continue these if statements for each person that needs to be tagged in Slack
      In hindsight, a dictionary & for loop would likely have been a cleaner option,
      but work got busy and I couldn't go back to clean it up. 
    */
    }
    var tagUsers = tagList.toString();
    /* 
    
    Now, we're building the payload for the Slack Message. 
    This is very specific and prone to errors, so check that it works using Slack's tool:
    https://app.slack.com/block-kit-builder/

  Copy from VP:
    ":rotating_light::rotating_light::rotating_light:  New Meeting Tracker Alert! :rotating_light::rotating_light::rotating_light:
    It's your daily reminder to update the new meeting tracker.
    If you've been tagged, please address this by EOD."
  */
  
    let payloadText =
{
  "blocks": [
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": ":rotating_light::rotating_light::rotating_light: New Meeting Tracker Alert! :rotating_light::rotating_light::rotating_light:"
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "It's your daily reminder to update the new meeting tracker."
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "If you've been tagged, please address this by EOD."
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": finalList // Sends list with perons's name, meeting date, and company
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "In case you need it, here's a link to the sheet. :point_right:"
      },
      "accessory": {
        "type": "button",
        "text": {
          "type": "plain_text",
          "text": "Sheet Name",
          "emoji": true
        },
        "value": "sheet_link_123",
        "url": "https://docs.google.com/unique_sheet_url | Sheet Name",
        "action_id": "button-action"
      }
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": tagUsers // Tags with slack user numbers if they appear in finalList
      }
    }
  ]
};
  // This is standard operating procedure to creating the payload and destination
    const webhook = "https://hooks.slack.com/services/unique_channel_identifiers/";
    const options = {
      method: "post", 
      contentType: "application/json",
      muteHttpExceptions: true,
      payload: JSON.stringify(payloadText),
    };
    const sendMsg = UrlFetchApp.fetch(webhook, options);
    var respCode = sendMsg.getResponseCode();
    //Logger.log(sendMsg); // Debug to confirm send
    //Logger.log(respCode); // Debug to show errors, if any
  /*
    This is the else statement from the if for tagging users. If that list is empty, then this messages sends. It is a message that says the data is clean. 
  */
  } else {
    let noMeetingMsg = 
    {
	    "blocks": [
		{
			"type": "section",
			"text": {
				"type": "mrkdwn",
				"text": ":star::star::star:  New Meeting Tracker Alert! :star::star::star:"
			}
		},
		{
			"type": "section",
			"text": {
				"type": "mrkdwn",
				"text": "Good job, everyone! All meetings from the last 5 days are up to date. Keep doing what you're doing!"
			}
		}
	]
};
// Again, the standard operating procedure to creating the payload and destination, just as above.
  
    const webhook = "https://hooks.slack.com/services/unique_channel_identifiers/";
    const options = {
      method: "post", 
      contentType: "application/json",
      muteHttpExceptions: true,
      payload: JSON.stringify(noMeetingMsg),
    };
    const sendMsg = UrlFetchApp.fetch(webhook, options);
    var respCode = sendMsg.getResponseCode();
    
    //Logger.log(sendMsg); // Debug to confirm send
    //Logger.log(respCode); // Debug to show errors, if any
}
};