GASでSlackの投稿をスプレッドシートに出力するスクリプト

-
#2020/12/30追加
スレッドを取得してくるように改修、</pre>と</div>の箇所があるので全角になっているからそこは書き換えないといけない。
いい加減、Git使おうよオレ氏、、、。そして100行ほど膨れ上がりました。


過去分のSlackの投稿を一覧したいと思って、Googleスプレッドシートに出力するGASを仕方なく書いた。色々と癖が強かったのとAPIが古い奴のサンプルしかなかったので仕方なく自分でいろんなとこからコピペしたつぎはぎスクリプトです。
350行ほどです。後、手動でぶっこ抜くところあったので色々と元のシートにパラメータ埋め込んでます。へっぽこなので相変わらずセンスないコードです。

◆GAS

//固定設定値
const SHEET_URL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
const ss = SpreadsheetApp.openByUrl(SHEET_URL);
const SETTING_SHEET = ss.getSheetByName('設定')
const ARCHIVE_URL = SETTING_SHEET.getRange('B2').getValue();
const OLDEST = SETTING_SHEET.getRange('B3').getValue();
const LATEST = SETTING_SHEET.getRange('B4').getValue();
const CHANNEL_SHEET = '対象チャンネル'
const FILE = SETTING_SHEET.getRange('B5').getValue();
const OUTFILE = SETTING_SHEET.getRange('B6').getValue();
const OUTFOLDER = SETTING_SHEET.getRange('B7').getValue();

/* SlackAPI操作 */
//APIトークン
const API_TOKEN = PropertiesService.getScriptProperties().getProperty('API_TOKEN');
if (!API_TOKEN) {
  throw 'You should set "API_TOKEN" property from [File] > [Project properties] > [Script properties]';
}
const SlackAccessor = (function () {
  function SlackAccessor(apiToken) {
    this.APIToken = apiToken;
  }
  let MAX_HISTORY_PAGINATION = 10;
  let HISTORY_COUNT_PER_PAGE = 1000;
  let p = SlackAccessor.prototype;
  //API リクエスト
  p.requestAPI = function (path, params) {
    if (params === void 0) {
      params = {};
    }
    let url = "https://slack.com/api/" + path + "?";
    let qparams = [("token=" + encodeURIComponent(this.APIToken))];
    for (let k in params) {
      qparams.push(encodeURIComponent(k) + "=" + encodeURIComponent(params[k]));
    }
    url += qparams.join('&');
    //console.log("==> GET " + url);
    let response = UrlFetchApp.fetch(url);
    let data = JSON.parse(response.getContentText());
    if (data.error) {
      console.log("GET " + path + ": " + data.error);
    }
    return data;
  };
  //メンバーリスト取得
  p.requestMemberList = function () {
    let response = this.requestAPI('users.list');
    let memberNames = {};
    response.members.forEach(function (member) {
      memberNames[member.id] = member.name;
      //console.log("memberNames[" + member.id + "] = " + member.name);
    });
    return memberNames;
  };
    // チャンネル情報取得
  p.requestChannelInfo = function() {
    let options = {};
    options['types'] = 'public_channel,private_channel';
    let response = this.requestAPI('conversations.list', options);
    //response.channels.forEach(function (channel) {
    //  console.log("channel(id:" + channel.id + ") = " + channel.name);
    //});
    return response.channels;
  };
  //特定チャンネルのメッセージ取得
  p.requestMessages = function (channel, oldest, latest) {
    let _this = this;
    if (oldest === void 0) {
      oldest = '1';
    }
    let messages = [];
    let options = {};
    options['oldest'] = oldest;
    options['latest'] = latest;
    options['count'] = HISTORY_COUNT_PER_PAGE;
    options['channel'] = channel;
    let loadChannelHistory = function (oldest) {
      if (oldest) {
        options['oldest'] = oldest;
      }
      let response = _this.requestAPI('conversations.history', options);
      messages = response.messages.concat(messages);
      return response;
    };
    let resp = loadChannelHistory();
    let page = 1;
    while (resp.has_more && page <= MAX_HISTORY_PAGINATION) {
      resp = loadChannelHistory(resp.messages[0].ts);
      page++;
    }
    //console.log("channel(id:" + channel.id + ") = " + channel.name + " => loaded messages.");
    //最新レコードを一番下にする
    return messages.reverse();
  };
  // 特定チャンネルの特定のスレッドのメッセージ取得
  p.requestThreadMessages = function (channel, ts_array, oldest ,latest) {
    var all_messages = [];
    let _this = this;

    let loadThreadHistory = function (options, oldest) {
      if (oldest) {
        options['oldest'] = oldest;
      }
      Utilities.sleep(1250);
      let response = _this.requestAPI('conversations.replies', options);

      return response;
    };
    ts_array = ts_array.reverse();

    ts_array.forEach(ts => {
      if (oldest === void 0) { oldest = '1'; }

      let options = {};
      options['oldest'] = oldest;
      options['latest'] = latest;
      options['ts'] = ts;
      options['count'] = HISTORY_COUNT_PER_PAGE;
      options['channel'] = channel;

      let messages = [];
      let resp;
      resp = loadThreadHistory(options);
      if(!resp.messages){
        return;
      }
      messages = resp.messages.concat(messages);
      var page = 1;
      while (resp.has_more && page <= MAX_HISTORY_PAGINATION) {
        resp = loadThreadHistory(options, resp.messages[0].ts);
        messages = resp.messages.concat(messages);
        page++;
      }
      // 最初の投稿はスレッド元なので削除
      messages.shift();
      // 最新レコードを一番下にする
      all_messages = all_messages.concat(messages);
      //console.log("channel(id:" + channel.id + ") = " + channel.name + " ts = " + ts + " => loaded replies.");
    });
    return all_messages;
  };
  return SlackAccessor;
})();

