Slack Bulk Bot

slack-bulk-bot.js

// README
// GO TO https://api.slack.com/apps?new_app=1, select from scratch, give the app a name and pick a workspace
// GO TO OAuth & Permissions, Oauth permissions -> Scopes, add chat:write and users:read to user scopes
// Press "install to workspace" at the top of the page, approve and copy User OAuth Token
// Go to Google sheets, create a new sheet, press Extentions -> Apps Script, Copy this code, press save, press execute
// Go back to Google Sheets, select Slack -> Initialize and paste the User OAuth token
// Fill out the input sheet with names (or user names), and messages you can use $firstname and $lastname placeholders, but it may be empty
// Press Slack -> Send to Recipients, confirm and watch while the status column is populated, the status column must be empty

const LOOKUP_TPL = [
  'userid',
  'username',
  'fullname',
  'displayname',
  'firstname',
  'lastname',
];

const SETTINGS_TPL = [
  'key',
  'value',
];

const INPUT_TPL = [
  'query',
  'message',
  'status',
];

const SLACK_TOKEN_NAME = 'slack-user-token';

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Slack')
      .addItem('Initialize Sheets', 'initSheets')
      .addItem('Update Lookup Table', 'updateLookupTable')
      .addItem('Send to Recipients', 'postLoop')
      .addToUi();
}

function initSheets() {
  getOrCreateSheet('Input', INPUT_TPL);
  getOrCreateSheet('Lookup', LOOKUP_TPL);
  getOrCreateSheet('Settings', SETTINGS_TPL);
  updateLookupTable();
}

function getOrCreateSheet(sheetName, tpl) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) ?? SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  const headers = sheet.getRange('1:1').getValues();

  const missingColumns = tpl.filter(c => !headers.includes(c));
  missingColumns.forEach((column, i) => {
    sheet.getRange(1, i + headers.length).setValue(column);
  });

  return sheet;
}

function loadSheetAsObject(sheetName, tpl) {
  const sheet = getOrCreateSheet(sheetName, tpl);
  const range = sheet.getDataRange();
  const rows = range.getValues();

  const result = [];
  for (let i = 1; i < rows.length; i++) {
    const data = {};
    for (let j = 0; j < rows[i].length; j++) {
      const key = rows[0][j];
      data[key] = rows[i][j];
      data['set'+key] = (value) => range.getCell(i+1, j+1).setValue(value);
    }
    result.push(data);
  }
  return result;
}

function updateLookupTable() {
  const token = getSetting(SLACK_TOKEN_NAME);
  const members = loadUserData(token).members;

  if (!members) {
    SpreadsheetApp.getUi().alert('Unable to communicate with slack');
    return;
  }
  
  const resultData = members.filter(m => !m.deleted).map(m => ({
    userid: m.id,
    username: m.name,
    fullname: m.profile.real_name,
    displayname: m.profile.display_name,
    firstname: m.profile.first_name,
    lastname: m.profile.last_name,
  }));

  const rows = [
    LOOKUP_TPL,
    ...resultData.map(d => LOOKUP_TPL.map(h => d[h]))
  ];

  const sheet = getOrCreateSheet("Lookup", LOOKUP_TPL);
  sheet.getRange(1,1,rows.length, LOOKUP_TPL.length).setValues(rows);
}

function getLookupTable() {
  const data = loadSheetAsObject("Lookup", LOOKUP_TPL);

  const lookup = {};
  for (var i = 1; i < data.length; i++) {
    const user = data[i];
    lookup[user.username] = user;
    lookup[user.fullname] = user;
    lookup[user.displayname] = user;
    lookup[user.userid] = user;
  }
  return lookup;
}

function getSettings() {
  const data = loadSheetAsObject("Settings", SETTINGS_TPL);
  const result = {};
  for (const row of data) {
    result[row.key] = row.value;
  }
  return result;
}

function getSetting(name) {
  const settings = getSettings();
  if(!settings[name]) {
    let setting;
    while(!setting) {
      setting = SpreadsheetApp.getUi().prompt('Enter value for: '+name).getResponseText();
    }
    getOrCreateSheet("Settings", SETTINGS_TPL).appendRow([name, setting]);
    return setting;
  }
  return settings[name];
}

function confirm(msg) {
  const ui = SpreadsheetApp.getUi();
  return ui.alert(msg, ui.ButtonSet.YES_NO) === ui.Button.YES
}

function postLoop () {
  const lookup = getLookupTable();
  const input = loadSheetAsObject("Input", INPUT_TPL);
  const token = getSetting(SLACK_TOKEN_NAME);


  if(!confirm(`You are going to send a message to ${input.length} users, are you sure?`)) {
    return;
  }

  for (var i = 0; i < input.length; i++) {
    const user = lookup[input[i].query];

    if(input[i].status) continue;    

    const status = user ? 'Sending...' : 'Not Found: ';
    input[i].setstatus(status);

    if(!user) continue;

    const message = ['firstname', 'lastname'].reduce(
      (r, a) => r.replace('$'+a, user[a]),
      ''+input[i].message
    );

    const response = postToSlack(''+user.userid, message, token);
    input[i].setstatus(response.ok ? 'sent' : 'NOT OK');
  }
}

function loadUserData(token) {
  const response = UrlFetchApp.fetch(
    'https://slack.com/api/users.list',
    {
      method             : 'get',
      contentType        : 'application/json',
      headers            : {
        Authorization : 'Bearer ' + token
      },
      muteHttpExceptions : true,
  });
  return JSON.parse(response.getContentText())
}

function postToSlack(channel, message, token) {
  const response = UrlFetchApp.fetch(
    'https://slack.com/api/chat.postMessage',
    {
      method             : 'post',
      contentType        : 'application/json',
      headers            : {
        Authorization : 'Bearer ' + token
      },
      payload            : JSON.stringify({
        'channel' : channel,
        'text' : message,
        'as_user' : true
      }),
      muteHttpExceptions : true,
  })
  return JSON.parse(response.getContentText())
}