สอนทำแจ้งเตือนสินค้าหมดอายุ ล่วงหน้า จาก Google Sheet ไป ที่ Line Application : Line Notify Ep.2

Introduction

In this tutorial, you will learn how to set up notifications for expired products using Google Sheets and send these alerts to your Line application via Line Notify. This process is essential for managing inventory efficiently and ensuring that you are always aware of product expirations ahead of time.

Step 1: Prepare Your Google Sheet

• Create a Google Sheet to track your products.
• Include the following columns:
  • Product Name
  • Expiration Date
  • Notification Status
• Populate the sheet with sample data to test the notification system.

Step 2: Set Up Notification Logic in Google Sheets

• Use conditional formatting to highlight products nearing expiration:

  • Select the expiration date column.
  • Go to Format > Conditional formatting.
  • Set the rule to highlight cells that are less than or equal to today’s date plus a defined number of days (e.g., 7 days).

• In the Notification Status column, write a formula to indicate whether a notification has been sent:

  =IF(AND(A2<>"", B2<=TODAY()+7, C2<>"Notified"), "Notify", "")
  

• Drag the formula down to apply it to all rows in the sheet.

Step 3: Create a Line Notify Token

• Go to the Line Notify website and log in with your Line account.
• Navigate to the "My Page" section and click on "Generate Token."
• Enter a name for the token and select the target chat (either a personal chat or a group).
• Click on "Generate Token" and save this token, as you will need it in the next steps.

Step 4: Write a Google Apps Script

• Open your Google Sheet and go to Extensions > Apps Script.
• Replace any default code with the following script:

  function sendLineNotify(message) {
      var token = "YOUR_LINE_NOTIFY_TOKEN"; // Replace with your generated token
      var options = {
          "method": "post",
          "payload": "message=" + message,
          "headers": {
              "Authorization": "Bearer " + token
          }
      };
      UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
  }
  
  function checkExpiration() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var data = sheet.getDataRange().getValues();
      for (var i = 1; i < data.length; i++) {
          if (data[i][2] == "Notify") { // Check the Notification Status
              var message = "Product " + data[i][0] + " is expiring on " + data[i][1];
              sendLineNotify(message);
              sheet.getRange(i + 1, 3).setValue("Notified"); // Update status
          }
      }
  }
  

• Replace "YOUR_LINE_NOTIFY_TOKEN" with your actual Line Notify token.

Step 5: Set Up a Trigger for Automation

• In the Apps Script editor, go to Triggers (clock icon).
• Click on “Add Trigger” in the bottom right corner.
• Set the function to checkExpiration, select the event source as “Time-driven,” and choose how often you want the script to run (e.g., daily).

Conclusion

You have now set up a system to receive notifications for expiring products directly to your Line application. This automated process will help you manage your inventory more effectively.

Key Points

• Ensure your Google Sheet is properly formatted and populated with data.
• Use Google Apps Script to send notifications.
• Set a time-driven trigger to automate the notification process.

Next Steps

• Test the setup with sample data to ensure notifications are received successfully.
• Customize the message format and notification frequency as needed.