How to Use Notion with Gmail and Google Sheets using Apps Script

Introduction

Notion, Gmail, and Google Sheets are powerful tools for personal and team productivity. By integrating these tools using Google Apps Script, you can automate workflows, streamline tasks, and enhance productivity. This tutorial will guide you through how to use Notion with Gmail and Google Sheets using Google Apps Script, allowing you to send emails, add data to Notion, and manage your data in Google Sheets with ease.

Step 1: Set Up Google Sheets

Start by creating a Google Sheet where you will store and manage your data. For example, you could create a sheet with the following columns:

| Task          | Email               | Status   | Notion Page Link |
|---------------|---------------------|----------|------------------|
| Send Report   | [email protected]     | Pending  |                  |
| Meeting Notes | [email protected]     | Complete |                  |
        

This structure allows you to track tasks, send emails, and log Notion page links directly from Google Sheets.

Step 2: Set Up Notion Integration

Notion doesn’t have a native API for direct integration with Google Sheets and Gmail, but you can use tools like the Make (formerly Integromat) or Zapier to connect Google Sheets with Notion. Alternatively, you can use the Notion API directly in Apps Script to create or update pages.

To use the Notion API, you need to:

1. Go to Notion Integrations and create a new integration.
2. Copy the Integration Token provided by Notion. You will use this token to authenticate your API calls from Google Apps Script.
3. Share a Notion page or database with your integration to allow it to access and modify your content.

Now, you’re ready to use Notion’s API with Google Apps Script to create or update pages in Notion directly from Google Sheets.

Step 3: Write Google Apps Script for Gmail, Google Sheets, and Notion

Next, we’ll write a Google Apps Script to automate the process of sending emails, updating Google Sheets, and creating Notion pages. Here’s an example script to get started:

function sendEmailAndUpdateNotion() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');  // Adjust the sheet name
    var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();  // Get data from the sheet

    var notionApiToken = 'your_notion_integration_token';  // Replace with your Notion API token
    var notionDatabaseId = 'your_notion_database_id';  // Replace with your Notion database ID

    data.forEach(function(row, index) {
        var task = row[0];
        var email = row[1];
        var status = row[2];
        var notionPageLink = row[3];

        // Check if task status is Pending, if so, send an email and create a Notion page
        if (status === 'Pending') {
            // Send email using Gmail
            GmailApp.sendEmail(email, 'Reminder: Task - ' + task, 'Please complete the task: ' + task);

            // Create a new page in Notion using Notion API
            var notionPayload = {
                "parent": { "database_id": notionDatabaseId },
                "properties": {
                    "Name": { "title": [{ "text": { "content": task } }] },
                    "Email": { "rich_text": [{ "text": { "content": email } }] },
                    "Status": { "select": { "name": status } },
                }
            };

            var options = {
                "method": "POST",
                "headers": {
                    "Authorization": "Bearer " + notionApiToken,
                    "Content-Type": "application/json"
                },
                "payload": JSON.stringify(notionPayload)
            };

            // Send the request to Notion API
            var response = UrlFetchApp.fetch('https://api.notion.com/v1/pages', options);
            var jsonResponse = JSON.parse(response.getContentText());

            // Log the Notion page URL for reference
            var notionUrl = jsonResponse.url;
            sheet.getRange(index + 2, 4).setValue(notionUrl);  // Update the Notion link in the sheet
        }
    });
}
        

Explanation:

• This script reads task data from the Google Sheet.
• If the task is marked as "Pending," it sends a reminder email using Gmail.
• It then creates a new page in Notion with the task details using the Notion API.
• The Notion page link is then added to the Google Sheet in the "Notion Page Link" column.

Remember to replace `'your_notion_integration_token'` with your Notion API token and `'your_notion_database_id'` with your Notion database ID.

Step 4: Trigger the Script to Run Automatically

You can set up a trigger to run this script automatically at specific intervals. For example, you might want it to check for pending tasks and send reminders once a day.

1. In the Google Apps Script editor, go to Triggers > Add Trigger.
2. Select the function sendEmailAndUpdateNotion.
3. Choose the event type as Time-driven and set the frequency (e.g., daily, hourly, etc.).
4. Click Save to set the trigger.

Now, the script will automatically run at the interval you specified, checking for pending tasks, sending emails, and creating Notion pages without manual intervention.

Step 5: Handling Errors and Notifications

It's essential to handle errors and notify users in case something goes wrong. You can modify the script to catch errors and send an email notification in case of a failure:

function sendErrorNotification(errorMessage) {
    var adminEmail = '[email protected]';  // Replace with your email address
    GmailApp.sendEmail(adminEmail, 'Error in Notion Integration', errorMessage);
}

function sendEmailAndUpdateNotion() {
    try {
        // Existing script logic
    } catch (e) {
        sendErrorNotification(e.message);
    }
}
        

In this code, the sendErrorNotification function sends an email to the admin if the script encounters an error. The try-catch block wraps the main script logic to catch and report errors.

Conclusion

By integrating Notion, Gmail, and Google Sheets with Google Apps Script, you can automate tasks, send notifications, and streamline your workflow. This integration allows you to keep track of tasks, communicate with team members via email, and create or update Notion pages directly from Google Sheets. With the power of Apps Script, the possibilities for workflow automation are endless.