生成AIを使ってGoogleフォーム×Slackツールの自動リマインドツールを作った話

published

author

authorDisplayName

Yasuaki Murata

どう解決したか？

Google Apps Script (GAS) を使ってアンケート未対応者を抽出し、Slack Botで通知する仕組みを構築しました。これにより、運営担当者が手動で確認・リマインドする手間を削減できます。

ツールの設定方法は以下の通りです。

ツールの設定手順

1.初期設定

最初に1回だけ行う設定作業です。

1-1. Slack Botの作成

まず、通知を送るためのSlack Botを作成します。

Slack APIにアクセスして[Create New App] > [From scratch]からアプリを新規作成します。

左メニューの[Settings] > [Basic Information]にてアイコンや名前を設定します。

左メニューの[Features] > [OAuth & Permissions]を開きます。

[Bot Token Scopes]にて必要な以下権限を付与します。

chat:write

im:write

users:read

users:read.email

[OAuth Tokens]にて”Install to ~ ”をクリックします。

インストールしたらBot User OAuth Token（xoxb- から始まる文字列）が追加されるのでコピーしてください。

1-2. GASの作成

次に、リマインドを管理するGoogleスプレッドシートとGASを準備します。

新規のGoogleスプレッドシートを作成してください。

メニューの [拡張機能] > [Apps Script] をクリックして元々あるコードを削除し、以下コードを貼り付けます。


/**
 * Googleフォーム未回答者SlackリマインドBOT
 */

// ----------------------------------------
// 設定・定数
// ----------------------------------------

const APP_NAME = "SlackリマインダーBOT";
const PROPS = PropertiesService.getScriptProperties();

/** シート定義とスタイル設定 */
const SHEET_CONFIG = {
  MASTER: {
    NAME: '管理コンソール',
    HEADERS: ['実行ON', 'アンケートURL', '回答用シートURL', '対象者リストシート名', '締切日', '実行ログ'],
    WIDTHS: [60, 200, 200, 200, 100, 300],
    SAMPLE: [false, 'https://docs.google.com/forms/d/...', 'https://docs.google.com/spreadsheets/d/...', '2024春_社員総会', '2025/12/31', '（自動記録）']
  },
  TARGET: {
    HEADERS: ['通知ON', '氏名', 'メールアドレス', 'リマインド結果'],
    WIDTHS: [60, 150, 250, 250],
    SAMPLE: [false, '山田 太郎', 'taro.yamada@example.com', '']
  }
};

// ----------------------------------------
// UI・メニュー関連
// ----------------------------------------

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  
  ui.createMenu('🛠 リマインド管理メニュー')
    .addItem('1. 初回セットアップ (管理シート+API)', 'performInitialSetup')
    .addItem('2. 未作成の対象シートを一括作成', 'createMissingTargetSheets')
    .addSeparator()
    .addItem('3. 定期実行設定 (カスタム)', 'showTriggerSettingsDialog')
    .addItem('   ∟ 設定解除', 'menuDeleteTriggers')
    .addSeparator()
    .addItem('4. 手動リマインド実行', 'executeAllReminders')
    .addToUi();
}

// ----------------------------------------
// 1. 初回セットアップ
// ----------------------------------------

function performInitialSetup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ui = SpreadsheetApp.getUi();

  let masterSheet = ss.getSheetByName(SHEET_CONFIG.MASTER.NAME);
  if (!masterSheet) {
    masterSheet = ss.insertSheet(SHEET_CONFIG.MASTER.NAME, 0);
    formatSheet_(masterSheet, SHEET_CONFIG.MASTER.HEADERS, SHEET_CONFIG.MASTER.WIDTHS, '#4a86e8');
    masterSheet.getRange(2, 1, 999, 1).insertCheckboxes();
    const sampleRow = SHEET_CONFIG.MASTER.SAMPLE;
    masterSheet.getRange(2, 1, 1, sampleRow.length).setValues([sampleRow]).setFontColor('#808080');
    masterSheet.getRange(2, 5, 999, 1).setNumberFormat('yyyy/MM/dd');
    
    const defaultSheet = ss.getSheetByName('シート1');
    if (defaultSheet) try { ss.deleteSheet(defaultSheet); } catch(e) {}
    
    ui.alert('作成完了', '管理コンソールを作成しました。\n続いてSlackトークンの設定を行います。', ui.ButtonSet.OK);
  } else {
    ui.alert('確認', '管理コンソールは既に存在します。\nSlackトークンの設定確認に進みます。', ui.ButtonSet.OK);
  }
  setSlackToken();
}

