How to send Slack notifications from a Google Sheets using Apps Script

Note: This post was originally published in our community forum.

In the modern workplace, automating routine tasks is key to efficiency. One common task is sending notifications based on spreadsheet data, such as birthdays or anniversaries. Slack, a popular communication tool for teams, can be integrated with Google Sheets to send automated notifications, making it easier to celebrate special occasions or remind team members of important dates.

In this blog post, we’ll guide you through the process of setting up Slack notifications from a Google Spreadsheet using Google Apps Script, and how to create a Slack app for this purpose.

Creating a Slack app for notifications

Before diving into the script, you need to set up a Slack app to send notifications to your workspace. Here’s how:

1. Start by visiting the Slack API website at https://api.slack.com/apps. Log in if prompted.
2. Create a New App: Click on “Create New App.” Choose “From scratch” and give your app a name. Select the workspace where you want the app installed.
3. Enable Incoming Webhooks: After creating your app, go to “Incoming Webhooks” in the “Features” section and toggle it on. This allows your app to send messages to a Slack channel.
4. Generate a Webhook URL: Click “Add New Webhook to Workspace,” choose a channel (like #general) for notifications, and click “Allow.” Copy the webhook URL provided.

This URL is what you’ll use in your Google Apps Script to send messages from your spreadsheet to Slack.

Automating Slack notifications with Google Apps Script

With your Slack app and webhook ready, the next step is to automate notifications from a Google Spreadsheet. Assume you have a spreadsheet with Column A for emails, Column B for names, and Column C for birthdays.

1. Open Your Spreadsheet: Go to the spreadsheet from which you want to send notifications.
2. Access the Apps Script Editor: Click on Extensions > Apps Script and clear any existing code.
3. Paste the Script: Use the following Google Apps Script, replacing 'YOUR_WEBHOOK_URL_HERE' with your actual webhook URL from Slack.

      function sendSlackNotifications() {
        var webhookUrl = 'YOUR_WEBHOOK_URL_HERE';
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
        var data = sheet.getDataRange().getValues();
        var today = new Date();
        today.setHours(0,0,0,0);
      
        data.forEach(function(row, index) {
          // Skip header row
          if (index === 0) return; 
          var email = row[0], name = row[1], birthday = new Date(row[2]);
          birthday.setHours(0,0,0,0);
      
          if (birthday.getTime() === today.getTime()) {
            var payload = JSON.stringify({ text: `🎉 Happy Birthday, ${name} (${email})! 🎉` });
            var options = { method: 'post', contentType: 'application/json', payload: payload };
            UrlFetchApp.fetch(webhookUrl, options);
          }
        });
      }

4. Save and Run the Script: Click the disk icon to save, then click the play button to run your script manually.
5. Automate Daily Notifications: To have this script run daily, click on the clock icon (Triggers) and set a time-based trigger for the sendSlackNotifications function.

Final thoughts

Integrating Slack with Google Sheets through Apps Script is a powerful way to automate notifications. Whether you’re celebrating birthdays, tracking project milestones, or reminding team members of important dates, this setup simplifies communication and ensures no special occasion goes unnoticed. Always remember to keep your webhook URL secure and test your setup thoroughly before rolling it out to your entire team. With a little setup, your team’s communication can become more efficient and engaging.