GAS: How to Simple automatic task reminder sample using Google Sheets and Slack

Required Skills

Basic understanding of Slack workflow, Incoming Webhooks configuration, and Google App Script.

Overview

This is a simple method of automatic posting to Slack. The reminder information registered in the spreadsheet is posted to the specified user in the Slack channel at the specified date and time.

Script Processing

The script is executed when the reminder date and time entered into the spreadsheet is exceeded at the time of script execution. The timing of posting depends on the script execution trigger, so it is recommended to have the trigger set arbitrarily, such as immediacy or sending a batch every morning.

1. Get sheet:Reminder Retrieves the Reminder sheet and reads all data.
2. Get Current Time: Get the current date and time.
3. Loop through data: Loop through each row of data and check if the notification date/time has been set.
4. Check Frag: If the flag is blank or null, and the current time is past the date and time of the notification, continue processing.
5. Prepare to notify Slack: Using the Slack webhook URL, include the user ID with a mentions in the message and create a payload to send the message to the channel ID.
6. Post to Slack: Use UrlFetchApp.fetch to send notifications to Slack.
7. Update Flag:
   • If the notification is sent, update the flag to sent.
   • If the notification failed, update the flag to error.

Spread Sheet

Spreadsheet Contents. This sample only executes one reminder.
Post if Flag is Null.

How to use Reminder
Please fill in the following fields. Please leave Flag blank when registering.

1. NotificationDateTime Enter the date and time for the reminder in the format yyyy/MM/dd HH:mm:ss
2. Message Please enter a message to be posted, including a URL.
3. UserID If the target of the mentions is a user, enter @username or @userid prefixed with @. If it is a UserGroup, enter as !subteam^groupid.
4. ChannelID Enter the ID of the channel you want to post.

Tip: If you want to mentions to a user group, register according to the following rules
Formatting text for app surfaces Mentioning groups 
https://api.slack.com/reference/surfaces/formatting#mentioning-groups

Sample Script

This is a sample script.
A little log output is also left written to check for errors.

function sendReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reminder");
  var data = sheet.getDataRange().getValues();
  var now = new Date();

  for (var i = 1; i < data.length; i++) { // Skip header row
    var flag = data[i][0]; // Flag in column A
    var notificationDateTimeStr = data[i][1]; // Notification execution date and time in column B
    var message = data[i][2]; // Notification message in column C
    var userId = data[i][3]; // Slack Mention Target UserID in column D
    var channelId = data[i][4]; // Slack channel ID in column E

    // Skip if notification execution date/time is empty
    if (!notificationDateTimeStr) {
      continue;
    }

    var notificationDateTime = new Date(notificationDateTimeStr);

    if (flag !== "sent" && now >= notificationDateTime) {
      var slackURL = "https://hooks.slack.com/services/url"; // Enter the Webhook URL here
      var payload = {
        "channel": channelId,
        "text": message + " <" + userId + ">"
      };

      var options = {
        "method": "post",
        "contentType": "application/json",
        "payload": JSON.stringify(payload)
      };

      // Log output for debugging You can turn this off.
      Logger.log("URL: " + slackURL);
      Logger.log("Payload: " + JSON.stringify(payload));
      Logger.log("Options: " + JSON.stringify(options));

      var response = UrlFetchApp.fetch(slackURL, options);
      Logger.log("Response: " + response.getContentText());

      // Update flag to "sent" to prevent re-execution
      sheet.getRange(i + 1, 1).setValue("sent");
    }
  }
}