Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@norifumiishii

HRMOS勤怠by IEYASUにAPI連携するGAS作っていく #4

Posted at

非エンジニアのバックオフィスが頑張るシリーズです。
長時間労働者を抜き出します。

やりたいこと

  • 月次レポートのシートのなかで、残業時間36が45時間以上、80時間以上の人を抜き出して、長時間労働シートに転記する

プロンプト

さっそくChatGPTにお願いしていきます。

プロンプト
次に、長時間労働者を抜き出す longwork.gs をつくります。

対象月を聞く（yyyymm）　入力がない場合は現在日付の前月
特定条件のレコードを抽出して、「長時間労働一覧」シートに項目を転記する
条件は2つあります。

# 条件
月次レポート一覧シートの「残業時間」が45時間以上

# 転記内容
長時間種別（45時間以上と記入）、稼働月、姓、名、メールアドレス（ユーザー一覧で一致するものをユーザーIDをキーに）、総労働時間、法廷時間外労働時間、法廷外休日労働時間、残業時間36

# 条件
月次レポート一覧シートの「残業時間」が80時間以上

# 転記内容
長時間種別（80時間以上と記入）、稼働月、姓、名、メールアドレス（ユーザー一覧で一致するものをユーザーIDをキーに）、総労働時間、法廷時間外労働時間、法廷外休日労働時間、残業時間36

デバッグ（月次レポートもデバッグ）

はい、ということで、例によって1回目から動くようなコードは出力してくれませんのでデバッグしていきます。

月次レポートのほうの時間のフォーマットが[h]:mmだったり、[h]:mm:ss だったりで統一されていなくて、残業時間を抜き出す処理がうまくいきませんでしたので、元のmonthly_report.gs のほうを直してもらいました。
いろいろ小細工で直そうとしているのが伺えたので、直接指示します。

プロンプト
いやそうでなくて Range クラスの setNumberFormat メソッド　を使用して更新してください

転記はそのまま行い、該当するカラム全体に上記メソッドで[h]:mm形式で更新

また対象年月のリクエストURLが yyyy-mm形式である必要があるのに、yyyymm形式になっていたため、その点も修正してもらいました。
結果現在使用しているスクリプトが以下です。

monthly_report.gs
/**
 * トークンを取得する関数
 * スクリプトプロパティからAPIキーと会社URLを取得し、トークンを取得します。
 * 
 * @return {string} APIトークン
 */
function getToken() {
  const ps = PropertiesService.getScriptProperties();
  const apiKey = ps.getProperty('HRMOS_API_KEY');
  const endPoint = 'https://ieyasu.co/api/' + ps.getProperty('HRMOS_COMPANY_URL') + '/v1/authentication/token';
  const headerInfo = token => ({
    'Content-Type': 'application/json',
    'Authorization': 'Basic ' + token
  });
  const options = {
    method: 'get',
    headers: headerInfo(apiKey),
    "muteHttpExceptions": true
  };
  const response = UrlFetchApp.fetch(endPoint, options);
  const json = JSON.parse(response.getContentText());
  return json.token;
}

/**
 * ユーザーに取得対象月を問い合わせ、月次レポートを取得してスプレッドシートに転記する関数
 */
