Create a Notification from Google Sheets to LINE Notification.

It started because I had some free time and wanted to try out a project. After searching on Google, I found out that I could send notifications to LINE using Google Sheets, so I decided to give it a try. Now that I’ve done it, I thought I’d share how it went.

I came up with the idea to solve the problem of food expiring in the fridge. It would be great to have a notification to remind me before it happens.

Step 1 : Prepare Your Google Sheet

Step 2 : Get a LINE Notify Token

Step 3: Create a Google Apps Script

Step 4: Set Up a Trigger

Step 1 : Prepare Your Google Sheet

1. https://sheets.new/
2. Create fields: No, Inventory, Shelf No, Expiry Date, Date notice before expired.

Step 2 : Get a LINE Notify Token

1. go to website https://notify-bot.line.me/en/
2. login using LINE account
3. after login click menu profile → My page

4 . Click Generate token

5. Save the generated token for later

Step 3: Create a Google Apps Script

function sendFridgeNotifications() {
  const token = "YOUR_LINE_NOTIFY_TOKEN"; // Replace with your LINE Notify token

  // Access the Google Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  // Get today's date and format it
  const today = new Date();
  const timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  const todayString = Utilities.formatDate(today, timeZone, 'yyyy-MM-dd');

  // Get the index of each column based on the header row
  const headers = data[0];
  const columnIndex = {
    inventory: headers.indexOf('Inventory'),
    expiryDate: headers.indexOf('Expired Date'),
    noticeBefore: headers.indexOf('Date notice Before Expired'),
  };

  // Loop through the sheet data starting from the second row
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const inventoryItem = row[columnIndex.inventory]; // Inventory name
    const expiryDate = new Date(row[columnIndex.expiryDate]); // Expiry date
    const noticeBefore = Number(row[columnIndex.noticeBefore]); // Days before expiry to notify

    // Validate data
    if (!inventoryItem || isNaN(expiryDate) || isNaN(noticeBefore)) {
      continue; // Skip invalid rows
    }

    // Calculate the notification date
    const notifyDate = new Date(expiryDate);
    notifyDate.setDate(notifyDate.getDate() - noticeBefore);
    const notifyDateString = Utilities.formatDate(notifyDate, timeZone, 'yyyy-MM-dd');

    // Compare with today's date
    if (notifyDateString === todayString) {
      // Send a notification if the dates match
      const message = `🔔 Reminder: '${inventoryItem}' is expiring soon on ${Utilities.formatDate(expiryDate, timeZone, 'yyyy-MM-dd')}.`;
      sendLineNotification(token, message);
    }
  }
}

// Function to send a notification to LINE
function sendLineNotification(token, message) {
  const url = "https://notify-api.line.me/api/notify";
  const options = {
    method: "post",
    headers: {
      Authorization: "Bearer " + token,
    },
    payload: {
      message: message,
    },
  };

  UrlFetchApp.fetch(url, options);
}

Before deployment, we will test the function sendFridgeNotifications to ensure it works.

• Assume today’s date is 26/09/2024, all inventory expires on 27/09/2024, and the expiry date notice is 1.

2. Click function sendFirdgeNotifications

3. For the first time, you must grant permission.

4. Click Run

5. Notification is shown.

Step 4: Set Up a Trigger

When the set time arrives, it will send a notification.

Thank you for reading this far. I hope you found it helpful.
If you need help with website development, programming, or creating similar notification systems, feel free to contact me. 😁

You can check out an example at www.webyourdream.com or reach me at gtpatchaya@gmail.com.