Slackに投稿されたメッセージを自動でスプレッドシートに転記する方法【GAS】

function myFunction() {

}

function saveMessagesToSheet() {
  const token = "YOUR_SLACK_BOT_TOKEN";
  const channel = "YOUR_CHANNEL_ID";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  // ヘッダーの設定
  if (sheet.getRange("A3").getValue() === "") {
    sheet.getRange("A3").setValue("投稿時刻");
    sheet.getRange("B3").setValue("投稿者");
    sheet.getRange("C3").setValue("投稿内容");
  }

  const lastExecutionTime = new Date(sheet.getRange("A1").getValue());
  const apiUrl = `https://slack.com/api/conversations.history?channel=${channel}&limit=100`;
  const options = {
    method: "get",
    headers: {
      "Authorization": `Bearer ${token}`
    }
  };

  const response = UrlFetchApp.fetch(apiUrl, options);
  const data = JSON.parse(response.getContentText());

  for (let i = 0; i < data.messages.length; i++) {
    const message = data.messages[i];
    processMessage(message, token, sheet, lastExecutionTime);

    // スレッドの返信がある場合
    if (message.reply_count > 0) {
      const repliesApiUrl = `https://slack.com/api/conversations.replies?channel=${channel}&ts=${message.ts}`;
      const repliesResponse = UrlFetchApp.fetch(repliesApiUrl, options);
      const repliesData = JSON.parse(repliesResponse.getContentText());

      for (let j = 0; j < repliesData.messages.length; j++) {
        processMessage(repliesData.messages[j], token, sheet, lastExecutionTime);
      }
    }
  }

  // 最終実行時刻の更新（JST）
  const currentTimeJST = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
  sheet.getRange("A1").setValue(currentTimeJST);
}

function processMessage(message, token, sheet, lastExecutionTime) {
  const messageTimestamp = new Date(message.ts * 1000);

  if (messageTimestamp > lastExecutionTime) {
    const userApiUrl = `https://slack.com/api/users.info?user=${message.user}`;
    const userResponse = UrlFetchApp.fetch(userApiUrl, {
      method: "get",
      headers: {
        "Authorization": `Bearer ${token}`
      }
    });

    const userData = JSON.parse(userResponse.getContentText());
    const userName = userData.user ? userData.user.real_name : "Unknown User";

    const nextRow = sheet.getLastRow() + 1;
    sheet.getRange(nextRow, 1).setValue(messageTimestamp);
    sheet.getRange(nextRow, 2).setValue(userName);
    sheet.getRange(nextRow, 3).setValue(message.text);
  }
}