function writeDataFormater(channel, messages, memberList) {
  /* 取得したSlack投稿のフォーマット整形 */
  try {
    const COL_DATE = 1; // 日付・時間(タイムスタンプから読みやすい形式にしたもの)
    const COL_USER = 2; // ユーザ名 
    const COL_TEXT = 3; // テキスト内容
    const COL_URL = 4;  // メッセージのURL
    const COL_TIME = 5; // 差分取得用に使用するタイムスタンプ
    const COL_REPLY_COUNT = 6; // スレッド内の投稿数
    const COL_IS_REPLY = 7; // リプライのとき1，そうでないとき0
    const COL_JSON = 8; // 念の為取得した JSON をまるごと記述しておく列
    let record = [];
    for (let msg of messages) {
      var date = new Date(+msg.ts * 1000);
      //console.log("message: " + date);
      if ('subtype' in msg) {
        if (msg.subtype === 'thread_broadcast') {
          continue;
        }
      }
      let row = [];
      // 日付
      let postdate = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
      row[COL_DATE - 1] = postdate;
      // ユーザー名
      row[COL_USER - 1] = memberList[msg.user] || msg.username;
      // 整形した投稿内容
      row[COL_TEXT - 1] = UnescapeMessageText(msg.text, memberList);
      // 投稿URL
      row[COL_URL - 1] = ARCHIVE_URL + channel + '/p' + msg.ts;
      //差分取得用タイムスタンプ
      row[COL_TIME - 1] = msg.ts;
      //スレッド内の投稿数
      if ('reply_count' in msg) {
        row[COL_REPLY_COUNT - 1] = msg.reply_count;
      }
      //リプライ
      if ('thread_ts' in msg) {
        if (msg.ts != msg.thread_ts){
          row[COL_IS_REPLY - 1] = 1;
        }else{
          row[COL_IS_REPLY - 1] = 0;
        }
      }
      // メッセージの JSON 形式
      row[COL_JSON - 1] = JSON.stringify(msg);
      record.push(row);
    }
    return record;
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function UnescapeMessageText(text, memberList) {
  /*  Slackテキスト整形 */
  return (text || '').replace(//g, '>').replace(/"/g, '"').replace(/&/g, '&').replace(/<@(.+?)>/g, function ($0, userID) {
    var name = memberList[userID];
    return name ? "@" + name : $0;
  });
};

function writeSheet(values, labels, sheetname, sheeturl) {
  /* スプレッドシートに書き出し */
  try {
    if (values.length > 0) {
      // シートを追加する
      let spreadSheet = SpreadsheetApp.openByUrl(sheeturl);
      let sheet = set_sheet(spreadSheet,sheetname);
      // 1行目だけヘッダ追加
      let headerCheck = sheet.getLastRow();
      if (headerCheck == 0) {
        sheet.appendRow(labels);
      }
      //シートに書き出し
      values.forEach(function (value) {
        sheet.appendRow(value);
      });
      const n = labels.length;
      // カラム整形
      let width = [150,100,800,300,200,100,100,100]
      let j = 0;
      for (let i = 1; i <= n; i++) {
        //sheet.autoResizeColumns(i,1);
        sheet.setColumnWidth(i, width[j]);
        j =++j;
      }
      return true;
    }
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function dupudataDelete(sheetname,sheeturl,col_num){
  /* 重複データ削除 */
  try {
      let spreadSheet = SpreadsheetApp.openByUrl(sheeturl);
      let sheet = set_sheet(spreadSheet,sheetname);
      let lastRow = sheet.getLastRow();
      let lastCol = sheet.getLastColumn();
      let range = sheet.getDataRange();
      //ソート
      range.sort({column:col_num});
      //重複削除
      range.removeDuplicates([col_num]);
      //ソート後に最終行に移動したヘッダを先頭に戻す
      lastRow = sheet.getLastRow();
      let header = sheet.getRange(lastRow,1,1,lastCol).getValues();
      sheet.insertRowsBefore(1,1);
      sheet.getRange(1, 1, 1, header[0].length).setValues(header);
      sheet.deleteRows(lastRow+1,1);
      return true;
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function set_sheet(spreadsheet,name) {
  /* 1ファイル出力時は同じ名前のシートがなければ作成 */
  let sheet = spreadsheet.getSheetByName(name)
  if (sheet) return sheet
  if(FILE == 'YES'){
    sheet = spreadsheet.getSheets()[0];
  }else{
    sheet = spreadsheet.insertSheet();
  }
  sheet.setName(name);
  return sheet;
}

function getSpreadsheetURL(fileName) {
  /* スプレッドシートが存在していなければ作成、既に存在していれば開く */
  try {
    let folderId = OUTFOLDER;
    let target = findSpreadsheetByName(folderId,fileName)
    if (target==false){
      //新規作成
      const newSS = SpreadsheetApp.create(fileName);
      const file = DriveApp.getFileById(newSS.getId()); 
      DriveApp.getFolderById(folderId).addFile(file)
      DriveApp.getRootFolder().removeFile(file)
      return file.getUrl();
    }else{
      //既存シートオープン
      return target;
    }
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function findSpreadsheetByName(id, name) {
  /* ファイル名からスプレッドシート検索 */
  let folder = DriveApp.getFolderById(id);
  let files = folder.getFilesByName(name);
  while (files.hasNext()) {
    let file = files.next();
    if ('application/vnd.google-apps.spreadsheet' == file.getMimeType()) {
      const ss = SpreadsheetApp.openById(file.getId());
      return ss.getUrl()
    }
  }
  return false;
}

function getLastTimestamp (sheetname,sheetUrl, is_reply) {
  /* スプレッドシートから最新のタイムスタンプ取得 */
  try {
    const COL_TIME = 5;
    const COL_IS_REPLY = 7;
    let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
    let sheet = spreadSheet.getSheetByName(sheetname);
    let lastRow = sheet.getLastRow();
    if(lastRow > 0) {
      let row_of_last_update = 0;
      for (let row_no = lastRow; row_no >= 1; row_no--) {
        if (parseInt(sheet.getRange(row_no, COL_IS_REPLY).getValue()) == is_reply) {
          row_of_last_update = row_no;
          break;
        }
      }
      if (row_of_last_update === 0) {
        return '1';
      }
      return sheet.getRange(row_of_last_update, COL_TIME).getValue();
    }
    return '1';
    } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
      return false;
  }
}

function getThreadtimestamp (sheetname,sheetUrl) {
  /* スプレッドシートからタイムスタンプ列を取得 */
  try {
    const COL_TIME = 5;
    const RANGE_COL = 2;
    let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
    let sheet = spreadSheet.getSheetByName(sheetname);
    let lastRow = sheet.getLastRow();
    let result = [];
    if(lastRow > 0) {
      //リプライが存在するタイムスタンプのみ取得
      const range_data = sheet.getRange(2, COL_TIME, lastRow - 1, RANGE_COL).getValues();
      range_data.map(function( value ) {
        if(value[1]!=''){
          result.push(String(value[0]));
        }
      });
      return result;
    }
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
      return false;
  }
}

function getThreadTS(messages) {
  /* スレッド一覧取得 */
  try {
      let result = [];
      messages.forEach(function (msg) {
        if(msg.reply_count > 0){
          result.push(msg.ts);
        }
      });
      return result;
    } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
      return false;
  }
}

function getChannnelSheetValue(sheetname,sheetUrl){
 /* 対象チャンネルシートから対象チャンネル取得 */
  try {
    const COL_ID = 1;
    const COL_NAME = 2;
    const START_ROW = 2;
    let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
    let sheet = spreadSheet.getSheetByName(sheetname);
    if (sheet) {
      let result = [];
      let lastRow = sheet.getLastRow();
      if(lastRow > 0) {
        for (let i = START_ROW; i <= lastRow; i++) {
          let values = {};
          values['id'] = sheet.getRange(i, COL_ID).getValue();
          values['name'] = sheet.getRange(i, COL_NAME).getValue();
          result.push(values);
        }
      }
      return result;      
    }else{
      return false;
    }
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function getUnixTime(dateTime) {
　/* UNIX時間取得 */
  const date = new Date(dateTime);
  const milsec = date.getTime();
  let sec = milsec / 1000;
  const time = sec.toString();
  return time;
}

function main() {
  try {
    const slack = new SlackAccessor(API_TOKEN);
    //メンバーリスト取得
    const memberList = slack.requestMemberList();
    //チャンネルID取得
    let channelInfo;
    channelInfo = getChannnelSheetValue(CHANNEL_SHEET,SHEET_URL);
    if (channelInfo == false){
      channelInfo = slack.requestChannelInfo();
    }
    //終了日設定
    const dt = new Date
    const y = dt.getFullYear();
    const m = ("00" + (dt.getMonth()+1)).slice(-2);
    const d = ("00" + dt.getDate()).slice(-2);
    const hh =("00" + dt.getHours()).slice(-2);
    const mm =("00" + dt.getMinutes()).slice(-2);
    const ss =("00" + dt.getSeconds()).slice(-2);
    let latest = getUnixTime( y + '/' + m + '/' + d + ' ' + hh + ':' + mm + ':' + ss);
    if(Object.prototype.toString.call(LATEST)=='[object Date]'){
      latest = getUnixTime(LATEST);
    }
    let timestamp = latest;
    let first = latest;
    let OLDESTFlg = 0;
    //開始日の設定値がある場合に取得
    if(Object.prototype.toString.call(OLDEST)=='[object Date]'){
      timestamp = getUnixTime(OLDEST);
      first = timestamp;
      OLDESTFlg = 1;
    }
    //チャンネル内の投稿取得
    const title = ['投稿日時','投稿者','投稿内容','アーカイブURL','差分用タイムスタンプ','スレッド内投稿数','リプライ','元JSON'];
    let sheetUrl = "";
    //スプレッドシート1個のみ
    if (FILE == 'NO'){
        sheetUrl = getSpreadsheetURL(OUTFILE);
    }    

    // チャンネルごとにメッセージ内容を取得 
    //let first_exec_in_this_channel = false;
    const sortCol = 5;
    for (let ch of channelInfo) {
      let channel = ch.id;
      let sheetname = ch.name;
      //チャンネル毎のスプレッドシート作成
      if (FILE == 'YES'){
        sheetUrl = getSpreadsheetURL(sheetname);
      }
      if(OLDESTFlg === 0){
        //シートごとの最終タイムスタンプ取得
        timestamp = getLastTimestamp(sheetname,sheetUrl,0);
        if (!timestamp) {
          timestamp = latest;
        }
      }
      //Slack投稿取得
      let messages  = slack.requestMessages(channel, timestamp, latest);
      if(messages.length > 0){
        let data = writeDataFormater(channel, messages, memberList);
        //let rtn = writeSheet(data,title,sheetname,sheetUrl);
        //取得した分とスプレッドシートに蓄積されているタイムスタンプをマージした上でスレッドを取得
        let message_ts_array = getThreadTS(messages); //昨日分から取得
        let sheet_ts_array = getThreadtimestamp(sheetname,sheetUrl); //シートから取得
        let merge_ts_array = message_ts_array.concat(sheet_ts_array);
        const ts_array = Array.from(new Set(merge_ts_array));
        if(ts_array.length > 0){
          let thread_messages = slack.requestThreadMessages(channel, ts_array, timestamp);
          let thread_data = writeDataFormater(channel, thread_messages, memberList);
          let thread_rtn = writeSheet(thread_data,title,sheetname,sheetUrl);
        }
      //重複データ削除
      dupudataDelete(sheetname,sheetUrl,sortCol);
      }
    }

  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}



◆参考サイト
先人

SlackAPI関連

スレッド操作

ファイル名一致

コメント

コメントを投稿

このブログの人気の投稿

GASでGoogleDriveのサブフォルダとファイル一覧を出力する

-
 タイトルのまんまです。 以前、EXCELで似たようなこと をしていたのでGASではどうするのかなぁと思ってやってみました。まぁほとんど丸パクリです。とりあえず、また使うかもしれないので自分用にメモです。しかしGASでGoogleDriveのファイル一覧嘗め回そうとすると3分かかってしまう。数が多いと6分制限に引っかかるのでこれでは使えない。せいぜい100ファイルとか数が少ないところで使う分にはいいけどダイナミックな場合は何回かに分割してやらないと少し厳しいかもしれない。 ◆GAS //設定値取得 const SHEET_URL = SpreadsheetApp.getActiveSpreadsheet().getUrl(); const ss = SpreadsheetApp.openByUrl(SHEET_URL); const SETTING_SHEET = ss.getSheetByName('設定') const TARGET_FOLDER_ID = SETTING_SHEET.getRange('B2').getValue(); const SAVE_FOLDER_ID = SETTING_SHEET.getRange('B3').getValue(); const SAVE_FILE_NAME = SETTING_SHEET.getRange('B4').getValue(); const SAVE_SHEET_NAME = SETTING_SHEET.getRange('B5').getValue(); function writeSheet(values, labels, sheetUrl, sheetname, width) { /* スプレッドシートに書き出し */ try { if (values.length > 0) { // シートを追加する let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl); let sheet = set_sheet(sheetUrl,sheetname); // 1行目だけヘッダ追加 let he...
続きを読む

マクロ経済学(IS-LM分析)

-
イメージ
危うく3日坊主になりそうでした。とりあえず今日は 「IS-LM分析」 です。 テキストでは 簡便4象限法 とかいうのの説明が多いです。なのでさっぱり わけわからん状態ですが、それなりに頑張ってみます。 1: IS曲線 の導出 財市場の均衡を表す。Iは投資を表し、Sは貯蓄を表す。 ・IS曲線導出の材料 I→利子率の関数、S→所得の関数で「I=S」で財市場を表す。 ・ 投資関数 利子率の減少関数、I (投資関数)=I(r)　※rは利子率 ・政府活動の導入 裁量的な拡張的な財政政策はIS曲線をシフトさせる。 IS曲線の右シフト=政府支出の増大や減税等の有効需要を増加させる要因による 2: LM曲線 の導出 利子率の低下が国民所得の増大につながる因果関係を明らかにする目的 ・LM曲線導出の材料 貨幣需要(MD)=L、L=取引的動機+予備的動機(L1)+投機的動機(L2) MD=L1+L2 貨幣供給(Ms)=名目貨幣供給/物価(P) ・LM曲線のシフト 金融緩和策・物価下落：実質貨幣供給(MS)が増大→右(下)へシフト 金融引締策・物価上昇：実質貨幣供給(MS)が減少→左(上)へシフト 独立投資拡大→IS曲線が右にシフト 利子率がr1→r2に下落する。 IS曲線、LM曲線の均衡点がE→Fに移動する。 国民所得がY1→Yfとなって増大する。 3:特殊なLM曲線 ・ 流動性の罠 のLM曲線 金融政策は無効→LM曲線をシフトさせても均衡点Eは変わらない IS曲線を右にシフトさせる拡張的な財政政策を実施すると均衡点が E→E´となり国民所得がY1→Y2に増加するので財政政策は有効となる。　　　 ・ 投資の利子率に対する弾力性ゼロ 金融政策は無効→LM曲線を右にシフトさせても国民所得が増えない。 流動性の罠と同じくIS曲線を右にシフトさせる拡張的な財政政策を実施すると 均衡点がE→E´となり国民所得がY1→Y2に増加するので財政政策は有効となる。　　　　　　 4: クラウディング・アウト 政府が市場に介入して公共投資を行うと民間の投資が締出しを受ける ・古典派による財政政策の無効論 政府支出を拡大すると利子率が上昇してしまい。投資が減少する。 政府の支出がクラウディングアウトを引き起こして支...
続きを読む

IEのセキュリティ設定をいじくるバッチ

-
まぁかなり頻出ですがIEでアプリ用に設定しないといけなくて クリックOnlyで処理したかったので色々と調べてみて作ってみた。 ローカルイントラネットの設定いじくるのとActiveX関連をいじくるのと 互換表示設定をいじくっています。 というよりそういう設定しないと動かないアプリの組み方辞めてください。 頼むから・・・。 挙動が微妙に他と違うのでIEとかEdgeには苦しめられます。 @echo off set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap" reg add %REGROOT% /f /v "AutoDetect" /t REG_DWORD /d "0" reg add %REGROOT% /f /v "IntranetName" /t REG_DWORD /d "1" reg add %REGROOT% /f /v "ProxyBypass" /t REG_DWORD /d "1" reg add %REGROOT% /f /v "UNCAsIntranet" /t REG_DWORD /d "1" rem IEのローカルイントラネット設定(*ドメイン指定) set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Ranges\Range1" set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\対象ドメイン" reg add %REGROOT% /f /v "*" /t REG_DWORD /d "1" rem スクリプトを実行しても安全だとマークされていない activex コントロールの初期化とスクリ...
続きを読む