For IT Specialist

GAS: How to Script to reply to a thread using only Slack Incoming Webhooks.

Required Skills

Basic understanding of Slack workflow, API settings, and Google App Script.

Description

The script on this page is a case of using Slack workflow.
Linking Slack Workflow and Google Sheet is easy; it is suitable for beginners because it is a simpler process than using a complex script with Token to get timestamps of posts.

Therefore, the design of this page uses only Slack workflows, Incoming Webhooks, Google Sheet, and App Script. is required before sending data to Google Sheet. This is to get the timestamp of the post, and App Script will post it in a thread.

The following is assumed to be used, and the example script is written on the assumption that the creation of Apps on the Slack side, configuration of Incoming Webhooks, and URL acquisition have already been completed.

  • Slack (workflow, Incoming Webhooks)
  • Google (spreadsheets, App Script)
Point

Although you may struggle with the script to get the Thread_ts, it is easy to get the ts by using the Slack workflow. Also, since the number of digits is easy to calculate, you can use spreadsheet functions to process the obtained URLs.

Note that when creating a workflow, the flow to send a message to a channel must be in place prior to the flow to be added to the Google Sheet spreadsheet. If there is no flow to send a message to a channel, it will not appear in the variables to be entered into the spreadsheet and cannot be selected.

Google App Script Flow

This App Script is run when changes are made to the spreadsheet, in order to run the Script when a Slack workflow enters data into the Sheet.

  1. Spreadsheet changes occur (Apps Script triggers on spreadsheet changes)
  2. Script is executed
  3. Check column A. If value is 0, script continues to execute; if non-zero, process aborts
  4. Script enters function into column B. Function to take values in PostURL in column D, convert them, and create Thread_ts
  5. Thread posting of messages written to the script with timestamps in column B
  6. When the script is processed, change the value in column A to 1

The third process in the flow is responsible for stopping the process, which is not controlled by the Apps Script trigger. Without this processing, any changes to the spreadsheet will always cause the last row to be posted. If you dare to re-execute the Post, you can do so by setting column A of the last row to 0.

SheetA

Only a minimal number of tables are written in SheetA as a sample. In reality, the Slack workflow can handle a variety of information, and GAS can reply to threads using them.
The table consists of columns A,B,C,D,E from left to right.
Columns A, C, D, and E are populated by the Slack workflow, and column B is where the script populates the function.

ScriptFlagThread_tsPostPostURLPostUserName

Sample Script

Sample script. A description is also left for error logging when creating the script.

function notifyReservationComplete() {
  var SampleSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetA");
  var lastRow = SampleSheet.getLastRow();
  var flagCell = SampleSheet.getRange(lastRow, 1);
  var flagValue = flagCell.getValue();

  if (flagValue !== 0) {
    Logger.log("flag is non-zero, the process is aborted");
    return;
  }

  // Set up a new expression to retrieve the value of Thread_ts
  var cellReference = 'D' + lastRow; // Last row of column D
  var formula = '=CONCATENATE(LEFT(RIGHT(' + cellReference + ',16),10),".",RIGHT(' + cellReference + ',6))';
  SampleSheet.getRange(lastRow, 2).setFormula(formula); // Set the formula in the second row

  // Wait for spreadsheet calculations once
  SpreadsheetApp.flush();
  
  // Get updated Thread_ts value
  var threadTs = SampleSheet.getRange(lastRow, 2).getValue();
  var mentionUser = SampleSheet.getRange(lastRow, 5).getValue(); // Slack Mentions in Column E
  var slackURL = "https://hooks.slack.com/services/url"; // Here is the URL for Incoming Webhooks
  var message = "The workflow has been submitted.<" + mentionUser + "Thank you so much. Please wait to you."; // Message in reply to thread.Note that the description rules change for the UserGroup mentions.https://api.slack.com/reference/surfaces/formatting#mentioning-groups
  
  var payload = {
    "text": message,
    "thread_ts": threadTs
  };

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

  Logger.log("Payload: " + JSON.stringify(payload));
  Logger.log("Options: " + JSON.stringify(options));
  Logger.log("URL: " + slackURL);

  var response = UrlFetchApp.fetch(slackURL, options);
  Logger.log("Response: " + response.getContentText());
  
  flagCell.setValue(1); // Set flag to 1
}