function setSlackToken() {
  const ui = SpreadsheetApp.getUi();
  const storedToken = PROPS.getProperty('SLACK_BOT_TOKEN');
  const msg = storedToken ? '設定済みです。変更する場合のみ入力してください。' : 'xoxb-から始まるトークンを入力してください。';
  const response = ui.prompt('Slack Bot Token設定', msg, ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() == ui.Button.OK) {
    const token = response.getResponseText().trim();
    if (!token || !token.startsWith('xoxb-')) {
      ui.alert('エラー', '無効なトークン形式です。', ui.ButtonSet.OK);
      return;
    }
    PROPS.setProperty('SLACK_BOT_TOKEN', token);
    ui.alert('保存完了', 'トークンを保存しました。', ui.ButtonSet.OK);
  }
}

// ----------------------------------------
// 2. シート一括作成ロジック
// ----------------------------------------

function createMissingTargetSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName(SHEET_CONFIG.MASTER.NAME);
  if (!masterSheet) {
    SpreadsheetApp.getUi().alert('エラー', '管理コンソールが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const data = masterSheet.getDataRange().getValues();
  const SHEET_NAME_COL_IDX = 3;
  const LOG_COL_IDX = 5;
  let createdCount = 0;

  for (let i = 1; i < data.length; i++) {
    const sheetName = data[i][SHEET_NAME_COL_IDX];
    if (!sheetName) continue;

    const existingSheet = ss.getSheetByName(sheetName);
    if (!existingSheet) {
      const newSheet = ss.insertSheet(sheetName);
      formatSheet_(newSheet, SHEET_CONFIG.TARGET.HEADERS, SHEET_CONFIG.TARGET.WIDTHS, '#6aa84f');
      newSheet.getRange(2, 1, 999, 1).insertCheckboxes();
      const sampleRow = SHEET_CONFIG.TARGET.SAMPLE;
      newSheet.getRange(2, 1, 1, sampleRow.length).setValues([sampleRow]).setFontColor('#808080');
      
      masterSheet.getRange(i + 1, LOG_COL_IDX + 1).setValue(`シート作成完了 (${formatDate_()})`).setFontColor('#000000');
      createdCount++;
    }
  }
  const msg = createdCount > 0 ? `${createdCount}枚のシートを新規作成しました。` : '新規作成が必要なシートはありませんでした。';
  SpreadsheetApp.getUi().alert('処理完了', msg, SpreadsheetApp.getUi().ButtonSet.OK);
}

// ----------------------------------------
// 3. 定期実行設定 (GUI版 - 時間毎対応)
// ----------------------------------------

/** 設定ダイアログを表示 */
function showTriggerSettingsDialog() {
  const html = HtmlService.createHtmlOutput(getTriggerUiHtml_())
    .setWidth(400)
    .setHeight(400); // 少し高さを広げた
  SpreadsheetApp.getUi().showModalDialog(html, '⏰ 定期実行スケジュールの設定');
}

/** クライアント(HTML)から呼ばれる設定処理 */
function setupTriggerFromClient(config) {
  try {
    deleteAllTriggers_(true); // 既存全削除

    const time = ScriptApp.newTrigger('executeAllReminders').timeBased();
    let triggerBuilder;
    let msg = "";

    // 頻度に応じた設定
    if (config.freq === 'HOURLY') {
      // 時間ごと (1, 2, 4, 6, 8, 12)
      triggerBuilder = time.everyHours(parseInt(config.interval));
      msg = config.interval + "時間ごとにセットしました。";
      
    } else if (config.freq === 'DAILY') {
      // 毎日
      triggerBuilder = time.everyDays(1).atHour(parseInt(config.hour));
      msg = "毎日 " + config.hour + "時台にセットしました。";

    } else if (config.freq === 'WEEKDAY') {
      // 平日 (月-金)
      ['MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY'].forEach(day => {
         ScriptApp.newTrigger('executeAllReminders').timeBased().onWeekDay(ScriptApp.WeekDay[day]).atHour(parseInt(config.hour)).create();
      });
      return "平日(月-金)の " + config.hour + "時台にセットしました。"; // ここでリターン

    } else if (config.freq === 'WEEKLY') {
      // 毎週
      triggerBuilder = time.onWeekDay(ScriptApp.WeekDay[config.day]).atHour(parseInt(config.hour));
      msg = "毎週曜日の " + config.hour + "時台にセットしました。";
      
    } else {
      throw new Error('不明な頻度設定');
    }

    if (triggerBuilder) {
      triggerBuilder.create();
    }
    
    return msg;
  } catch (e) {
    throw new Error("設定エラー: " + e.message);
  }
}

/** トリガー削除 */
function menuDeleteTriggers() {
  deleteAllTriggers_(false);
}

function deleteAllTriggers_(silent) {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(t => ScriptApp.deleteTrigger(t));
  if (!silent) SpreadsheetApp.getUi().alert('完了', '定期実行を解除しました。', SpreadsheetApp.getUi().ButtonSet.OK);
}

// ----------------------------------------
// 4. リマインド実行
// ----------------------------------------

function executeAllReminders() {
  const token = PROPS.getProperty('SLACK_BOT_TOKEN');
  if (!token) {
    showError_('Slackトークン未設定です。');
    return;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName(SHEET_CONFIG.MASTER.NAME);
  if (!masterSheet) {
    showError_('管理シートが見つかりません。');
    return;
  }

  const data = masterSheet.getDataRange().getValues();
  const colIndex = { active: 0, url: 1, resUrl: 2, sheetName: 3, deadline: 4, result: 5 };
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  let processedCount = 0;
  let errorCount = 0;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const resultCell = masterSheet.getRange(i + 1, colIndex.result + 1);

    if (row[colIndex.active] !== true) continue;

    if (!isValidUrl_(row[colIndex.url]) || !isValidUrl_(row[colIndex.resUrl])) {
      resultCell.setValue('URL形式エラー').setBackground('#fff2cc');
      continue;
    }
    if (!row[colIndex.sheetName]) {
      resultCell.setValue('シート名未入力').setBackground('#fff2cc');
      continue;
    }

    if (row[colIndex.deadline]) {
      const deadline = new Date(row[colIndex.deadline]);
      deadline.setHours(23, 59, 59);
      if (today > deadline) {
        resultCell.setValue(`期限切れ (${formatDate_()})`).setFontColor('#808080').setBackground('#f3f3f3');
        continue;
      }
    }

    try {
      const result = processProject_(ss, row, colIndex, token);
      resultCell.setValue(result).setFontColor('#000000').setBackground('#d9ead3');
      processedCount++;
    } catch (e) {
      console.error(`Row ${i + 1}: ${e.message}`);
      resultCell.setValue(`エラー: ${e.message}`).setFontColor('#cc0000').setBackground('#f4cccc');
      errorCount++;
    }
  }

  try {
    if (processedCount > 0 || errorCount > 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast(`処理完了: ${processedCount}件, エラー${errorCount}件`, 'SlackリマインダーBOT');
    }
  } catch (e) {}
}

function processProject_(ss, row, colIndex, token) {
  const targetSheetName = row[colIndex.sheetName];
  const targetSheet = ss.getSheetByName(targetSheetName);
  if (!targetSheet) throw new Error(`対象シート「${targetSheetName}」が見つかりません`);

  const answeredEmails = getAnsweredEmails_(row[colIndex.resUrl]);
  const answeredSet = new Set(answeredEmails);

  const targetData = targetSheet.getDataRange().getValues();
  const tCol = { notifyCheck: 0, name: 1, email: 2, result: 3 };
  let stats = { sent: 0, answered: 0, skipped: 0, error: 0 };
  const formTitle = getFormTitle_(row[colIndex.resUrl]) || 'アンケート';

  for (let i = 1; i < targetData.length; i++) {
    const email = targetData[i][tCol.email];
    const shouldNotify = targetData[i][tCol.notifyCheck];
    const resultCell = targetSheet.getRange(i + 1, tCol.result + 1);

    if (!email) continue;

    if (answeredSet.has(email)) {
      if (targetData[i][tCol.result] !== '回答済') {
        resultCell.setValue('回答済').setBackground('#d9ead3');
      }
      stats.answered++;
      continue;
    }

    if (shouldNotify !== true) {
      stats.skipped++;
      continue;
    }

    const slackId = lookupUserByEmail_(email, token);
    if (slackId) {
      const msg = `【リマインド】\n未回答のアンケートがあります。\n件名: ${formTitle}\nURL: ${row[colIndex.url]}\n\nご回答をお願いします。`;
      const postRes = postMessageToSlack_(slackId, msg, token);
      
      if (postRes && postRes.ok) {
        resultCell.setValue(`送信済 (${formatDate_()})`).setBackground('#fff2cc');
        stats.sent++;
        Utilities.sleep(1000);
      } else {
        resultCell.setValue(`送信エラー`).setBackground('#f4cccc');
        stats.error++;
      }
    } else {
      resultCell.setValue(`Slack ID不明`).setBackground('#f4cccc');
      stats.error++;
    }
  }
  return `送信:${stats.sent}, 済:${stats.answered}, ID不明/Err:${stats.error} (${formatDate_()})`;
}

// ----------------------------------------
// ユーティリティ & HTML
// ----------------------------------------

function getAnsweredEmails_(url) {
  try {
    const ss = SpreadsheetApp.openByUrl(url); 
    const sheet = ss.getSheets()[0];
    const data = sheet.getDataRange().getValues();
    const headerRow = data[0];
    let emailIdx = headerRow.indexOf('メールアドレス');
    if (emailIdx === -1) emailIdx = headerRow.indexOf('Email');
    if (emailIdx === -1) emailIdx = headerRow.indexOf('e-mail');
    
    if (emailIdx === -1) return [];
    return data.slice(1).map(r => r[emailIdx]).filter(String);
  } catch (e) {
    let msg = e.message;
    if (msg.includes('You do not have permission')) msg = '権限がありません(アクセス拒否)';
    else if (msg.includes('Service not found') || msg.includes('is missing')) msg = 'ファイルが見つかりません(URL不正)';
    throw new Error(`回答シート読込失敗: ${msg}`);
  }
}

function isValidUrl_(string) {
  try { return string.indexOf('http') === 0; } catch (_) { return false; }
}

function getTriggerUiHtml_() {
  return `
    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
        <style>
          body { font-family: sans-serif; padding: 10px; font-size: 14px; color: #333; }
          .form-group { margin-bottom: 15px; }
          label { display: block; margin-bottom: 5px; font-weight: bold; font-size: 13px; }
          select, button { width: 100%; padding: 8px; box-sizing: border-box; margin-top: 4px; border: 1px solid #ccc; border-radius: 4px; }
          button { background-color: #4a86e8; color: white; border: none; cursor: pointer; font-weight: bold; margin-top: 20px; padding: 10px; }
          button:hover { background-color: #3c78d8; }
          .note { font-size: 11px; color: #666; margin-top: 5px; line-height: 1.4; }
        </style>
      </head>
      <body>
        <!-- 頻度選択 -->
        <div class="form-group">
          <label>頻度</label>
          <select id="freq" onchange="updateUi()">
            <option value="DAILY">毎日</option>
            <option value="WEEKDAY">平日 (月〜金)</option>
            <option value="WEEKLY">毎週</option>
            <option value="HOURLY">◯時間ごと</option>
          </select>
        </div>

        <!-- 曜日 (毎週の場合のみ) -->
        <div class="form-group" id="day-group" style="display:none;">
          <label>曜日</label>
          <select id="day">
            <option value="MONDAY">月曜日</option>
            <option value="TUESDAY">火曜日</option>
            <option value="WEDNESDAY">水曜日</option>
            <option value="THURSDAY">木曜日</option>
            <option value="FRIDAY">金曜日</option>
          </select>
        </div>

        <!-- 時間指定 (時間ごと以外の場合) -->
        <div class="form-group" id="hour-group">
          <label>実行時間 (時)</label>
          <select id="hour">
            ${[...Array(24)].map((_, i) => `<option value="${i}" ${i===9?'selected':''}>${i}:00 〜 ${i}:59</option>`).join('')}
          </select>
          <div class="note">※Googleの仕様上、指定時間の0分〜59分の間のどこかで実行されます。</div>
        </div>

        <!-- 間隔指定 (時間ごとの場合のみ) -->
        <div class="form-group" id="interval-group" style="display:none;">
          <label>実行間隔</label>
          <select id="interval">
            <option value="1">1時間ごと</option>
            <option value="2">2時間ごと</option>
            <option value="4">4時間ごと</option>
            <option value="6">6時間ごと</option>
            <option value="8">8時間ごと</option>
            <option value="12">12時間ごと</option>
          </select>
          <div class="note">※設定完了時点からカウントが始まります。</div>
        </div>

        <button onclick="save()">設定を保存</button>

        <script>
          function updateUi() {
            const freq = document.getElementById('freq').value;
            
            // 表示制御
            document.getElementById('day-group').style.display = (freq === 'WEEKLY') ? 'block' : 'none';
            
            if (freq === 'HOURLY') {
              document.getElementById('hour-group').style.display = 'none';
              document.getElementById('interval-group').style.display = 'block';
            } else {
              document.getElementById('hour-group').style.display = 'block';
              document.getElementById('interval-group').style.display = 'none';
            }
          }
          
          function save() {
            const btn = document.querySelector('button');
            btn.disabled = true;
            btn.textContent = '設定中...';
            
            const config = {
              freq: document.getElementById('freq').value,
              day: document.getElementById('day').value,
              hour: document.getElementById('hour').value,
              interval: document.getElementById('interval').value
            };

            google.script.run
              .withSuccessHandler((msg) => {
                alert(msg);
                google.script.host.close();
              })
              .withFailureHandler((err) => {
                alert('エラー: ' + err.message);
                btn.disabled = false;
                btn.textContent = '設定を保存';
              })
              .setupTriggerFromClient(config);
          }
          
          // 初期化
          updateUi();
        </script>
      </body>
    </html>
  `;
}

function formatSheet_(sheet, headers, widths, color) {
  sheet.clear();
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setValues([headers]).setBackground(color).setFontColor('white').setFontWeight('bold').setHorizontalAlignment('center');
  widths.forEach((w, i) => sheet.setColumnWidth(i + 1, w));
  sheet.getRange(1, 1, 100, headers.length).setBorder(true, true, true, true, true, true, '#d9d9d9', SpreadsheetApp.BorderStyle.SOLID);
  sheet.setFrozenRows(1);
}
function showError_(msg) { console.error(msg); try { SpreadsheetApp.getUi().alert('エラー', msg, SpreadsheetApp.getUi().ButtonSet.OK); } catch(e) {} }
function getFormTitle_(url) { try { return SpreadsheetApp.openByUrl(url).getName().replace('（回答）', '').trim(); } catch (e) { return null; } }
function formatDate_() { return Utilities.formatDate(new Date(), 'Asia/Tokyo', 'MM/dd HH:mm'); }
function lookupUserByEmail_(email, token) {
  try {
    const res = UrlFetchApp.fetch(`https://slack.com/api/users.lookupByEmail?email=${encodeURIComponent(email)}`, { headers: { 'Authorization': `Bearer ${token}` }, muteHttpExceptions: true });
    const json = JSON.parse(res.getContentText());
    return json.ok ? json.user.id : null;
  } catch (e) { return null; }
}
function postMessageToSlack_(channel, text, token) {
  try {
    UrlFetchApp.fetch('https://slack.com/api/chat.postMessage', { method: 'post', contentType: 'application/json', headers: { 'Authorization': `Bearer ${token}` }, payload: JSON.stringify({ channel: channel, text: text }), muteHttpExceptions: true });
    return { ok: true };
  } catch (e) { return null; }
}

任意の名前をつけてコードを保存したらスプレッドシートを再読み込みしてください。

読み込みをするとメニューバーの右側に [🛠 リマインド管理メニュー] が出現します。

1-3. シートの設定

通知に必要な設定を進めていきます。

メニューバーの [🛠 リマインド管理メニュー] > [1. 初回セットアップ] をクリックします。

権限の承認画面が出ますので許可してください。

管理用のシート(管理コンソール）が自動作成されます。

続いてSlack Bot Token設定という画面が出るので、1-1でコピーしたSlackのトークンを貼り付けてください。

2.ツール実行手順

あたらしいGoogleフォームのリマインド通知を行いたい場合は以下設定を行ってください。

2-1. Googleフォームの用意

新規のGoogleフォームを用意してアンケート結果がスプレッドシートに出力するように設定してください。

2-2. アンケート情報の登録

「管理コンソール」シートを開いて新しい行に、以下情報を入力してください。

項目	説明・入力内容	備考
実行ON	チェックを入れる	リマインド対象になります
アンケートURL	回答してもらいたいGoogleフォームの回答画面URLを入力します	設定画面ではなく全員に周知する回答URLを登録してください。
回答用シートURL	フォームの回答が集まるスプレッドシートのURLを入力します。	シートは事前に作成しておいてください。
対象者リストシート名	通知対象者を設定するため任意のリスト名を入力します。	例: `2024春_社員総会` や `新人歓迎会リスト` など
締切日	締切日を入力します、 yyyy/mm/dd	締切日を超過すると、実行ONでもリマインドは行われません。