ユニット会計用

code.ts

const ADMIN_USER_ID = {製作者のLINE ID};
const CHROSTE_GROUP_ID = {ユニットのLINEグループID};
const CHANNEL_ACCESS_TOKEN = {LINE Messaging APIのアクセストークン};

// 毎週日曜日のトリガー
function weekly() {
  post_differs(CHROSTE_GROUP_ID);
}

// 毎夜のトリガー
function daily() {
  post_differs(ADMIN_USER_ID);
}

// Sheet書き換えのトリガー
function rewrite() {
}

//////////////////////////////////////////////////////////////////////////////////////////////////

// 費目別差額（残高）
function post_differs(user_id: string) {
  const chroste: Konto = new Konto();
  const message: string = 
    "装飾費残高: " + chroste.getDcolation().getDiffer() + "円\r\n" +
    "原材料費残高: " + chroste.getMaterial().getDiffer() + "円\r\n" +
    "総残高: " + chroste.getTotal().getDiffer() + "円";
  postChat(user_id, message);
}

//////////////////////////////////////////////////////////////////////////////////////////////////

const INCOME_EXPEND_SHEET_NAME = "収支一覧";
const REFUND_SHEET_NAME = "返金一覧";
const STUDENT_LIST_SHEET_NAME = "個人負担";

// 収支・差額の連想配列
type Amount = {
  income: Cell, // 収入
  expend: Cell, // 支出
  differ: Cell  // 差額(残高)
};

// コンテンツ共通の連想配列
type Content = {
  log_num: Cell,    // 記録番号
  date: Cell,       // 日付
  name: Cell,       // 費目(装飾費or原材料費)
  item_name: Cell,  // 品目名
  inner_num: Cell,  // お金を受け取った人の出席番号
  outer_num: Cell,  // お金を払った人の出席番号
  amount: Amount    // 収支・差額
};

// コンテンツ取り扱いクラス
class Finance {
  constructor(private content: Content) { }
  getIncome = (): Number => getCell(this.content.amount.income);  // 収入
  getExpend = (): Number => getCell(this.content.amount.expend);  // 支出
  getDiffer = (): Number => getCell(this.content.amount.differ);  // 差額(残高)
}

// コンテンツ取得クラス
class Konto {
  getDcolation = (): Finance => this.setContent(this.getInAndExTotal("Decolation"));  // 装飾費取得
  getMaterial = (): Finance => this.setContent(this.getInAndExTotal("Material"));     // 原材料費取得
  getTotal = (): Finance => this.setContent(this.getInAndExTotal("Total"));           // 合計収支取得
  // 収支番号からログ取得
  getLogByNumber = (number: number): Finance => {
    const common_row = 2 + number;
    const amount: Amount = {
      income: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 6),
      expend: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 7),
      differ: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 8),
    };
    const content: Content = {
      log_num: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 1),
      date: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 2),
      name: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 3),
      item_name: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 5),
      inner_num: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 10),
      outer_num: setCellPlace(INCOME_EXPEND_SHEET_NAME, common_row, 10),
      amount: amount
    };
    return this.setContent(content);
  }
  // 出席番号から返金記録取得
  getRefundLogByNumber = (number: number): Finance => {
    const common_row = 2 + number;
    const amount: Amount = {
      income: setCellPlaceEmpty(),
      expend: setCellPlace(REFUND_SHEET_NAME, common_row, 7),
      differ: setCellPlace(REFUND_SHEET_NAME, common_row, 8),
    };
    const content: Content = {
      log_num: setCellPlace(REFUND_SHEET_NAME, common_row, 1),
      date: setCellPlace(REFUND_SHEET_NAME, common_row, 2),
      name: setCellPlaceEmpty(),
      item_name: setCellPlaceEmpty(),
      inner_num: setCellPlace(REFUND_SHEET_NAME, common_row, 5),
      outer_num: setCellPlace(REFUND_SHEET_NAME, common_row, 3),
      amount: amount
    };
    return this.setContent(content);
  }
  // 収支小計取得
  private getInAndExTotal = (type: "Decolation" | "Material" | "Total"): Content => {
    const common_column: number = 14 + (
        (type == "Decolation") ? 0:
        (type == "Material") ? 1 :
        (type == "Total") ? 2:
        2
    );
    const amount: Amount = {
      income: setCellPlace(INCOME_EXPEND_SHEET_NAME, 3, common_column),
      expend: setCellPlace(INCOME_EXPEND_SHEET_NAME, 4, common_column),
      differ: setCellPlace(INCOME_EXPEND_SHEET_NAME, 5, common_column),
    };
    const content: Content = {
      log_num: setCellPlaceEmpty(),
      date: setCellPlace(INCOME_EXPEND_SHEET_NAME, 7, 14),
      name: setCellPlace(INCOME_EXPEND_SHEET_NAME, 1, common_column),
      item_name: setCellPlaceEmpty(),
      inner_num: setCellPlaceEmpty(),
      outer_num: setCellPlaceEmpty(),
      amount: amount
    };
    return content;
  };
  // 取り扱いクラス化
  private setContent = (content: Content): Finance => new Finance(content);
}

// Cell住所
type Cell = {
  sheet_name: string, // シート名
  row: number,        // 行
  column: number,     // 段
  existence: Boolean  // 存在するか;Falseの時は無条件でCellの値を0として返す
};

// Cell住所指定
const setCellPlace = (sheet_name: string, row: number, column: number): Cell =>
  ({ sheet_name: sheet_name, row: row, column: column, existence: true });

// Cell住所指定(existenceをfalseとして)
const setCellPlaceEmpty = (): Cell =>
  ({ sheet_name: "", row: 0, column: 0, existence: false });

// セルの値を取得
// Referenz(en) => https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
const getCell = (cell_place: Cell): any => {
  if (cell_place.existence) {
    const active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = active_spreadsheet.getSheetByName(cell_place.sheet_name);
    const cell = sheet.getRange(cell_place.row, cell_place.column);
    return cell.getValue();
  } else
    return 0;
};

// LINEにメッセージを送信
// Referenz(ja) => https://developers.line.biz/ja/reference/messaging-api/#send-push-message
function postChat(user_id: string, text: string) {
  const method: "post" | "get" | "delete" | "patch" | "put" = "post";
  const headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer " + CHANNEL_ACCESS_TOKEN,
  };
  const data = {
    "to": user_id,
    "messages": [{
      "type": "text",
      "text": text,
    }]
  };
  const options = {
    "method": method,
    "headers": headers,
    "payload": JSON.stringify(data),
  };
  return UrlFetchApp.fetch("https://api.line.me/v2/bot/message/push", options);
}