Googleカレンダーに登録されている作業内容を集計するには

/************************************
 * 設定
 ************************************/

// 予定を取り出すカレンダーID
//  設定をしたいGoogleカレンダーを開いているGoogleアカウントと同じアカウントでSpreadSheatを開く必要があります。
var CAL_ID = Session.getActiveUser().getEmail();

// スプレッドシートID   Google Spread Sheat URLの /d/〜/ の部分
var SPREADSHEET_ID = '（編集するSpreadSheetのID）';

// シート名
var RAW_SHEET_NAME = 'events_raw';
var SUMMARY_SHEET_NAME = 'summary_by_client';
var INVALID_SHEET_NAME = '入力不備一覧';

// 集計メールの宛先
var TO_EMAIL = '（送信先Emailアドレス）';

// 会社別シート名の区切り文字（会社名_YYYY-MM）
var COMPANY_SHEET_SEP = '_';

/************************************
 * タイトル解析
 * 形式：［会社名］［作業内容］［金額］メモ
 ************************************/
function parseTitle_(title) {
  if (!title) return { client: '', work: '', amount: null, memo: '' };

  var normalized = title.replace(/［/g, '[').replace(/］/g, ']');

  var re = /^\s*\[([^\]]+)\]\s*\[([^\]]+)\]\s*\[([^\]]+)\]\s*(.*)$/;
  var m = normalized.match(re);
  if (m) {
    return {
      client: m[1].trim(),
      work: m[2].trim(),
      amount: parseAmount_(m[3]),
      memo: (m[4] || '').trim()
    };
  }

  // 旧形式救済
  var re2 = /^\s*\[([^\]]+)\]\s*\[([^\]]+)\]\s*(.*)$/;
  var m2 = normalized.match(re2);
  if (m2) {
    return { client: m2[1].trim(), work: m2[2].trim(), amount: null, memo: (m2[3] || '').trim() };
  }

  var re3 = /^\s*\[([^\]]+)\]\s*(.*)$/;
  var m3 = normalized.match(re3);
  if (m3) {
    return { client: m3[1].trim(), work: '', amount: null, memo: (m3[2] || '').trim() };
  }

  return { client: '', work: '', amount: null, memo: normalized.trim() };
}

function parseAmount_(s) {
  if (s === null || s === undefined) return null;
  var str = String(s).trim();
  if (!str) return null;

  str = str.replace(/[,\s]/g, '');
  str = str.replace(/[¥￥円]/g, '');
  str = str.replace(/[^0-9\-]/g, '');
  if (!str) return null;

  var n = Number(str);
  return isFinite(n) ? n : null;
}

/************************************
 * ヘッダー
 ************************************/
