SlackのPublicチャネル一覧を書き出すGoogle Apps Script

slack_public_channels_list.js

const slackApiUrl = 'https://slack.com/api';
const slackToken = 'xoxp-hogehoge';

function execute() {
  // 現在日時を取得
  const currentDate = new Date();
  
  // チャネル一覧を取得
  let url = slackApiUrl + '/conversations.list';
  url += '?token=' + slackToken;
  url += '&exclude_archived=true';
  url += '&types=public_channel';
  url += '&limit=9999';
  
  var json = UrlFetchApp.fetch(url).getContentText();
  var jsonData = JSON.parse(json);
  
  // SpreadSheet記入データ用
  array = [];
  
  // メンバー一覧を取得
  var users = getUsers();
  
  for (let i = 0; i < jsonData.channels.length; ++i) {
    let c = jsonData.channels[i];
    Logger.log(c.name);
    if (!c.is_channel) {
      continue;
    }
    
    var channelCreatedDate = Utilities.formatDate(new Date(c.created * 1000), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
    
    // チャネルごとに最新のメッセージを取得
    var message = getLatestChannelMesage(c.id);

    var creator = users[c.creator];
    
    if (message != null) {
      // メッセージがある場合には現在日時との差分日数を計算
      var messageDate = Utilities.formatDate(new Date(message.ts * 1000), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
      var ts = message.ts;
      var date = new Date(ts * 1000);
      var ellapsedDays = parseInt((currentDate - date) / 86400000, 10);
      var messageUser = users[message.user];
      array.push([c.name, c.id, creator, channelCreatedDate, c.num_members, messageUser, message.text, messageDate, ellapsedDays]);
    } else {
      // メッセージがない場合にはチャネル作成日時と現在時刻の差分日数を計算
      var ts = c.created;
      var date = new Date(ts * 1000);
      var ellapsedDays = parseInt((currentDate - date) / 86400000, 10);
      array.push([c.name, c.id, creator, channelCreatedDate, c.num_members, '', '', '', ellapsedDays]);
    }
  }
  
  // SpreadSheet書き込み
  writeData(array);
}

function getUsers() {
  let url = slackApiUrl + '/users.list';
  url += '?token=' + slackToken;
  
  var json = UrlFetchApp.fetch(url).getContentText();
  var jsonData = JSON.parse(json);
  
  var usersMap = {};
  
  for (let i = 0; i < jsonData.members.length; ++i) {
    let m = jsonData.members[i];
    usersMap[m.id] = m.name;
  }
  return usersMap;
}

function writeData(data) {
  var sheet = SpreadsheetApp.openById("SpreadSheetのID").getSheetByName("list");
  
  // データクリア
  sheet.getRange("A2:I1000").clearContent();
  
  var rows = data.length;
  var cols = data[0].length; 
  var range = sheet.getRange(2, 1, rows, cols)
  range.setValues(data);
  range.sort([{column: 1, ascending: true}, {column: 1, ascending: true}]);
  sheet.setRowHeights(2, rows-1, 21);
}

function getLatestChannelMesage(channelId) {
  let url = slackApiUrl + '/conversations.history';
  url += '?token=' + slackToken;
  url += '&channel=' + channelId;
  url += '&limit=100';
  
  var json = UrlFetchApp.fetch(url).getContentText();
  var jsonData = JSON.parse(json);
  
  var message = {};
  
  for (let i = 0; i < jsonData.messages.length; ++i) {
    var m = jsonData.messages[i];
    // Leaveしましたなどのメッセージではなくコメントだった場合のみ
    if (m.type == 'message' && m.subtype != 'channel_leave' && m.subtype != 'channel_join') {
      return m;
    }
  }
  return null;
}