スプレッドシートに入れた還元系のキャンペーン情報を Google Apps Script でカレンダーに入れる

クレカとか, なんとか Pay とか, なんとかポイントみたいなやつでやってるキャンペーンの期間とか上限とかがゴチャゴチャで分かりにくいし覚えられる訳もない. とりあえず Google スプレッドシートに入れて管理してたけど, 一覧で見やすいだけで別に分かりやすくなる訳ではないことが判明しただけだった.

他のデータベースに入れて iCalendar を生成するサービスでも作ろうと前々から思ってたけど, よく考えたら Google Apps Script を使えば Google スプレッドシートの読み取りも Google カレンダーの登録も一発という事に気づいたのですぐに作った.

シートは前々から作ってたのをそのまま流用して, こんな感じにした.

• A 列の status が 登録/更新する の場合だけ実行するようにした.
• B 列のイベントの id が存在しない場合は新規作成, 存在する場合は更新という感じ.
• F 列か H 列が空だと終日で登録される.

ツール -> スクリプト エディタ からエディタを開いて, それっぽいコードを入れる. 動けば何でもいいやと思って勢いで書いたら読みにくいし, そのうち書き直したい.

コードは GitHub にも上げた. GitHub の方は TypeScript で書き直した後のコード. コピペで動けばいいって人は下に貼った TS で書き直す前のコードを使うといいと思う.

github.com

const EXECUTE_STATUS_VALUE = PropertiesService.getScriptProperties().getProperty('EXECUTE_STATUS_VALUE');
const ADDED_STATUS_VALUE = PropertiesService.getScriptProperties().getProperty('ADDED_STATUS_VALUE');
const CALENDAR_ID = PropertiesService.getScriptProperties().getProperty('CALENDAR_ID');
const SHEET_NAME = PropertiesService.getScriptProperties().getProperty('SHEET_NAME');

function addEventsToGoogleCalendar () {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

  for (let rowNumber = 2; rowNumber <= sheet.getLastRow(); rowNumber++) {
    const status = sheet.getRange(rowNumber, 1).getValue();

    if (status !== EXECUTE_STATUS_VALUE) {
      continue;
    }

    let columnNumber = 2;
    const id = sheet.getRange(rowNumber, columnNumber).getValue();
    const summary = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const target = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const eventStartDateValue = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const eventStartTime = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const eventEndDateValue = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const eventEndTime = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const baseDescription = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const limit = sheet.getRange(rowNumber, ++columnNumber).getValue();
    const reference = sheet.getRange(rowNumber, ++columnNumber).getValue();

    const title = buildTitle(summary, target);
    const description = buildDescription(baseDescription, limit, reference);
    const formattedStartDate = Utilities.formatDate(eventStartDateValue, 'Asia/Tokyo', 'yyyy/MM/dd');
    const formattedEndDate = Utilities.formatDate(eventEndDateValue, 'Asia/Tokyo', 'yyyy/MM/dd');

    const startDateTime = eventStartTime === '' ? new Date(formattedStartDate) : getStartDateTime(formattedStartDate, eventStartTime);
    const endDateTime = eventEndTime === '' ? getAllDayEventEndDateTime(formattedEndDate) : getEndDateTime(formattedEndDate, eventEndTime);
    const isAllDayEvent = eventStartTime === '' || eventEndTime === '';

    const calendarEvent = id === ''
      ? createNewCalendarEvent(title, startDateTime, endDateTime, description, isAllDayEvent)
      : updateCalendarEvent(id, title, startDateTime, endDateTime, description, isAllDayEvent);
    calendarEvent.setVisibility(CalendarApp.Visibility.PRIVATE);

    sheet.getRange(rowNumber, 1).setValue(ADDED_STATUS_VALUE);
    sheet.getRange(rowNumber, 2).setValue(calendarEvent.getId());
  }
}

function buildTitle (summary, target) {
  const trimSummary = summary.trim();
  const trimTarget = target.trim();

  return trimTarget === '' ? trimSummary : `${trimSummary}@${trimTarget}`;
}

function buildDescription (baseDescription, limit, reference) {
  let description = baseDescription;

  if (limit !== '') {
    description = `${description}\n還元上限 : ${limit}`;
  }

  if (reference !== '') {
    description = `${description}\nref : ${reference}`;
  }

  return description.trim();
}

function getStartDateTime (startDate, eventStartTime) {
  const hours = eventStartTime.getHours();
  const minutes = eventStartTime.getMinutes();
  const seconds = eventStartTime.getSeconds();

  return new Date(`${startDate} ${hours}:${minutes}:${seconds}`);
}

