payslip

all_json.gs

function extractAll() {
  // 情報を書き出すスプレッドシートのファイル名を設定
  const FILENAME = "プライベートテスト2"

  // jsonファイルをGoogleドライブにアップロードしてテスト
  const jsonFile = DriveApp.getFileById('1CfwuzEQTKZA6616ZbfGcxxfkTKumQn3m');

  // スプレッドシートを新規作成
  const ss = SpreadsheetApp.create(FILENAME);

  // jsonオブジェクトとして読み込み
  const jsonString = jsonFile.getBlob().getDataAsString('utf8');
  const json = JSON.parse(jsonString);


  // 認識された文字列
  const text = json["text"];

  // 認識された文字列をスプレッドシートに書き出し
  textSheet = ss.getSheetByName('シート1').setName('テキスト');
  textSheet.getRange("A1").setValue(text);

  // キーバリューペアをスプレッドシートに書き出し
  keyvalueSheet = ss.insertSheet('キーバリュー');
  const keyvalueResult = [];
  const forms = json["pages"][0]["formFields"];
  for (form of forms) {
    keyvalueResult.push([form["fieldName"]["textAnchor"]["content"], form["fieldValue"]["textAnchor"]["content"]]);
  }
  keyvalueSheet.getRange(1, 1, keyvalueResult.length, 2).setValues(keyvalueResult);

  // エンティティーをスプレッドシートに書き出し
  entitySheet = ss.insertSheet('エンティティー');
  const entityResult = [];
  const entities = json["entities"][0]["properties"];
  for (entity of entities) {
    entityResult.push([entity["type"], entity["mentionText"]]);
  }
  entitySheet.getRange(1, 1, entityResult.length, 2).setValues(entityResult);

  // テーブルをスプレッドシートに書き出し
  const tables = json["pages"][0]["tables"];
  tables.forEach((table, index) => {
    let tableSheet = ss.insertSheet('テーブル' + index);
    let headerRows = table["headerRows"];
    let bodyRows = table["bodyRows"];
    for (row of headerRows) {
      let rowArray = [];
      let cells = row["cells"];
      if (cells) {
        for (cell of cells) {
          let cellText = '';
          let segments = cell["layout"]["textAnchor"]["textSegments"];
          try {
            for (segment of segments) {
              let sub = text.substr(segment["startIndex"], segment["endIndex"] - segment["startIndex"]);
              cellText += sub;
            }
            rowArray.push(cellText);
          }
          catch(e) {
            Logger.log(e);
          }
        }
      }
      if (rowArray.length > 0) {
        tableSheet.appendRow(rowArray);
      }
    }
    if (bodyRows) {
      for (row of bodyRows) {
        let rowArray = [];
        let cells = row["cells"];
        if (cells) {
          for (cell of cells) {
            let cellText = '';
            let segments = cell["layout"]["textAnchor"]["textSegments"];
            try {
              for (segment of segments) {
                let sub = text.substr(segment["startIndex"], segment["endIndex"] - segment["startIndex"]);
                cellText += sub;
              }
              rowArray.push(cellText);
            }
            catch(e) {
              Logger.log(e);
            }
          }
        }
        if (rowArray.length > 0) {
          tableSheet.appendRow(rowArray);
        }
      }
    }
  });
}

appsscript.json

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://mail.google.com/",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/cloud-platform",
    "https://www.googleapis.com/auth/calendar"
  ]
}

calendar.gs

function sumCalendar() {
  // その月の合計労働時間
  let workHours = 0;

  // 4時間と6時間の予定タイトル文字列を設定
  const fourHours = ['よ１', 'よ２', 'よ３'];
  const sixHours = ['よ１＋', 'よ３＋', 'よ１番', 'よ５番'];

  // スクリプトプロパティからカレンダーIDを取得
  const properties = PropertiesService.getScriptProperties();
  const calendarId = properties.getProperty('calendarId');

  // IDからカレンダーを取得
  const calendar = CalendarApp.getCalendarById(calendarId);

  // 現在の日時から取得対象の起点と終点を設定
  const now = new Date();
  const year = now.getFullYear();
  const month = now.getMonth();
  const date1 = new Date(year, month - 1, 1);
  const date2 = new Date(year, month, 1);

  // 予定を取得する
  const events = calendar.getEvents(date1, date2);
  for (event of events) {
    let title = event.getTitle();
    if (fourHours.includes(title)) {
      workHours += 4;
    }
    else if (sixHours.includes(title)) {
      workHours += 6;
    }
  }

  return workHours;
}

healthcheck.gs

function checkHealth() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("URLリスト");
  const rows = sheet.getRange("A:B").getValues();
  for (let row of rows) {
    try {
      let response = UrlFetchApp.fetch(row[1]);
      if (response.getResponseCode() != 200) {
        Logger.log('ステータスコードのエラーです');
        GmailApp.sendEmail(
          '[email protected]',
          'ステータスコードのエラー',
          `${row}ステータスコードのエラーです`,
        );
      }
      else if (!response.getContentText().includes(row[0])) {
        Logger.log('指定された文字列が含まれていません');
        GmailApp.sendEmail(
          '[email protected]',
          '文字列のエラー',
          `${row}文字列のエラーです`,
        );
      }
    } catch(e) {
      Logger.log('Error:');
      Logger.log(e);
      GmailApp.sendEmail(
        '[email protected]',
        'fetchのエラー',
        `${row}fetchのエラーです`,
      );
    }
  }
  const historySheet = ss.getSheetByName("実行履歴");
  historySheet.appendRow([new Date(), 'ヘルスチェックを実行しました']);
}

json.gs

