More than 1 year has passed since last update.

Googleスプレッドシート & Apps Scriptで消耗品管理システムを作成し、Googleカレンダーで交換時期を通知しよう

Last updated at 2024-09-22Posted at 2024-09-22

はじめに

皆さんは、消耗品や調味料のストックが切れて困ったことはありませんか？私は頻繁にあります。買い置きのシャンプーが切れたり、調味料がなくなったり…。
これを解決するために、Googleスプレッドシート、Googleフォーム、そしてGoogleカレンダーを活用した自動化システムを構築しました。この記事では、その手順を詳しく説明します！

システム概要

このシステムは、以下の機能を提供します

QRコードを使ってGoogleフォームに交換日を記録
スプレッドシートにデータが自動的に保存され、補充間隔や予想交換日を計算
予想交換日が近づいたら、Googleカレンダーに自動で通知を追加

スプレッドシートの準備

物品リストシートの作成
- Googleスプレッドシートに「物品リスト」シートを作成します。
- 以下のように、物品名とカラム名を用意します。今回は「シャンプー」「リンス」「ボディソープ」「歯磨き粉」を使用します。
物品名最近の交換日補充間隔予想交換日

シャンプー

リンス

ボディソープ

歯磨き粉
物品名の入力
- 「物品名」列には、それぞれの消耗品を手動で入力します（シャンプー、リンス、ボディソープ、歯磨き粉）。
- 他の列（最近の交換日、補充間隔、予想交換日）は空白にしておきます。

以下のようになれば完了です。

Googleフォームの作成

Googleフォームの準備
- Googleフォームを開き、新しいフォームを作成します。
- 質問を設定します
  - 物品名（プルダウン形式）：シャンプー、リンス、ボディソープ、歯磨き粉を選択肢として設定します。
  - 交換日（日付入力）：消耗品が交換された日を記録するフィールドです。

以下のようになれば完了です。

フォームとスプレッドシートのリンク方法
- Googleフォームの「応答」タブに移動し、右上のGoogleシートのアイコンをクリックします。
- 「既存のスプレッドシートを選択」を選択すると、作成しているGoogleスプレッドシートが表示されます。
- 先ほどまで作成していたスプレッドシートを選択し、挿入をクリックします。これでスプレッドシートとフォームがリンクされます。
- フォームに回答が送信されると、「フォームの回答」 という名前のシートが自動的に作成され、データが記録されます。

以下のようになれば完了です。

試しにフォームで回答を送信してみてください。
スプレッドシートに自動的に回答が追加されれば、スプレッドシートのリンクは成功です！

物品名をGoogleフォームで事前入力するリンクの作成

目的

QRコードを使ってGoogleフォームにアクセスする際に、物品名が事前に入力されていると手間が省け、入力が簡単になります。
これにより、消耗品の交換や補充をすぐに記録できるため、管理が効率化されます。

手順

Googleフォームで「事前入力リンク」を作成する
- Googleフォーム編集画面で、右上の「︙」アイコンをクリックし、「事前入力したリンクを取得」を選択します。
- 物品名フィールドに適当な値を入力し、事前入力されたフォームのリンクを取得します。
物品名を動的に設定する
- 取得したリンクのURLの末尾に「物品名」を動的に付加します。これにより、物品ごとに異なるフォームがQRコードに埋め込まれます。

印刷用シートにQRコードを表示

目的

このステップでは、各物品に対応するGoogleフォームのリンクをQRコード化し、印刷用シートに表示します。
QRコードを印刷して冷蔵庫やストック棚に貼り付けることで、消耗品がなくなった際に簡単にGoogleフォームにアクセスでき、物品名が事前入力された状態でフォームに記録ができます。

手順

印刷用シートの作成
- Googleスプレッドシートで「印刷用」というシートを作成し、以下のような列を設定します：
物品名 QRコード

シャンプー

リンス

ボディソープ

