Automate Data Updates

Linking Google Alerts with Airtable via RSS and Scripts

"Keeping up with the latest news and updates on specific topics can be time-consuming. This guide will show you how to automate this process by configuring Google Alerts to send news updates directly to an Airtable base via RSS feeds, using Airtable's scripting capabilities. This setup keeps your database updated with the latest news on topics of interest without manual intervention".

Tools Overview

Google Alerts: A content change detection and notification service, offering a simple way to monitor the web for interesting new content.

Airtable: A cloud collaboration service that combines the features of a database and a spreadsheet, allowing you to manage your data efficiently.

Steps to Automate Data Updates

In the following steps, you'll learn how to set up Google Alerts, configure your Airtable base, and write a script to automatically fetch and record news data. This will help you maintain an up-to-date database effortlessly.

Step 1: Set Up Google Alerts

Visit Google Alerts: Navigate to Google Alerts.
Create a New Alert: Type in the search term for the news topic you are interested in. For example, if you're interested in "Short " enter that into the search box.
Show Options: Before creating the alert, click on the "Show options" link below the search box.
Change Delivery to RSS: In the options, change the "Deliver to" setting from your email to "RSS feed."
Create Alert: Click the "Create Alert" button. You will see a new alert with an RSS icon next to it.
Get RSS Link: Click the RSS icon, and you will be taken to the RSS feed. Copy the URL from your browser's address bar; this is the RSS feed URL you will need.

0:00

Step 2: Set Up Airtable Base

Create a New Base: Log into your Airtable account or create a new one here and create a new base from scratch or use an existing template that suits your data structure needs.
Design Your Table: Create a table with columns that match the data you want to store from your Google Alerts, such as "Title," "Link," "Published Date," etc.

Step 3: Write an Airtable Script to Fetch RSS and Update the Table

Navigate to Automations in Airtable:
Create a new automation.
Select "At a scheduled time" as the trigger. Set the time interval you want it to trigger (minutes, hour, day, etc.), as well as a starting date and time. Then test the trigger.
For the action, select "Run script."

0:00

Write the Script:
Open the scripting app by clicking the “Edit code” button on the bottom right side of the page and paste the code, editing the script as needed:

0:00

Replace the "YOUR URL HERE" with your RSS feed URL
Ensure the table and field names in the code match your Airtable table and fields.

<!-- // Define the RSS feed URL
let url = "YOUR GOOGLE RSS FEED URL";

try {
    // Fetch the RSS feed
    let response = await fetch(url);
    if (!response.ok) {
        throw new Error(`HTTP error! status: ${response.status}`);
    }
    let xmlText = await response.text();

    if (!xmlText) {
        throw new Error("No RSS data received.");
    }

    // Function to parse XML with basic string manipulation
    function parseRSS(xml) {
        const items = [];
        const entries = xml.split('<entry>').slice(1); // Split the XML by <entry>

        entries.forEach(entry => {
            const titleStart = entry.indexOf('<title>') + 7;
            const titleEnd = entry.indexOf('</title>');
            const title = entry.substring(titleStart, titleEnd);

            const linkStart = entry.indexOf('href="') + 6;
            const linkEnd = entry.indexOf('"', linkStart);
            const link = entry.substring(linkStart, linkEnd);

            const publishedStart = entry.indexOf('<published>') + 11;
            const publishedEnd = entry.indexOf('</published>');
            const published = entry.substring(publishedStart, publishedEnd);

            items.push({
                title: title,
                link: link,
                published: published
            });
        });

        return items;
    }

    // Parse the XML text using simple string functions
    let data = parseRSS(xmlText);

    if (data.length === 0) {
        throw new Error("No entries found in RSS feed.");
    }

    // Log the data to see the output
    console.log(data);
    console.log(data[0].link);

    // Additional logic to update Airtable omitted for brevity
    // Here you can add code to insert/update records in Airtable based on extracted data

    // 3. Save news array
    const newsArr = data;
    // 4. Load table records
    const newsTable = base.getTable("YOUR TABLE NAME");
    const newsQuery = await newsTable.selectRecordsAsync({
        fields: ["URL"]
    });

    // 5. Create unique identifier / URL array
    const uniqueUrls = newsQuery.records.map(rec => rec.getCellValue("URL"));

    // Extract URL function
    function extractUrl(googleUrl) {
        // Decode the URL to ensure all characters are interpreted correctly
        let decodedUrl = decodeURIComponent(googleUrl);

        // Find the start and end indices of the actual URL within the decoded string
        let urlStart = decodedUrl.indexOf("url=") + 4; // Start after "url="
        let urlEnd = decodedUrl.indexOf("&", urlStart); // End at the next "&"

        if (urlEnd === -1) { // Check if "&" is not found, which might happen if "url" is the last parameter
            urlEnd = decodedUrl.length;
        }

        // Extract the actual URL
        const actualUrl = decodedUrl.substring(urlStart, urlEnd);

        return actualUrl;
    }

    // Function to clean-up the title
    function extractTitle(text) {
        // Find the start index of the title content right after '<title type="html">'
        const titleStart = text.indexOf('<title type="html">') + 19; // Move past the '<title type="html">' tag
        
        // Extract the title using substring            // Decoding HTML entities if necessary
        const title = text.substring(titleStart, ).replace(/"/g, '"').replace(/&/g, '&').replace(/</g, '').replace(/b>/g, '').replace(/'/g, '');
        
        return title;
    }

    // 6. Loop over jobs array and create CreateArr

    const createArr = [];

    newsArr.forEach(news => {
        // Call the function and store the result
        news.title = extractTitle(news.title);
        // Call the function with the Google URL
        news.link = extractUrl(news.link);
        // check whether job already exists
        if(!uniqueUrls.includes(news.link)) {
            createArr.push({
                fields: {
                    "News title": news.title,
                    "URL": news.link,
                    "Date published": news.published
                }
            })
        }
        console.log(news.title);
    })

    // 7. createRecordsAsync()
    await newsTable.createRecordsAsync(createArr);

} catch (error) {
    console.error("Failed to fetch or parse RSS feed:", error.message);
}
 -->
<script>
  console.log('Hello, Brizy!');
</script>

Step 4: Run Your Script

Run the Script: Click the "Run" button in the scripting block. This will execute your script.
Check Your Table: If the script runs successfully, your table should be populated with the latest entries from your Google Alerts RSS feed.

This setup automates the ingestion of news data from Google Alerts into an Airtable base, using a script to parse RSS and update your records accordingly. Adjust the script as necessary to match your specific data fields and table structure.

Ready to streamline your data updates? Try setting up your Google Alerts and Airtable base today. If you found this guide helpful, share it with your network and visit our website for additional resources and information.