function extractPaySlip(jsonFileId) {
  // 返すデータの初期化
  const result = {};

  // 渡されたjsonファイルを読み込む
  const jsonFile = DriveApp.getFileById(jsonFileId);
  const jsonString = jsonFile.getBlob().getDataAsString('utf8');
  const json = JSON.parse(jsonString);

  // テキストとテーブルを取得
  const text = json["text"];
  const tables = json["pages"][0]["tables"];

  // 勤務時間
  const row1 = tables[0]["bodyRows"][0];
  const row2 = tables[0]["bodyRows"][7];
  result["時間"] = getResultFromTable(text, row1, [2])[0];
  result["単価"] = getResultFromTable(text, row1, [3])[0];
  result["支払額"] = getResultFromTable(text, row1, [4])[0];
  result["所得税"] = getResultFromTable(text, row2, [6])[0];
  result["給与総額"] = getResultFromTable(text, row2, [7])[0];

  return result;
}

json_handling.gs

/**
 * JSONファイルのテーブルから情報を取得する
 *
 * @param {string} text - JSONから取得したテキスト全体
 * @param {object} row - JSONから取得したテーブルの行
 * @param {array} columnNumbers - テーブルから取得対象にする列番号の配列
 * @return {array} resultArray - 取得した情報を並べた配列
 */
function getResultFromTable(text, row, columnNumbers) {
  const resultArray = [];
  const cells = columnNumbers.map((x) => row["cells"][x]);
  // 各セルからテキストを取得
  for (cell of cells) {
    let cellText = '';
    let segments = cell["layout"]["textAnchor"]["textSegments"];
    if (!segments) {
      return false;
    }
    for (segment of segments) {
      let sub = text.substr(segment["startIndex"], segment["endIndex"] - segment["startIndex"]);
      cellText += sub;
    }
    resultArray.push(cellText);
  }
  return resultArray;
}

mail.gs

function getSalaryMail() {
  // 抽出条件指定
  const query = 'from:[email protected] subject:給与明細';
  const firstThread = GmailApp.search(query, 0, 8)[7];
  const firstMessage = firstThread.getMessages()[0];

  // 添付のPDFファイルをコピー
  let file = DriveApp.createFile(firstMessage.getAttachments()[0].copyBlob());
  let pdfFileId = file.getId();

  // Document AIを使ってOCR
  const jsonFileId = recognize(pdfFileId);
  
  //const jsonFileId = '1CfwuzEQTKZA6616ZbfGcxxfkTKumQn3m';

  // jsonファイルを処理
  const dataFromJson = extractPaySlip(jsonFileId);

  // カレンダーから情報取得
  const dataFromCalendar = sumCalendar();

  // 数字のチェック
  let wrongFlag = false;
  const hours = Number(dataFromJson["時間"].replace(/,/g, ""));
  const unitPrice = Number(dataFromJson["単価"].replace(/,/g, ""));
  const pay = Number(dataFromJson["支払額"].replace(/,/g, ""));
  const tax = Number(dataFromJson["所得税"].replace(/,/g, ""));
  const total = Number(dataFromJson["給与総額"].replace(/,/g, ""));
  if (hours * unitPrice !== pay) {
    wrongFlag = true;
  }
  if (Math.floor(pay * 0.03063) !== tax) {
    wrongFlag = true;
  }
  if (pay - tax !== total) {
    wrongFlag = true;
  }
  

  // メール送信
  const mailTo = '[email protected]';
  const subject = '給与明細とカレンダーの照合結果';
  let body = '';
  if (wrongFlag) {
    body += '間違いがあるかもしれません\n\n';
  }
  body += 'カレンダーの合計時間：' + dataFromCalendar + '\n\n';
  body += JSON.stringify(dataFromJson, null, '\t');
  GmailApp.createDraft(mailTo, subject, body);

  // スプレッドシートに記録
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const historySheet = ss.getSheetByName("実行履歴");
  historySheet.appendRow([new Date(), '給与明細確認を実行しました']);
}

ocr.gs

/**
 * Document AIを使ってPDFファイルをスキャンする
 *
 * @param {string} pdfFileId - スキャン対象PDFファイルのID
 * @return {string} jsonFileId - OCR結果のjsonファイルのID
 */
function recognize(pdfFileId) {
  // スクリプトプロパティからdocument aiのエンドポイントとjsonファイルを保存するフォルダを取得
  const properties = PropertiesService.getScriptProperties();
  const url = properties.getProperty('documentaiEndpoint');
  const jsonFolderId = properties.getProperty('jsonFolderId');

  // 認証トークンを取得
  let token = ScriptApp.getOAuthToken();

  // PDFファイルを取得してエンコード
  const blob = DriveApp.getFileById(pdfFileId).getBlob()
  const encoded = Utilities.base64Encode(blob.getBytes());
  
  // パラメータの設定
  const param = {
    "rawDocument": {
      "mimeType":'application/pdf',
      "content":  encoded
    }
  };
  
  // オプションの設定
  const options = {};
  options.headers = {'Authorization':"Bearer " + token, 'Content-Type': "application/json; charset=utf-8"};
  options.payload = JSON.stringify(param);
  options.method = "POST";
  options.muteHttpExceptions=true;

  // Document AI APIに接続して得たjsonファイルを保存
  const response = UrlFetchApp.fetch(url, options);
  const jsonResponse = JSON.parse(response);
  const jsonString = JSON.stringify(jsonResponse["document"]);
  const pdfFileName = DriveApp.getFileById(pdfFileId).getName();
  const jsonFileName = pdfFileName.split('.').slice(0, -1).join('.') + '.json';
  const jsonBlob = Utilities.newBlob(jsonString, 'application/json', jsonFileName);
  const jsonFolder = DriveApp.getFolderById(jsonFolderId);
  const jsonFile = jsonFolder.createFile(jsonBlob);
  return jsonFile.getId();
}