歯磨き粉
物品名の参照
- 印刷用シートの物品名列に、物品リストシートから物品名を参照します。=物品リスト!A2 などの数式で、物品リストのデータを参照します。
QRコードの生成
以下の数式を使って、Googleフォームの事前入力URLをQRコード化します
```
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL("https://docs.google.com/forms/d/e/FORM_ID/viewform?usp=pp_url&entry.1234567890="&ENCODEURL(A2)))
```
- FORM_ID：これはGoogleフォームの固有IDです。GoogleフォームのURLから取得します。
- entry.1234567890：これはフォームのフィールドIDで、事前に入力したい質問に対応します。
- A2：物品名が格納されたセルを動的に参照し、それをGoogleフォームに自動入力します。
- 注意点
  この数式はサンプルなので、各自のGoogleフォームのIDや質問のフィールドIDに合わせてカスタマイズしてください。

物品名	QRコード
シャンプー
リンス
ボディソープ
歯磨き粉

QRコード生成URLの説明

https://api.qrserver.com/v1/create-qr-code/ はQRコードを生成するためのAPIです。このAPIにいくつかのパラメータを渡すことで、QRコードを生成します。

size=150x150: QRコードのサイズを指定しています。例えば、200x200 など他のピクセル数にも変更可能です。
data=: QRコードに埋め込むデータです。URLなどをURLエンコードした文字列を指定します。

以下のようになれば完了です。

※QRコードは example.com で生成しています。

Google Apps Scriptで交換日・補充間隔・予想交換日を自動計算

目的

Googleフォームから送信されたデータをもとに、物品リストの「最近の交換日」「補充間隔」「予想交換日」を自動で計算し、更新します。これにより、手動でデータを追跡する必要がなくなり、消耗品の補充時期を正確に管理できます。

手順

Apps Scriptの設定
- Googleスプレッドシートの「拡張機能」→「Apps Script」を開きます。
- 新しいプロジェクトを作成し、スクリプトエディタを開きます。
スクリプトの作成
- 次に、最近の交換日や補充間隔、予想交換日を自動計算する関数を作成します。スクリプト内では、Googleフォームの回答を読み取り、「物品リスト」シートを更新します。

Google Apps Scriptで交換日・補充間隔・予想交換日を自動計算するスクリプトの実装

スクリプトの構成

次に、実際に「最近の交換日」「補充間隔」「予想交換日」を計算するためのGoogle Apps Scriptを作成します。
このスクリプトは、Googleフォームの回答をもとにして自動的に物品リストを更新します。

手順

Google Apps Scriptの関数作成
- getLastReplacementDate(itemName, dataList)
  物品ごとの最新の交換日を取得します。
- calculateAverageInterval(itemName, dataList)
  過去の交換間隔を基に平均日数を計算します。
- getNextReplacementDate(itemName, dataList)
  次回の交換日を予測します。

交換日、補充間隔、予想交換日を計算する関数

ここでは、具体的なGoogle Apps Scriptのコードを示します。
このスクリプトを使用して、「物品リスト」シートを自動的に更新し、Googleフォームの回答に基づいて消耗品の交換日や次回の予想交換日を計算します。

/**
 * 指定された物品の最新の交換日を取得する関数
 * 
 * @param {string} itemName - 物品名
 * @param {Array} dataList - 物品名と交換日が格納された2次元配列
 * @return {Date|string} 最新の交換日。データがない場合はnullを返す
 */
function getLastReplacementDate(itemName, dataList) {
  var exchangeDates = [];
  
  // 指定された物品名の交換日を収集
  for (var i = 0; i < dataList.length; i++) {
    if (dataList[i][0] === itemName) {
      exchangeDates.push(new Date(dataList[i][1]));
    }
  }

  // データが存在する場合、最新の交換日を返す
  if (exchangeDates.length > 0) {
    exchangeDates.sort(function(a, b) { return a - b; });  // 日付を昇順にソート
    return exchangeDates[exchangeDates.length - 1];  // 最新の日付を返す
  } else {
    // データがない場合のメッセージ
    return null;
  }
}

/**
 * 指定された物品の交換データから、平均交換間隔を計算する関数
 * 
 * @param {string} itemName - 計算する物品名
 * @param {Array} dataList - 物品名と交換日が格納された2次元配列
 * @return {number|null} 平均交換間隔（日数）。データが1件以下の場合はnullを返す
 */