function setupRawHeader_(sheet) {
  var headers = [
    'Event ID',
    'Calendar ID',
    '日付',
    '開始時刻',
    '終了時刻',
    'タイトル（元）',
    '会社名',
    '作業内容',
    '金額',
    'メモ（タイトル）',
    'カレンダーの説明'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

function setupInvalidHeader_(sheet) {
  var headers = [
    'Event ID',
    'Calendar ID',
    '日付',
    '開始時刻',
    '終了時刻',
    'タイトル（元）',
    '会社名',
    '作業内容',
    '金額',
    'メモ（タイトル）',
    'カレンダーの説明',
    '不備内容'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

/************************************
 * 会社別（金額）集計
 ************************************/
function buildSummaryByClient_() {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var rawSheet = ss.getSheetByName(RAW_SHEET_NAME);
  var summarySheet = ss.getSheetByName(SUMMARY_SHEET_NAME);

  if (!summarySheet) summarySheet = ss.insertSheet(SUMMARY_SHEET_NAME);
  summarySheet.clear();

  if (!rawSheet) {
    summarySheet.getRange(1, 1).setValue('RAWシートがありません');
    return;
  }

  var lastRow = rawSheet.getLastRow();
  if (lastRow < 2) {
    summarySheet.getRange(1, 1).setValue('データがありません');
    return;
  }

  var data = rawSheet.getRange(2, 1, lastRow - 1, 11).getValues();
  var map = {}; // {会社名: { amount, count }}

  for (var i = 0; i < data.length; i++) {
    var client = data[i][6] || '(会社名未設定)'; // G
    var amount = data[i][8]; // I
    if (amount === null || amount === '' || isNaN(Number(amount))) amount = 0;

    if (!map[client]) map[client] = { amount: 0, count: 0 };
    map[client].amount += Number(amount);
    map[client].count += 1;
  }

  var rows = [['会社名', '件数', '合計金額']];
  Object.keys(map).forEach(function (k) {
    rows.push([k, map[k].count, map[k].amount]);
  });

  summarySheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  if (rows.length > 1) summarySheet.getRange(2, 3, rows.length - 1, 1).setNumberFormat('#,##0');
}

/************************************
 * 期間指定のイベント取り込み
 ************************************/
function exportEvents_(startDate, endDate) {
  var cal = CalendarApp.getCalendarById(CAL_ID);
  if (!cal) throw new Error('カレンダーが見つかりません: ' + CAL_ID);

  var events = cal.getEvents(startDate, endDate);
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);

  // RAW
  var rawSheet = ss.getSheetByName(RAW_SHEET_NAME);
  if (!rawSheet) rawSheet = ss.insertSheet(RAW_SHEET_NAME);
  setupRawHeader_(rawSheet);
  if (rawSheet.getLastRow() > 1) rawSheet.deleteRows(2, rawSheet.getLastRow() - 1);

  // 不備
  var invalidSheet = ss.getSheetByName(INVALID_SHEET_NAME);
  if (!invalidSheet) invalidSheet = ss.insertSheet(INVALID_SHEET_NAME);
  setupInvalidHeader_(invalidSheet);
  if (invalidSheet.getLastRow() > 1) invalidSheet.deleteRows(2, invalidSheet.getLastRow() - 1);

  var tz = Session.getScriptTimeZone();
  var rawValues = [];
  var invalidValues = [];

  for (var i = 0; i < events.length; i++) {
    var e = events[i];
    var start = e.getStartTime();
    var end = e.getEndTime();

    var title = e.getTitle();
    var parsed = parseTitle_(title);

    var rawRow = [
      e.getId(),
      CAL_ID,
      Utilities.formatDate(start, tz, 'yyyy/MM/dd'),
      Utilities.formatDate(start, tz, 'HH:mm'),
      Utilities.formatDate(end, tz, 'HH:mm'),
      title,
      parsed.client,
      parsed.work,
      parsed.amount,
      parsed.memo,
      e.getDescription() || ''
    ];
    rawValues.push(rawRow);

    var reason = '';
    if (!parsed.client) reason += '［会社名］が未設定。';
    if (!parsed.work) reason += '［作業内容］が未設定。';
    if (parsed.amount === null) reason += '［金額］が未設定または数値化できません。';

    if (reason) {
      var invalidRow = rawRow.slice();
      invalidRow.push(reason);
      invalidValues.push(invalidRow);
    }
  }

  if (rawValues.length) rawSheet.getRange(2, 1, rawValues.length, rawValues[0].length).setValues(rawValues);
  if (invalidValues.length) invalidSheet.getRange(2, 1, invalidValues.length, invalidValues[0].length).setValues(invalidValues);

  buildSummaryByClient_();
}

/************************************
 * 当月/先月の取り込み
 ************************************/
function exportThisMonthEvents() {
  var today = new Date();
  var first = new Date(today.getFullYear(), today.getMonth(), 1);
  var nextFirst = new Date(today.getFullYear(), today.getMonth() + 1, 1);
  exportEvents_(first, nextFirst);
}

function exportLastMonthEvents() {
  var today = new Date();
  var firstThis = new Date(today.getFullYear(), today.getMonth(), 1);
  var firstLast = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  exportEvents_(firstLast, firstThis);
}

/************************************
 * 会社別シート名：会社名_YYYY-MM
 ************************************/
function getCompanySheetName_(clientName, year, month) {
  var ym = year + '-' + ('0' + month).slice(-2);
  var safeClient = String(clientName).replace(/[\\\/\?\*\[\]]/g, '_').trim();
  return safeClient + COMPANY_SHEET_SEP + ym;
}

/************************************************
 * 会社名 + 年月 を指定して会社別シートを作成
 * 出力列: 会社名 / 作業内容 / 金額 / メモ
 * + 最下行に合計（金額）
 ************************************************/
function createCompanySheetForMonth_(clientName, year, month) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var rawSheet = ss.getSheetByName(RAW_SHEET_NAME);
  if (!rawSheet) throw new Error('RAWシートがありません: ' + RAW_SHEET_NAME);

  var lastRow = rawSheet.getLastRow();
  if (lastRow < 2) return;

  var data = rawSheet.getRange(2, 1, lastRow - 1, 11).getValues();

  var start = new Date(year, month - 1, 1);
  var end = new Date(year, month, 1);

  var rows = [];
  for (var i = 0; i < data.length; i++) {
    var r = data[i];
    var client = r[6];
    if (client !== clientName) continue;

    var dateStr = r[2];
    if (!dateStr) continue;

    var d;
    if (dateStr instanceof Date) {
      d = new Date(dateStr.getFullYear(), dateStr.getMonth(), dateStr.getDate());
    } else {
      var parts = String(dateStr).split(/[\/\-\.]/);
      if (parts.length < 3) continue;
      d = new Date(Number(parts[0]), Number(parts[1]) - 1, Number(parts[2]));
    }
    if (d < start || d >= end) continue;

    rows.push([clientName, r[7], r[8], r[9]]);
  }

  if (!rows.length) return;

  // ★シート名は「会社名_YYYY-MM」
  var sheetName = getCompanySheetName_(clientName, year, month);

  // 同名があれば「上書き」したいので削除→再作成（当月再集計に強い）
  var existing = ss.getSheetByName(sheetName);
  if (existing) ss.deleteSheet(existing);
  var sheet = ss.insertSheet(sheetName);

  var header = ['会社名', '作業内容', '金額', 'メモ'];
  sheet.getRange(1, 1, 1, 4).setValues([header]);
  sheet.getRange(2, 1, rows.length, 4).setValues(rows);

  sheet.getRange(2, 3, rows.length, 1).setNumberFormat('#,##0');

  var lastDataRow = rows.length + 1;
  var totalRow = lastDataRow + 1;

  sheet.getRange(totalRow, 2).setValue('合計');
  sheet.getRange(totalRow, 3).setFormula('=SUM(C2:C' + lastDataRow + ')').setNumberFormat('#,##0');

  sheet.setColumnWidth(1, 160);
  sheet.setColumnWidth(2, 220);
  sheet.setColumnWidth(3, 110);
  sheet.setColumnWidth(4, 360);

  sheet.getRange(2, 2, rows.length, 2).setWrap(true);
  sheet.getRange(1, 1, lastDataRow, 4).setBorder(true, true, true, true, true, true);
  sheet.getRange(1, 1, 1, 4).setFontWeight('bold').setHorizontalAlignment('center');

  sheet.getRange(totalRow, 2).setHorizontalAlignment('right');
  sheet.getRange(totalRow, 3).setHorizontalAlignment('right');
}

/************************************************
 * 指定年月の会社別シートを全社分まとめて作成
 ************************************************/
function createAllCompanySheetsForMonth_(year, month) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var rawSheet = ss.getSheetByName(RAW_SHEET_NAME);
  if (!rawSheet) throw new Error('RAWシートがありません: ' + RAW_SHEET_NAME);

  var lastRow = rawSheet.getLastRow();
  if (lastRow < 2) return [];

  var data = rawSheet.getRange(2, 1, lastRow - 1, 11).getValues();

  var clientsSet = {};
  for (var i = 0; i < data.length; i++) {
    var client = data[i][6];
    if (client) clientsSet[client] = true;
  }

  var clients = Object.keys(clientsSet);
  var created = [];

  for (var j = 0; j < clients.length; j++) {
    createCompanySheetForMonth_(clients[j], year, month);
    created.push(getCompanySheetName_(clients[j], year, month));
  }

  return created; // 作成したシート名一覧
}

/************************************************
 * 月末に当月分を集計するバッチ（手動実行も可）
 * 1) 当月分をevents_rawへ取り込み
 * 2) 当月分の会社別シートを全社分生成（会社名_YYYY-MM）
 * 3) Excelをメール添付（任意）
 ************************************************/
function monthlyBatchForThisMonth() {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);

  // 当月の年月
  var today = new Date();
  var year = today.getFullYear();
  var month = today.getMonth() + 1;

  // 1) 当月取り込み
  exportThisMonthEvents();

  // 2) 当月の会社別シート全社生成
  var createdSheets = createAllCompanySheetsForMonth_(year, month);

  SpreadsheetApp.flush();

  var ymLabel = year + '年' + ('0' + month).slice(-2) + '月';
  var url = ss.getUrl();

  var subject = '【当月分カレンダー作業ログ（金額）】' + ymLabel;
  var body = '';
  body += ymLabel + 'のカレンダー作業ログ（金額）を集計しました。\n\n';
  body += 'スプレッドシート：\n' + url + '\n\n';
  body += '▼会社別シート一覧（シート名＝会社名_YYYY-MM）\n';
  if (!createdSheets.length) {
    body += '　当月分のデータがありませんでした。\n';
  } else {
    createdSheets.forEach(function (name) { body += '　・' + name + '\n'; });
  }
  body += '\n';
  body += '※「入力不備一覧」シートに、［会社名］［作業内容］［金額］の入力ルールに合っていない予定が出力されています。\n';

  // Excel添付（必要なければ、このブロックを丸ごと削除してもOK）
  var exportUrl = 'https://docs.google.com/spreadsheets/d/' + SPREADSHEET_ID + '/export?format=xlsx';
  var params = {
    method: 'get',
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(exportUrl, params);
  var blob = response.getBlob().setName('当月作業ログ_' + ymLabel + '.xlsx');

  MailApp.sendEmail({
    to: TO_EMAIL,
    subject: subject,
    body: body,
    attachments: [blob]
  });
}

/************************************************
 * 月末判定：今日が月末なら当月バッチを実行
 * （日次トリガーで呼ぶ想定）
 ************************************************/
function runIfMonthEnd() {
  var today = new Date();
  var tomorrow = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1);

  // 明日が1日＝今日が月末
  if (tomorrow.getDate() === 1) {
    monthlyBatchForThisMonth();
  }
}