function fetchAndSaveMonthlyReports() {
  const ui = SpreadsheetApp.getUi();
  
  // 取得対象月を問い合わせる
  const month = getMonthFromUser();
  if (!month) {
    ui.alert('取得対象月が選択されていません。');
    return;
  }
  Logger.log(`対象月: ${month}`);

  const token = getToken(); // トークンを取得
  const ps = PropertiesService.getScriptProperties();
  const companyUrl = ps.getProperty('HRMOS_COMPANY_URL');
  const formattedMonth = `${month.slice(0, 4)}-${month.slice(4)}`; // "yyyy-mm"形式に変換
  const url = `https://ieyasu.co/api/${companyUrl}/v1/work_output_months/monthly/${formattedMonth}`;
  Logger.log(`APIリクエストURL: ${url}`);
  const headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer ' + token
  };

  const options = {
    method: 'get',
    headers: headers,
    'muteHttpExceptions': true
  };

  let reports = [];
  let page = 1;
  let totalPages = 1; // 初期値
  const limit = 100;
  let totalCount = 0;

  // ページネーションを使用して全月次レポートを取得
  do {
    Logger.log(`Fetching page ${page}`);
    const response = UrlFetchApp.fetch(`${url}?limit=${limit}&page=${page}`, options);
    const responseData = JSON.parse(response.getContentText());
    const responseHeaders = response.getAllHeaders();

    // レスポンスヘッダーから全件数と全ページ数を取得
    if (responseHeaders['x-total-count'] && responseHeaders['x-total-page']) {
      totalCount = parseInt(responseHeaders['x-total-count'], 10);
      totalPages = parseInt(responseHeaders['x-total-page'], 10);
    }

    // レスポンスデータが配列であることを確認
    if (Array.isArray(responseData)) {
      reports = reports.concat(responseData); // レポートデータを追加
      page++;
    } else {
      break;
    }
  } while (page <= totalPages); // 全ページを処理するまでループ

  const sheetName = '月次レポート一覧';
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName); // シートが存在しない場合は新規作成
  } else {
    sheet.clear(); // 既存のシートをクリア
  }

  const userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ユーザー一覧');
  const userData = userSheet.getDataRange().getValues();
  const userIndex = {};
  userData.forEach(row => {
    userIndex[row[0]] = { lastName: row[2], firstName: row[3] }; // ユーザーIDをキーにして姓名をマッピング
  });

  // 月次レポートデータをスプレッドシートに転記
  if (reports.length > 0) {
    const headers = [
      'ユーザーID', '社員番号', '姓', '名', '稼働月', '所定労働時間', '所定内労働時間', 
      '法定内時間外労働時間', '法定時間外労働時間', '法定外休日労働時間', '法定休日労働時間', 
      '深夜労働時間', '総労働時間', '実働時間', '遅刻時間', '早退時間', '所定不足時間', 
      '残業時間', '残業時間36', '所定日数', '出勤日数', '法定外休日出勤日数', 
      '法定休日出勤日数', '欠勤日数', '遅刻日数', '早退日数', '公休日数', '有給休暇日数', 
      '有給休暇残数', '有給休暇日数(時間休)', '時間有給休暇', '時間有給休暇残時間', 
      '子の看護休暇日数', '子の看護休暇残数', '子の看護休暇(時間休暇)', '子の看護休暇(時間休暇残時間)', 
      '子の看護休暇(休暇日数)', '介護休暇日数', '介護休暇残数', '介護休暇(時間休暇)', 
      '介護休暇(時間休暇残時間)', '介護休暇(休暇日数)', '経費', '作成日時', '更新日時', 
      '所定内労働時間(日中)', '所定内労働時間(深夜)', '法定内時間外労働時間(日中)', 
      '法定内時間外労働時間(深夜)', '法定時間外労働時間(日中)', '法定時間外労働時間(深夜)', 
      '法定外休日労働時間(日中)', '法定外休日労働時間(深夜)', '法定休日労働時間(日中)', 
      '法定休日労働時間(深夜)', '法定時間外+法定外休日労働時間(45時間以下)', 
      '法定時間外+法定外休日労働時間(45~60時間以下)', '法定時間外+法定外休日労働時間(60時間超過)'
    ]; // 日本語のカラム名
    sheet.appendRow(headers);

    reports.forEach((report, index) => {
      const user = userIndex[report.user_id] || { lastName: '', firstName: '' };
      const row = [
        report.user_id, // ユーザーID
        report.number, // 社員番号
        user.lastName, // 姓
        user.firstName, // 名
        report.month, // 稼働月
        report.prescribed_working_hours, // 所定労働時間
        report.hours_in_prescribed_working_hours, // 所定内労働時間
        report.hours_in_statutory_working_hours, // 法定内時間外労働時間
        report.excess_of_statutory_working_hours, // 法定時間外労働時間
        report.excess_of_statutory_working_hours_in_holidays, // 法定外休日労働時間
        report.working_hours_in_statutory_holidays, // 法定休日労働時間
        report.late_night_overtime_working_hours, // 深夜労働時間
        report.total_working_hours, // 総労働時間
        report.actual_working_hours, // 実働時間
        report.total_late_hours, // 遅刻時間
        report.total_leave_early_hours, // 早退時間
        report.lack_prescribed_work_time, // 所定不足時間
        report.over_work_time, // 残業時間
        report.over_work_time_36, // 残業時間36
        report.prescribed_working_days, // 所定日数
        report.working_days, // 出勤日数
        report.working_days_in_non_statutory_holidays, // 法定外休日出勤日数
        report.working_days_in_statutory_holidays, // 法定休日出勤日数
        report.absence_days, // 欠勤日数
        report.tardy_days, // 遅刻日数
        report.learve_early_days, // 早退日数
        report.public_holidays, // 公休日数
        report.paid_holidays, // 有給休暇日数
        report.remain_paid_holidays, // 有給休暇残数
        report.paid_holiday_with_time_paid, // 有給休暇日数(時間休)
        report.time_paid_holiday, // 時間有給休暇
        report.time_paid_holiday_zansu, // 時間有給休暇残時間
        report.kango_days, // 子の看護休暇日数
        report.kango_remain, // 子の看護休暇残数
        report.kango_time_paid_holiday, // 子の看護休暇(時間休暇)
        report.kango_time_paid_holiday_zansu, // 子の看護休暇(時間休暇残時間)
        report.kango_paid_holiday_with_time_paid, // 子の看護休暇(休暇日数)
        report.kaigo_days, // 介護休暇日数
        report.kaigo_remain, // 介護休暇残数
        report.kaigo_time_paid_holiday, // 介護休暇(時間休暇)
        report.kaigo_time_paid_holiday_zansu, // 介護休暇(時間休暇残時間)
        report.kaigo_paid_holiday_with_time_paid, // 介護休暇(休暇日数)
        report.expense, // 経費
        report.created_at, // 作成日時
        report.updated_at, // 更新日時
        report.daytime_prescribed_work_time, // 所定内労働時間(日中)
        report.midnight_prescribed_work_time, // 所定内労働時間(深夜)
        report.daytime_statutory_work_overtime, // 法定内時間外労働時間(日中)
        report.midnight_statutory_work_overtime, // 法定内時間外労働時間(深夜)
        report.daytime_out_statutory_work_time, // 法定時間外労働時間(日中)
        report.midnight_out_statutory_work_time, // 法定時間外労働時間(深夜)
        report.daytime_out_statutory_holiday_work_time, // 法定外休日労働時間(日中)
        report.midnight_out_statutory_holiday_work_time, // 法定外休日労働時間(深夜)
        report.daytime_statutory_holiday_work_time, // 法定休日労働時間(日中)
        report.midnight_statutory_holiday_work_time, // 法定休日労働時間(深夜)
        report.jikangai_under_45, // 法定時間外＋法定外休日労働時間（45時間以下）
        report.jikangai_45_to_60, // 法定時間外＋法定外休日労働時間（45~60時間以下）
        report.jikangai_over_60 // 法定時間外＋法定外休日労働時間（60時間超過）
      ];
      sheet.appendRow(row);
    });

    // 対象のカラムを [h]:mm 形式に設定
    const timeColumns = [
      6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54
    ];
    timeColumns.forEach(col => {
      sheet.getRange(2, col, sheet.getLastRow() - 1).setNumberFormat('[h]:mm');
    });
  }

  Logger.log(`全件数: ${totalCount}, 転記件数: ${reports.length}`);
}