function calculateAverageInterval(itemName, dataList) {
  var exchangeDates = [];
  
  // 指定された物品名に該当する交換日を全て収集する
  for (var i = 0; i < dataList.length; i++) {
    if (dataList[i][0] === itemName) {
      exchangeDates.push(new Date(dataList[i][1]));
    }
  }

  // データが2件以上ある場合、交換間隔を計算
  if (exchangeDates.length > 1) {
    // 交換日を昇順にソート
    exchangeDates.sort(function(a, b) { return a - b; });
    var totalDays = 0;
    
    // 交換間隔（日数）の合計を計算
    for (var j = 1; j < exchangeDates.length; j++) {
      totalDays += (exchangeDates[j] - exchangeDates[j - 1]) / (1000 * 60 * 60 * 24);  // ミリ秒を日数に変換
    }
    
    // 平均交換間隔を返す
    return totalDays / (exchangeDates.length - 1);
  } else {
    // データが少ない場合はnullを返す
    return null;
  }
}


/**
 * 次回の交換日を予測する関数
 * 
 * @param {string} itemName - 物品名
 * @param {Array} dataList - 物品名と交換日が格納された2次元配列
 * @param {number} [defaultDays=30] - デフォルトの交換日数。データが不足している場合に使用
 * @return {Date|string} 次回の交換日。データがない場合はnullを返す
 */
function getNextReplacementDate(itemName, dataList, defaultDays = 30) {
  var avgInterval = calculateAverageInterval(itemName, dataList);
  var exchangeDates = [];
  
  // 指定された物品の交換日を収集
  for (var i = 0; i < dataList.length; i++) {
    if (dataList[i][0] === itemName) {
      exchangeDates.push(new Date(dataList[i][1]));
    }
  }

  // 最近の交換日を取得して次回交換日を計算
  if (exchangeDates.length > 0) {
    var lastExchangeDate = exchangeDates[exchangeDates.length - 1];  // 最新の交換日
    var nextExchangeDate = new Date(lastExchangeDate);
    
    if (avgInterval) {
      // 平均交換日数を基に次回交換日を設定
      nextExchangeDate.setDate(nextExchangeDate.getDate() + avgInterval);
    } else {
      // デフォルト交換日数を使用
      nextExchangeDate.setDate(nextExchangeDate.getDate() + defaultDays);
    }
    
    return nextExchangeDate;
  } else {
    // データがない場合のメッセージ
    return null;
  }
}

スクリプトの詳細

getLastReplacementDate
この関数は、物品ごとの最新の交換日をリストから取得します。
スプレッドシートのデータを参照し、最新の日付を返します。
calculateAverageInterval
この関数は、過去の交換日データを基に、交換間隔の平均日数を計算します。
データが1件のみの場合はnullを返します。
getNextReplacementDate
この関数は、過去の交換日データと平均補充間隔を基に、次回の交換日を予測します。
データが不足している場合は、デフォルトの交換日数（30日など）を使って予測します。

最近の交換日、補充間隔、予想交換日に作成した関数を使用して計算する。

Apps Scriptは保存し、スプレッドシートに戻ります。
物品リストで空白のままだった最近の交換日、補充間隔、予想交換日にApp Scriptを使って自動計算した結果を反映します。

物品リストシートの説明

A列：物品名（シャンプー、リンスなど）
B列：最近の交換日（最新の交換日を計算して入力）
C列：補充間隔（平均交換日数を計算して入力）
D列：予想交換日（次回の交換日を計算して入力）

関数の入力

最近の交換日列（B列）

=getLastReplacementDate(A2, 'フォームの回答'!$B$2:$C$100)

物品名に基づき、フォームの回答シートから最新の交換日を取得します。
2. 補充間隔列（C列）

=calculateAverageInterval(A2, 'フォームの回答'!$B$2:$C$100)

過去の交換履歴から平均交換日数を計算します。
3. 予想交換日列（D列）

=getNextReplacementDate(A2, 'フォームの回答'!$B$2:$C$100, 30)

デフォルトで30日後を設定し、次回交換日を予測します。

以下のようになれば完了です。

フォームの回答シート
物品リストシート

Googleカレンダー通知機能の実装

目的

次回の交換日が近づいた際に、Googleカレンダーで通知が行われるように設定します。これにより、交換時期を忘れずに管理できるようになります。

手順

Googleカレンダーにイベントを追加するスクリプトを作成
Google Apps Scriptを使い、予想交換日に基づいてGoogleカレンダーに自動的にイベントを作成します。
このイベントは、次回の交換日に通知を設定することで、忘れないようにリマインドします。

