勤怠データをGoogle Sheetに貼り付け、スクリプトを実行すると、自動で対象者とのSlackチャンネルが作られるフロー

2024年6月6日 20:39

コードも読めないど素人ですが以下の手順でslackチャンネルを自動生成するフローを作成しました。

対象者とのグループを作成するためだけに毎月1時間半ほどかかっていましたが、この自動化により作業時間を軽減することが出来ました。

1. 勤怠データをダウンロードしGoogle Sheetに貼り付ける

シートに名前をつけます。このシートの名前は後に作るスクリプトの中に貼り付けます。
F列以降には以下の関数を貼り付けます

F2 = UNIQUE($A$2:A)
A列のスタッフコードを重複なしに並べる関数です

G2 = JOIN(", ", FILTER(D:D, $A:$A=$F2))
D列の日時(エラー発生日)を対象者1人につき1セルにまとめる関数です。G2の関数をG3から下のセルにも全て貼り付けます

H2 = JOIN(", ", FILTER(E:E, $A:$A=$F2))
E列の内容(エラー内容)を対象者1人につき1セルにまとめる関数です。H2の関数をH3から下のセルにも全て貼り付けます

※I 列とJ列には対象者とその上長のメールアドレスを関数で自動反映しています。(別タブに従業員マスタシートをimportrangeして、F列のスタッフコードを参照してxlookup関数でメールアドレスを引っ張ってきています。)

2. Google Apps Scriptを作る

Apps Scriptを開き以下のスクリプトを貼り付けます

function myFunction() {
  const B_BATCH_SIZE = 10; // 一度に送信する通知の数を減らす
  const B_RETRY_LIMIT = 5; // 再試行の回数
  const B_RETRY_WAIT_TIME = 60000; // 再試行までの待機時間（ミリ秒）
  let sentRows = []; // 送信済みの行を保持するための配列

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobcan Error Action Required');
  const lastRow = sheet.getLastRow();

  // データが存在するか確認
  if (lastRow <= 1) {
    Logger.log('データが存在しません');
    return;
  }

  const data = sheet.getRange(2, 7, lastRow - 1, 4).getValues();

  for (let i = 0; i < data.length; i += B_BATCH_SIZE) {
    const batch = data.slice(i, i + B_BATCH_SIZE);
    
    batch.forEach((row, index) => {
      const rowIndex = i + index + 2; // データ範囲の行番号を算出

      // 既に送信済みであればスキップ
      if (sentRows.includes(rowIndex)) return;

      // G列の日付文字列を分割し、配列として取得
      const dates = row[0].split(',').map(dateStr => {
        return Utilities.formatDate(new Date(dateStr.trim()), Session.getScriptTimeZone(), 'yyyy/MM/dd');
      }).join(', ');

      const params = {
        "email": row[2],   // I列: メールアドレス
        "Date": dates,    // D列: エラー日時
        "Error": row[1],    // E列: エラー内容
        "email2": row[3],   // G列: 上長メールアドレス
      };

      try {
        emitSlackWorkflowB(params, rowIndex);
        
        // 送信に成功した場合は送信済みの行として記録
        sentRows.push(rowIndex);
      } catch (e) {
        Logger.log(`Error sending notification for row ${rowIndex}: ${e.message}`);
      }
    });

    // バッチ送信後にスクリプトを一時停止
    Utilities.sleep(10000); // 10秒間一時停止（必要に応じて調整）
  }
}

function emitSlackWorkflowB(params, rowIndex) {
  const B_RETRY_LIMIT = 5; // 再試行の回数
  const B_RETRY_WAIT_TIME = 60000; // 再試行までの待機時間（ミリ秒）
  const url = 'ウェブリクエストURLを貼り付け';
  const options = {
    method: 'POST',
    headers: { 'Content-type': 'application/json' },
    payload: JSON.stringify(params),
    muteHttpExceptions: true // エラーメッセージを取得するために追加
  };

  let retryCount = 0;
  let success = false;

  while (!success && retryCount < B_RETRY_LIMIT) {
    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();

    if (responseCode === 200) {
      success = true;
    } else if (responseCode === 429) {
      // レート制限に引っかかった場合、待機して再試行
      Logger.log(`Rate limited for row ${rowIndex}. Retrying... (${retryCount + 1}/${B_RETRY_LIMIT})`);
      retryCount++;
      Utilities.sleep(B_RETRY_WAIT_TIME); // 再試行までの待機時間
    } else {
      throw new Error(`Request failed with code ${responseCode}`);
    }
  }

  if (!success) {
    throw new Error(`Failed to send notification for row ${rowIndex} after ${B_RETRY_LIMIT} attempts.`);
  }
}

6行目のJobcan Error Action RequiredはGoogle Sheetのタブの名前を同じでなければなりません。

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobcan Error Action Required');

下から27行目のウェブリクエストURLを貼り付け部分には後ほどslack上で取得するURLを貼り付けます

const url = 'ウェブリクエストURLを貼り付け';

3. Slackでワークフローを作成する

チャンネルを作成します。｛｝をクリックすると先ほど設定した変数を挿入できます。
"｛｝email" はslackの対象者の名前になります。
"｛｝email2"はその対象者の上長（この場合は勤怠の承認者）です。
チャンネルマネージャーに人事労務メンバーをデフォルトで追加しておくこともできます。

次に、「チャンネルへメッセージを送付する」を選択しチャンネル選択とメッセージ内容を記入します。「変数を挿入する」をクリックし、先ほど設定した変数を挿入します。以下、サンプルの文面です。

{}email 打刻エラーがあります。修正してください。
Your jobcan error affects payroll. ▶︎https://id.jobcan.jp/users/sign_in

Date: {}Date
Error: {}Error

{}email2 部下が修正したら承認してください。
Please approve your subordinate's error.▶︎承認の方法はコチラ

"承認の方法はコチラ"には承認者向けのマニュアルリンクを挿入しています。

最後に、最初に作ったスクリプトに貼り付けるURLを取得します

4. スクリプトを保存し、実行する

上記でコピーしたURLを、スクリプト内下から27行目のウェブリクエストURLを貼り付け部分に貼り付けます。保存し、実行ボタンを押します。

エラーが出たり実行できない場合は、スクリプト全体をコピーしてChat GPTに貼り付けてエラーの原因を聞きます。

5. 完成

スクリプトを実行すると以下のように自動でチャンネルが立ち上がります。（チャンネル名の末尾に謎の数字が自動生成されてしまうのですが、原因不明です、、この数字を取る方法がわかる方いれば教えてくださいTT）

この記事が気に入ったらサポートをしてみませんか？