/**
 * ユーザーに取得対象月を問い合わせる関数
 * カレンダーUIを表示し、デフォルトは現在日付の前月に設定
 * 
 * @return {string} 取得対象月 (YYYYMM形式)
 */
function getMonthFromUser() {
  const ui = SpreadsheetApp.getUi();
  const now = new Date();
  const lastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
  const defaultDate = Utilities.formatDate(lastMonth, Session.getScriptTimeZone(), 'yyyyMM');

  const response = ui.prompt('取得対象月を選択してください (YYYYMM形式)', defaultDate, ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() == ui.Button.OK) {
    const userInput = response.getResponseText();
    Logger.log(`ユーザー入力の対象月: ${userInput}`);
    return userInput || defaultDate; // ユーザー入力がない場合、デフォルト値を返す
  } else {
    Logger.log(`デフォルトの対象月: ${defaultDate}`);
    return defaultDate;
  }
}

さてこれで月次レポートの修正ができましたので、次は長時間労働者リストの抽出のデバッグを行います。
というか、月次レポートを修正したら動いてくれました。

longwork.gs
/**
 * 長時間労働者を抽出して「長時間労働一覧」シートに転記する関数
 */
function extractLongWorkRecords() {
  const ui = SpreadsheetApp.getUi();

  // 取得対象月を問い合わせる
  const month = getMonthFromUser();
  if (!month) {
    ui.alert('取得対象月が選択されていません。');
    return;
  }

  const sheetName = '月次レポート一覧';
  const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!reportSheet) {
    ui.alert(`${sheetName} シートが見つかりません。`);
    return;
  }

  const userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ユーザー一覧');
  const userData = userSheet.getDataRange().getValues();
  const userIndex = {};
  userData.forEach(row => {
    userIndex[row[0]] = { lastName: row[2], firstName: row[3], email: row[11] }; // ユーザーIDをキーにして姓名とメールアドレスをマッピング
  });

  const longWorkSheetName = '長時間労働一覧';
  let longWorkSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(longWorkSheetName);
  if (!longWorkSheet) {
    longWorkSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(longWorkSheetName); // シートが存在しない場合は新規作成
  } else {
    longWorkSheet.clear(); // 既存のシートをクリア
  }

  const headers = [
    '長時間種別', '稼働月', '姓', '名', 'メールアドレス', 
    '総労働時間', '法定時間外労働時間', '法定外休日労働時間', '残業時間'
  ];
  longWorkSheet.appendRow(headers);

  const data = reportSheet.getDataRange().getDisplayValues(); // 文字列として値を取得
  data.forEach((row, index) => {
    if (index === 0) return; // ヘッダー行をスキップ
    const userId = row[0];
    const user = userIndex[userId] || { lastName: '', firstName: '', email: '' };
    const overtime = convertToHours(row[17]); // 残業時間
    const totalWorkingHours = row[12];
    const statutoryOvertimeHours = row[8];
    const nonStatutoryHolidayOvertimeHours = row[9];

    Logger.log(`ユーザーID: ${userId}, 残業時間: ${row[17]}, 変換後の時間: ${overtime}`);

    if (!isNaN(overtime) && overtime >= 45) {
      const longWorkType = overtime >= 80 ? '80時間以上' : '45時間以上';
      const record = [
        longWorkType,
        row[4], // 稼働月
        user.lastName, // 姓
        user.firstName, // 名
        user.email, // メールアドレス
        totalWorkingHours, // 総労働時間
        statutoryOvertimeHours, // 法定時間外労働時間
        nonStatutoryHolidayOvertimeHours, // 法定外休日労働時間
        row[17] // 残業時間
      ];
      longWorkSheet.appendRow(record);
    }
  });

  Logger.log(`長時間労働者の抽出が完了しました。`);
}