/************************************************
 * トリガー自動設定（1回だけ手動実行）
 * 毎日21:00に runIfMonthEnd を実行
 ************************************************/
function installMonthEndTrigger() {
  // 既存トリガー整理（重複防止）
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function (t) {
    if (t.getHandlerFunction() === 'runIfMonthEnd') {
      ScriptApp.deleteTrigger(t);
    }
  });

  ScriptApp.newTrigger('runIfMonthEnd')
    .timeBased()
    .everyDays(1)
    .atHour(21) // 21時（お好みで変更）
    .create();
}

/************************************************
 * メニュー（手動実行用）
 ************************************************/
function menuCreateCompanySheetsForYM() {
  var ui = SpreadsheetApp.getUi();

  var ymResp = ui.prompt('対象年月', '例：2025-12 のように入力してください', ui.ButtonSet.OK_CANCEL);
  if (ymResp.getSelectedButton() !== ui.Button.OK) return;

  var parts = ymResp.getResponseText().trim().split(/[-\/]/);
  var year = parseInt(parts[0], 10);
  var month = parseInt(parts[1], 10);

  // 指定月を取り込み→会社別全社生成
  var first = new Date(year, month - 1, 1);
  var nextFirst = new Date(year, month, 1);
  exportEvents_(first, nextFirst);

  createAllCompanySheetsForMonth_(year, month);
  SpreadsheetApp.flush();

  ui.alert('完了', '会社別シート（会社名_YYYY-MM）を作成しました。', ui.ButtonSet.OK);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('カレンダー連携')
    .addItem('当月分を取り込み', 'exportThisMonthEvents')
    .addItem('先月分を取り込み', 'exportLastMonthEvents')
    .addSeparator()
    .addItem('当月バッチ実行（当月取り込み→会社別全社→メール）', 'monthlyBatchForThisMonth')
    .addItem('指定年月で会社別全社シート作成（YYYY-MM）', 'menuCreateCompanySheetsForYM')
    .addSeparator()
    .addItem('月末自動集計トリガーを設定（1回だけ）', 'installMonthEndTrigger')
    .addToUi();
}