Hiroto's diary

プログラミングとか色々

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

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

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

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

f:id:Hiroto-K:20200227012842p:plain

  • 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 列ずつ動いて取得できるの便利っぽい.

f:id:Hiroto-K:20200227123948p:plain

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

© 2015 hiroxto