Googleカレンダー通知機能の実装

次は、Googleカレンダーに予想交換日をイベントとして追加し、交換日が近づいたら自動的に通知される機能を実装します。

スクリプトの流れ

Apps Scriptでカレンダーにイベントを追加
物品リストの「予想交換日」をもとに、Googleカレンダーにイベントを作成します。
イベントには、例えば1日前の通知を設定します。

/**
 * 物品リストの予想交換日を、指定したGoogleカレンダーに追加する関数
 * 
 * スプレッドシートの「物品リスト」シートから、物品名と予想交換日を取得し、
 * 任意のカレンダーにイベントを作成する。
 * 
 * カレンダーには、予想交換日1日前に通知するポップアップリマインダーを設定する。
 */
function addCalendarEventsToSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("物品リスト"); // 「物品リスト」シートを取得
  var data = sheet.getRange('A2:D').getValues();  // 物品名, 最近の交換日, 補充間隔, 予想交換日を取得
  
  var calendarId = 'your-calendar-id@example.com';  // 任意のカレンダーIDを指定
  var calendar = CalendarApp.getCalendarById(calendarId);  // 特定のカレンダーを取得

  for (var i = 0; i < data.length; i++) {
    var itemName = data[i][0];  // 物品名
    var nextReplacementDate = data[i][3];  // 予想交換日

    // 物品名と予想交換日が存在する場合のみ処理を行う
    if (!itemName || !nextReplacementDate) {
      continue;  // データがなければ次のループへ
    }

    var nextDate = new Date(nextReplacementDate);

    // 既存の同じイベントを確認
    var existingEvents = calendar.getEventsForDay(nextDate);
    var eventExists = false;

    for (var j = 0; j < existingEvents.length; j++) {
      if (existingEvents[j].getTitle() === itemName + " の交換時期") {
        eventExists = true;
        break;
      }
    }

    // イベントが存在しない場合のみ作成
    if (!eventExists) {
      var event = calendar.createEvent(itemName + " の交換時期", nextDate, nextDate);

      // 通知を設定（例：1日前）
      event.addPopupReminder(1440);  // 1日前（1440分前）の通知を追加
    }
  }
}

スクリプトの動作

イベント作成
物品リストの予想交換日に基づいて、Googleカレンダーに「交換時期」のイベントが作成されます。
通知設定
イベントの1日前にポップアップ通知を設定しています。必要に応じて他の通知時間も追加できます。

以上でシステムは完成です。
作成したスクリプトを実行してみてください。Googleカレンダーに追加されていれば完成です！

最後に

これまでのスクリプトやフォーム、スプレッドシートを実装することで、消耗品の補充管理が効率化され、交換時期を見逃したり、補充の買い忘れがなくなります！
今回、初めてGoogleフォーム、スプレッドシート、Apps Script、Googleカレンダーを組み合わせたシステムを構築してみましたが、とても簡単にできました。
むしろ、Qiitaの記事を作成する方に時間がかかってしまいました。。。
なお、このシステム構築にあたって、記事作成やコード生成などでChatGPTに助けてもらいました。
ぜひ皆さんもChatGPTを活用して、生活をさらに豊かにしてください！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Googleスプレッドシート & Apps Scriptで消耗品管理システムを作成し、Googleカレンダーで交換時期を通知しよう

はじめに

システム概要

目次

スプレッドシートの準備

Googleフォームの作成

物品名をGoogleフォームで事前入力するリンクの作成

目的

手順

印刷用シートにQRコードを表示

目的

手順

QRコード生成URLの説明

Google Apps Scriptで交換日・補充間隔・予想交換日を自動計算

目的

手順

Google Apps Scriptで交換日・補充間隔・予想交換日を自動計算するスクリプトの実装

スクリプトの構成

手順

交換日、補充間隔、予想交換日を計算する関数

スクリプトの詳細

最近の交換日、補充間隔、予想交換日に作成した関数を使用して計算する。

物品リスト シートの説明

関数の入力

Googleカレンダー通知機能の実装

目的

手順

Googleカレンダー通知機能の実装

スクリプトの流れ

スクリプトの動作

最後に

物品リストシートの説明