メインコンテンツへスキップ
見出し画像

Googleスプレッドシート賞味期限管理システムを作成してみた(LINEで通知も)

    みなさま❤️ごきげんよう 土曜の夜の雑談のれぃです

    帰宅して冷蔵庫を開け楽しみにとってあった大好物を
    食べようとしたら が〜ん😱またか🤯まさかの😨
    賞味期限切れなんてことありませんか?

    持ったい無い。こんな自分が情けない。

    画像

    そんな日々の連続、でも気にしていたらとても疲れる。
    冷蔵庫よ。期限切れがあったら警戒音を鳴らしてほしい!

    ということで、そんな私はGeminiに聞いて
    Googleスプレッドシート賞味期限管理システムを作成してみました

    <Gemini>
    Googleスプレッドシートのみを使って、
    「賞味期限の自動計算」
    「日数に応じた色の変化(アラート)」
    「入力ミス防止のメニュー化」を備えた
    簡単かつ実践的な「賞味期限管理システム」の作成手順を解説します。

    1. シートの表レイアウト作成

    まず、新しいスプレッドシートを作成し、1行目(見出し列)に以下の項目を入力します。

    A列B列C列D列E列F列G列分類商品・食材名保管場所賞味期限残り日数ステータス備考

    2. 数式の設定(自動計算)

    数式を設定しておくことで、毎日自動的に「残り日数」と「ステータス」が更新されます。

    ① 残り日数の自動計算(E列)

    E2セルに以下の数式を入力し、下方向(E3以降)へコピーします。

    Excel

    =IF(ISBLANK(D2), "", D2 - TODAY())
    
    • 説明: D列(賞味期限)が空欄の場合は空白を表示し、日付が入っている場合は「賞味期限 - 今日」で残り日数を算出します。

    ② ステータスの自動分類(F列)

    F2セルに以下の数式を入力し、下方向(F3以降)へコピーします。

    Excel

    =IF(ISBLANK(D2), "", IF(E2<0, "期限切れ", IF(E2<=3, "要消費(3日以内)", IF(E2<=7, "注意(1週間以内)", "安全"))))
    
    • 判定基準:

      • 残り日数がマイナス ➔ 期限切れ

      • 残り日数が 0〜3日 ➔ 要消費(3日以内)

      • 残り日数が 4〜7日 ➔ 注意(1週間以内)

      • 残り日数が 8日以上 ➔ 安全

    3. 条件付き書式(自動アラート表示)

    視覚的にすぐ危険度がわかるよう、ステータスや行全体に色をつけます。

    1. A列からG列を選択(またはデータ範囲 A2:G100 を選択)。

    2. メニューから 「表示形式」 > 「条件付き書式」 を選択。

    3. 以下の規則を追加します(書式ルールは 「カスタム数式」 を選択)。

    規則1:期限切れ(赤色)

    • カスタム数式: =$F2="期限切れ"

    • 塗りつぶしの色: 淡い赤色(文字色:濃い赤)

    規則2:要消費(オレンジ色)

    • カスタム数式: =$F2="要消費(3日以内)"

    • 塗りつぶしの色: 淡いオレンジ色

    規則3:注意(黄色)

    • カスタム数式: =$F2="注意(1週間以内)"

    • 塗りつぶしの色: 淡い黄色

    4. 入力補助(プルダウンメニューの設定)

    入力の手間と表記ゆれを減らすためにプルダウンを作成します。

    「分類」と「保管場所」のプルダウン

    1. A列(分類)の2行目以降を選択(例: A2:A100)。

    2. メニューの 「データ」 > 「データの入力規則」 をクリック。

    3. ルールを追加 > 条件で 「プルダウン」 を選択し、項目を入力(例: 冷蔵品, 冷凍品, 加工食品, 調味料, 日用品 など)。

    4. C列(保管場所)も同様に設定(例: 冷蔵庫Main, 野菜室, 冷凍庫, パントリー など)。

    「賞味期限」の日付カレンダー化

    1. D列(賞味期限)の2行目以降を選択(例: D2:D100)。

    2. 「データ」 > 「データの入力規則」 でルールを追加。

    3. 条件で 「有効な日付」 を選択して保存。

      • これにより、セルをダブルクリックするだけでカレンダーから日付を選べるようになります。

    5. 運用を便利にするテクニック

    ソート(並べ替え)で期限が近い順に表示

    1. 1行目の見出し列(A1:G1)を選択。

    2. メニューの 「データ」 > 「フィルターを作成」 を選択。

    3. 賞味期限(D列)または残り日数(E列)のフィルターアイコンをクリックし、「昇順(A→Z)」で並べ替えると、期限が迫っているものが常に上に表示されます。

    スマホアプリからの活用

    Googleスプレッドシートのスマホアプリを入れ、該当シートをショートカット作成しておくと、買い物前や調理中にすぐ確認・更新ができて便利です。

    <れぃ❤️>
    スプレッドシートの賞味期限管理システムで、期限が迫った食材をLINEやメールへ自動通知するGoogle Apps Script(GAS)を作成してください。

    <Gemini>
    期限が迫っている食材(またはすでに期限切れの食材)を抽出し、毎朝決まった時間(例:朝8時)にメールまたはLINEへ自動通知するGoogle Apps Script(GAS)を作成しました。

    お好みの通知方法(メールまたはLINE Notify / LINE Messaging API)に合わせて設定してください。

    準備:スプレッドシートのレイアウト確認

    前述のシステム構成(D列:賞味期限、E列:残り日数、F列:ステータス)を前提としています。

    A列B列C列D列E列F列分類商品・食材名保管場所賞味期限残り日数ステータス

    ※ステータスが 「期限切れ」 または 「要消費(3日以内)」 になっている行を自動抽出して通知します。

    方法1:メールで自動通知するスクリプト(最も簡単)

    GoogleアカウントのGmailを使って、自分のメールアドレスに通知を送る最もシンプルな方法です。

    1. スクリプトの登録

    1. スプレッドシートの上部メニュー 「拡張機能」 > 「Apps Script」 をクリック。

    2. エディタが開いたら、もとからあるコードを消去し、以下のコードを貼り付けます。

    JavaScript

    function sendExpirationAlertMail() {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      const data = sheet.getDataRange().getValues();
      
      // ヘッダーを除いたデータ行を取得
      const alertItems = [];
      
      for (let i = 1; i < data.length; i++) {
        const name = data[i][1];       // B列:商品・食材名
        const location = data[i][2];   // C列:保管場所
        const expiryDate = data[i][3]; // D列:賞味期限
        const remainingDays = data[i][4]; // E列:残り日数
        const status = data[i][5];     // F列:ステータス
    
        // ステータスが「期限切れ」または「要消費(3日以内)」の場合に対象とする
        if (status === "期限切れ" || status === "要消費(3日以内)") {
          let formattedDate = "";
          if (expiryDate instanceof Date) {
            formattedDate = Utilities.formatDate(expiryDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
          } else {
            formattedDate = expiryDate;
          }
          
          alertItems.push(`・[${status}] ${name}(保管:${location} / 期限:${formattedDate} / 残り${remainingDays}日)`);
        }
      }
    
      // 対象がある場合のみメール送信
      if (alertItems.length > 0) {
        const recipient = Session.getActiveUser().getEmail(); // 実行者のGoogleメールアドレス
        const subject = `【賞味期限アラート】期限が迫っている食材が ${alertItems.length} 件あります`;
        const body = `以下の食材の賞味期限が迫っているか、既に過ぎています。\n早めの消費・確認をお願いします。\n\n` + alertItems.join("\n") + `\n\nスプレッドシートをご確認ください。`;
    
        MailApp.sendEmail(recipient, subject, body);
        Logger.log("アラートメールを送信しました。");
      } else {
        Logger.log("通知対象の食材はありませんでした。");
      }
    }
    

    方法2:LINE(LINE Messaging API)で通知するスクリプト

    スマホで受け取りたい場合は、LINE Bot(LINE Messaging API)を利用して自分のLINEに届くように設定できます。

    事前準備:

    1. LINE Developers にログインし、Bot(チャネル)を作成。

    2. 「チャネルアクセストークン(長期)」を発行し、ご自身の「ユーザーID」を取得します。

    コード

    JavaScript

    function sendExpirationAlertLine() {
      // LINE の設定情報を入力
      const LINE_TOKEN = "ここにチャネルアクセストークンを入力";
      const USER_ID = "ここにご自身のユーザーIDを入力";
    
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      const data = sheet.getDataRange().getValues();
      
      const alertItems = [];
      
      for (let i = 1; i < data.length; i++) {
        const name = data[i][1];       // B列
        const location = data[i][2];   // C列
        const expiryDate = data[i][3]; // D列
        const remainingDays = data[i][4]; // E列
        const status = data[i][5];     // F列
    
        if (status === "期限切れ" || status === "要消費(3日以内)") {
          let formattedDate = "";
          if (expiryDate instanceof Date) {
            formattedDate = Utilities.formatDate(expiryDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
          } else {
            formattedDate = expiryDate;
          }
          
          alertItems.push(`⚠️ ${name}\n   場所: ${location} | 期限: ${formattedDate} (${status})`);
        }
      }
    
      if (alertItems.length > 0) {
        const messageText = `【賞味期限お知らせ】\n期限が近い食材が ${alertItems.length} 件あります!\n\n` + alertItems.join("\n\n");
        
        // LINE API へリクエスト
        const url = "https://api.line.me/v2/bot/message/push";
        const payload = JSON.stringify({
          to: USER_ID,
          messages: [{ type: "text", text: messageText }]
        });
    
        const options = {
          method: "post",
          headers: {
            "Content-Type": "application/json",
            "Authorization": "Bearer " + LINE_TOKEN
          },
          payload: payload
        };
    
        UrlFetchApp.fetch(url, options);
        Logger.log("LINE通知を送信しました。");
      }
    }
    

    毎朝自動実行(トリガー)の設定手順

    コードを登録したら、毎日決まった時間に自動実行されるように設定します。

    1. Apps Script 画面の左メニューにある 「トリガー(時計のアイコン)」 をクリックします。

    2. 右下の 「+ トリガーを追加」 ボタンをクリックします。

    3. 以下の通り設定します:

      • 実行する関数を選択: sendExpirationAlertMail (または sendExpirationAlertLine)

      • イベントのソースを選択: 時間駆動型

      • 時間型イベントのタイプを選択: 日付ベースのタイマー

      • 時間の間隔を選択: 午前 7 時〜8 時 (お好みの時間帯を選択)

    4. 「保存」 をクリックします(初回保存時にGoogleアカウントへの権限承認ポップアップが出た場合は、画面の指示に従って許可してください)。

    これで毎朝自動的にチェックが行われ、該当する食材がある場合のみ通知が届くようになります。

    早速 作成して活用してみようと思います🎵

    最後までお読みくださりありがとうございました!


     
     
    歴20年デザイナー。認定心理カウンセラー、アートセラピスト。一軒家アトリエを開設。ワンオペ、モラ夫、アル中夫、毒父、毒親戚をテーマに投稿。激務でメニエール発作で3ヶ月の寝たきり後、運動療法で復活!低音難聴、BPPV、平衡感覚を失った経験から予防法も伝えている。

    あなたへのおすすめ