/**
 * [h]:mm フォーマットの時間を10進法の時間に変換する関数
 * @param {string} timeStr - [h]:mm フォーマットの時間
 * @return {number} 変換後の時間（10進法）
 */
function convertToHours(timeStr) {
  if (typeof timeStr !== 'string') {
    Logger.log(`timeStr is not a string: ${timeStr}`);
    return 0;
  }
  const parts = timeStr.split(':');
  if (parts.length < 2) {
    Logger.log(`timeStr does not have 2 parts: ${timeStr}`);
    return 0;
  }
  const hours = parseFloat(parts[0]);
  const minutes = parseFloat(parts[1]) / 60;
  const result = hours + minutes;
  Logger.log(`timeStr: ${timeStr}, hours: ${hours}, minutes: ${minutes}, result: ${result}`);
  return result;
}

/**
 * ユーザーに取得対象月を問い合わせる関数
 * カレンダーUIを表示し、デフォルトは現在日付の前月に設定
 * 
 * @return {string} 取得対象月 (YYYYMM形式)
 */
function getMonthFromUser() {
  const ui = SpreadsheetApp.getUi();
  const now = new Date();
  const lastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
  const defaultDate = Utilities.formatDate(lastMonth, Session.getScriptTimeZone(), 'yyyyMM');

  const response = ui.prompt('取得対象月を選択してください (YYYYMM形式)', defaultDate, ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() == ui.Button.OK) {
    const userInput = response.getResponseText();
    Logger.log(`ユーザー入力の対象月: ${userInput}`);
    return userInput || defaultDate; // ユーザー入力がない場合、デフォルト値を返す
  } else {
    Logger.log(`デフォルトの対象月: ${defaultDate}`);
    return defaultDate;
  }
}

分を10進法に変換して加算するとか、要らないんですけど、ま、このままにしておきます。

まとめ

今回もChatGPTにどう指示するかがポイントでした。スプレッドシート内にある情報だけでやりくりできるはずなのに、APIで情報を取りに行こうとします。最新の情報を取得するという意味では悪くないのですが、無駄なAPIコールにも繋がるので、今回はスプレッドシートだけで抽出や転記を行いました。
メールアドレスはユーザーIDをキーにして取得できるはずですが、しっかり指示をしないと適当な値を返します。このへんうまくできないもんですかね。文章を返すのであれば「知らないこと、わからないことはそのように答えてください」とか指示するだけですが。この値の取得方法がわからないときは質問するように指示しておく、とかなのでしょうか。次の機会にやってみたいと思います。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?