function getAllDayEventEndDateTime (endDate) {
  return new Date((new Date(endDate)).getTime() + (1000 * 60 * 60 * 24));
}

function getEndDateTime (endDate, eventEndTime) {
  const hours = eventEndTime.getHours();
  const minutes = eventEndTime.getMinutes();
  const seconds = eventEndTime.getSeconds();

  return new Date(`${endDate} ${hours}:${minutes}:${seconds}`);
}

function createNewCalendarEvent (title, startDateTime, endDateTime, description, isAllDayEvent) {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);

  if (isAllDayEvent) {
    return calendar.createAllDayEvent(title, startDateTime, endDateTime, { description });
  } else {
    return calendar.createEvent(title, startDateTime, endDateTime, { description });
  }
}

function updateCalendarEvent (id, title, startDateTime, endDateTime, description, isAllDayEvent) {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  const event = calendar.getEventById(id);

  if (isAllDayEvent) {
    event.setAllDayDates(startDateTime, endDateTime);
  } else {
    event.setTime(startDateTime, endDateTime);
  }

  return event.setTitle(title).setDescription(description);
}

ファイル -> プロジェクトのプロパティ からプロパティを設定する.

• EXECUTE_STATUS_VALUE
  • 実行対象のステータス
• ADDED_STATUS_VALUE
  • 実行完了後のステータス
• CALENDAR_ID
  • 登録するカレンダーの ID
  • カレンダーの設定と共有 の カレンダー ID を入れる
  • 乱数@group.calendar.google.com みたいなやつ
• SHEET_NAME
  • データの入ったシート名

設定してから addEventsToGoogleCalendar を実行するといい感じにカレンダーに入っていると思う.

---

sheet.getRange(rowNumber, ++columnNumber) で コード 1 行でシート 1 列ずつ動いて取得できるの便利っぽい.

あと久しぶりに GAS 書いたら, いつの間にか const とか let とかテンプレートリテラルも使えるようになっててよかった.

GitHub Actions で特定の OS の時のみ実行するスクリプトを切り替える

テスト時の必要なパッケージのセットアップ等で OS 毎に実行するスクリプトを切り替える必要があり, 色々と調べたので書き残します。

GitHub Actions では標準で Windows Server 2019, Ubuntu 18.04, 16.04, macOS Catalina 10.15 が使えます。 (2019/12/18 現在)

help.github.com

CI の際に, OS により実行する事が違う場合が多々ありますが, GitHub Actions では YAML ファイル内で簡単に分岐できます。

OS の切り替えに限らず, 特定の条件のみ実行する場合は jobs.<job_id>.if でコンテキストを使って分岐します。 変に説明するよりも見たほうが早いので, 例を貼り付けます。 以下は Linux (ubuntu-latest) の時は apt, macOS (macos-latest) の時は brew を使い, zsh をインストールする例です。

name: CI

on: [push]

jobs:
  build:
    runs-on: ${{ matrix.os }}
    strategy:
      matrix:
        os: [ubuntu-latest, macos-latest]
        node-version: [12.x]

    steps:
      - uses: actions/checkout@v1

      - name: Use the Node.js ${{ matrix.node-version }}
        uses: actions/setup-node@v1
        with:
          node-version: ${{ matrix.node-version }}

      # Linux の時のみ, apt で zsh をインストール
      - name: Install zsh on ubuntu-latest
        if: runner.os == 'Linux'
        run: |
            sudo apt update -y
            sudo apt install -y zsh

      # macOS の時のみ, HomeBrew で zsh をインストール
      - name: Install zsh on macos-latest
        if: runner.os == 'macOS'
        run: |
            brew update
            brew install zsh

runner.os で現在のジョブで実行されてる OS の取得が出来ます。 取得できる文字列はLinux, macOS, Windows です。

if で書く OS 名はシングルクォートで囲う必要があります(ダブルクォートだと Your workflow file was invalid: The pipeline is not valid., Unexpected symbol になります)。

分岐条件が複数ある場合は && で繋ぐだけです。

# os が macOS かつ, イベントが pull_request
if: runner.os == 'macOS' && github.event_name == 'pull_request'

Node.js のバージョン切り替えで使っている matrix.node-version や, ジョブの状態を取得する job.status などもコンテキストの一つですので if に使えます。

その他のコンテキストは GitHub ヘルプから参照できます。

help.github.com

Travis CI のようにジョブの情報が環境変数に入っていてシェルスクリプトの中で分岐するのは, シェルスクリプトにあまり慣れてない人(僕)が触るとまあまあ最悪なので, YAML ファイル内で簡単に分岐出来るのはありがたいですね。