How much time do you waste every day browsing hundreds of websites, getting lost in social media feeds, and searching for “important” information? Accessing information in the digital world is easy, but filtering for the right information is hard. This is exactly where AI Automation steps in.

In this guide, we will set up a personalized “sectoral trend radar” that works for you using Google Sheets and Google’s newest AI model, Gemini—without paying a single cent. Moreover, this system runs stably, requires no server, and is completely under your control.

The Logic Behind the System

This automation consists of three main steps. We can summarize the background process as follows:

1. Step: Source Collection (Gemini Deep Research): In the first stage, the most suitable and reliable sources for your interests are identified with AI support.
2. Step: Data Pool (Google Sheets): News from the identified sources is automatically fetched and stored in Google Sheets at specific intervals.
3. Step: Smart Analysis and Reporting (Apps Script & Gemini): Accumulated news is analyzed weekly by AI, summarized, and converted into a stylish HTML e-mail newsletter complete with visuals.

Step 1: Compiling Sources

A good newsletter starts with quality sources. Here, we will leverage Gemini’s Deep Research capabilities.

1.1. Source Discovery with Deep Research

Go to Gemini and select the “Deep Research” option from the Settings. Customize the bottom 3 lines of the command (prompt) below to fit your needs and have it list the best sources in your industry. This process will take some time. Afterward, you can edit the sources generated by Gemini, add new ones according to your wishes, or remove them.

Analyze the data in the [VARIABLES] section below and initiate Deep Research mode based on these parameters.

TASK: Identify the most competent sources on a global scale that publish on the specified [INTERESTS] from the defined [PROFESSION] perspective, and list them in a quantity of [QUANTITY].

FILTERING AND QUALITY CRITERIA (Strict Rules):

Zero Junk Content: Strictly exclude "content farms" set up for SEO purposes, sites that use automatic translation, "aggregators" that only pass on superficial news, and clickbait-focused blogs.

Authority: Select only sources with high "Domain Authority" scores, those referenced by industry professionals, or those with academic or technical depth.

Recency: It is mandatory that sources have entered active data/content within the last 1 year.

Targeting: Do not find general news sites (CNN, BBC, etc.); find vertical expertise sites directly focused on the relevant niche area.

OUTPUT FORMAT: Present the results in a table or list with the following headings:

Web Site Name and URL:

Category/Type: (e.g., Academic Journal, Industry Report, Technical Blog)

Authority Justification: (Why is it on this list? e.g., "World's largest X database" or "Publishes the annual Z report")

[VARIABLES]

Profession: Art Director
Interests: Graphic Design, Artificial Intelligence, Typography
Web Site Quantity: 10

1.2. Collecting RSS Links

We need RSS feeds so the automation can “read” the news. Copy the list found by Gemini and, selecting the “Pro” or “Flash” option this time, give the following command:

Find the RSS Feed links for the web site list below. If there is no direct RSS link, provide the most likely URL structures where I can pull the news feed.

Now, the RSS links of the websites where we will follow up-to-date news are ready.

Step 2: Google Sheets Setup

Now we are creating the heart of the system, the database.

2.1. Creating the Sheets

Go to Google Sheets and create a new spreadsheet. You can name it Sectoral Trend Report.

Create one more sheet from the bottom section and change the sheet names as follows (You must name them exactly as follows for the system to work):

1. Sheet Name: Settings
2. Sheet Name: Data

Go to the Settings sheet and paste the RSS links you found in Step 1 into column A, one under the other.

2.2. Getting the API from Google AI Studio

We need a key for the AI automation and for Gemini to prepare reports:

1. Go to Google AI Studio.
2. Click the “Create API Key” button.
3. Give your key any name (you can leave the “Choose an imported project” part as default) and click the Create button.
4. Copy the created key and note it down somewhere.

2.3. Integrating the Code

Now we return to our table in Google Sheets and add the codes that will run the system.

1. From the top menu in your Spreadsheet, click on Extensions > Apps Script.
2. Delete all the code on the page that opens.
3. Copy the Source Code block below and paste it there.
4. Do not forget to replace GEMINI_API_KEY, EMAIL_ADDRESS, and other personal information in the SETTINGS PANEL section at the very beginning of the code with your own information.
5. After editing the codes, save the file by clicking the disk icon at the top.

/** * ==========================================
 * SETTINGS PANEL (Edit Only This Section)
 * ==========================================
 */
const GEMINI_API_KEY = "YOUR_API_KEY_HERE"; 
const EMAIL_ADDRESS = "YOUR_EMAIL_HERE";

// PERSONAL & VISUAL SETTINGS
const FULL_NAME = "Samet Koseoglu"; 
const REPORT_TITLE = "WEEKLY TREND RADAR"; 
const BRAND_COLOR = “#000000”; 
const LOGO_URL = "https://www.sametkoseoglu.com/wp-content/uploads/2024/03/favicon.png";

// LANGUAGE & PERSONA SETTINGS
const LANGUAGE = "English"; // [LANGUAGE] - e.g., English, Turkish, German, French
const AI_PERSONA = "Art Director"; 
const TOPICS = "Graphic Design, AI, Art and Creative Strategy"; 

// NEWSLETTER STATIC TEXTS (Update these once based on your language)
const GREETING_TEXT = "Hello"; 
const BUTTON_TEXT = "REVIEW"; 
const STATS_TEXT = "This newsletter was curated by analyzing {NEWS} news items from {SOURCE} sources.";
const FOOTER_TEXT = "© 2026 | This report was specially curated by Gemini for {NAME}.";

/** * ==========================================
 * SYSTEM CORE (Do Not Touch)
 * ==========================================
 */

function runSystem() {
  fetchNews();
  clearOldNews();
  generateAndSendReport();
}

function fetchNews() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = ss.getSheetByName("Settings"); 
  const dataSheet = ss.getSheetByName("Data");
  const sources = settingsSheet.getRange("A:A").getValues();
  const media = XmlService.getNamespace('media', 'http://search.yahoo.com/mrss/');

  sources.forEach(row => {
    const url = row[0];
    if (url && url.startsWith("http")) {
      try {
        const xml = UrlFetchApp.fetch(url).getContentText();
        const document = XmlService.parse(xml);
        const channel = document.getRootElement().getChild('channel');
        const items = channel.getChildren('item');
        const sourceName = channel.getChildText('title') || url;
        
        // --- LOGO FALLBACK LOGIC ---
        // Get the site's main logo from the RSS channel
        let siteLogo = "";
        let channelImage = channel.getChild('image');
        if (channelImage) {
          siteLogo = channelImage.getChildText('url');
        }
        // ---------------------------

        items.slice(0, 5).forEach(item => {
          const title = item.getChildText('title');
          const link = item.getChildText('link');
          
          let articleImage = "";
          let enclosure = item.getChild('enclosure');
          if (enclosure) articleImage = enclosure.getAttribute('url').getValue();
          
          if (!articleImage) {
            let mediaContent = item.getChild('content', media) || item.getChild('thumbnail', media);
            if (mediaContent) articleImage = mediaContent.getAttribute('url').getValue();
          }

          // If article image is missing, use site logo
          const finalImage = articleImage || siteLogo;

          dataSheet.appendRow([new Date(), title, link, finalImage, sourceName]);
        });
      } catch(e) { console.log("Source Error: " + url); }
    }
  });
}
function clearOldNews() {
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const data = dataSheet.getDataRange().getValues();
  const now = new Date();
  for (let i = data.length - 1; i >= 1; i--) {
    const newsDate = new Date(data[i][0]);
    if ((now - newsDate) / (1000 * 60 * 60 * 24) > 7) dataSheet.deleteRow(i + 1);
  }
}

function generateAndSendReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const settingsSheet = ss.getSheetByName("Settings");
  if (dataSheet.getLastRow() < 2) return;
  
  const sourceCount = settingsSheet.getLastRow(); 
  const totalNewsCount = dataSheet.getLastRow();
  const allNews = dataSheet.getRange(2, 2, dataSheet.getLastRow() - 1, 4).getValues();
  const newsText = allNews.map(h => `Title: ${h[0]} | Link: ${h[1]} | Image: ${h[2]} | Source: ${h[3]}`).join("\n");
  
  const finalPrompt = `
    You are a world-renowned, critically acclaimed ${AI_PERSONA} with a sharp eye for curation. 
    PLEASE PREPARE ALL ANALYSIS AND TEXTS IN ${LANGUAGE}.
    
    STRATEGIC ASSESSMENT GUIDELINES:
    1. Focus on the core question: "What shifted this week?"
    2. ABSOLUTELY AVOID clichés like "In today's fast-changing world," "dynamic landscape," or "innovative horizons."
    3. Synthesize the news. Find the hidden connections between different items (e.g., how a tech update affects design aesthetics).
    4. If a specific theme dominates the data (e.g., AI Ethics, Minimalism, Sustainability), write the assessment as a "situation report" on that theme.
    5. Provide a "point of attention" or a "creative foresight" for fellow Art Directors based on this week's data.
    6. Tone should be sharp, intellectual, and information-dense. No fluff.
    
    STRATEGIC FOCUS: ${TOPICS}
    - Determine ONLY one most relevant category per news item.
    - Highlight important words using  word  tags.
    
    OUTPUT FORMAT (JSON):
    {
      "report_date": "Current date in ${LANGUAGE}",
      "general_assessment": "...",
      "news_items": [
        {"title": "...", "category": "...", "source": "...", "summary": "...", "link": "...", "image": "..."}
      ]
    }
    
    Data:
    ${newsText}
  `;
  
  const payload = { "contents": [{ "parts": [{ "text": finalPrompt }] }] };
  const options = { "method": "post", "contentType": "application/json", "payload": JSON.stringify(payload), "muteHttpExceptions": true };
  const url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=" + GEMINI_API_KEY;
  
  const res = UrlFetchApp.fetch(url, options);
  let aiResponse = JSON.parse(res.getContentText()).candidates[0].content.parts[0].text;
  let start = aiResponse.indexOf('{');
  let end = aiResponse.lastIndexOf('}'); 
  if (start !== -1 && end !== -1) aiResponse = aiResponse.substring(start, end + 1); 
  aiResponse = aiResponse.replace(/[\u0000-\u001F\u007F-\u009F]/g, " ");
  const reportData = JSON.parse(aiResponse);

  let newsHtml = "";
  reportData.news_items.forEach(h => {
    const imgContent = h.image ? `` : `
`;
    newsHtml += `
      

        

          
${imgContent}
        
        

          
${h.title}
          
${h.category} | ${h.source}
          
${h.summary}
          ${BUTTON_TEXT}
        
      
    `;
  });

  const statsOutput = STATS_TEXT.replace("{SOURCE}", sourceCount).replace("{NEWS}", totalNewsCount);
  const footerOutput = FOOTER_TEXT.replace("{NAME}", FULL_NAME);

  const htmlBody = `
    
      
        

          

            
            
${REPORT_TITLE}
            
${reportData.report_date} | ${TOPICS}
            
${statsOutput}
          
          

            

              
${GREETING_TEXT} ${FULL_NAME},
              
"${reportData.general_assessment}"
            
            ${newsHtml}
          
          

            ${footerOutput}
          
        
 
    
  `;
  
  MailApp.sendEmail({ to: EMAIL_ADDRESS, subject: ` ${REPORT_TITLE}: ${reportData.report_date}`, htmlBody: htmlBody });
}

Step 3: Trigger Setup

Actually, our system is ready right now. There are 3 commands inside the code block we just pasted. Now let’s test them:

1. Select the “fetchNews” command from the top section of the App Script page (Image 1.1) and click the run button. You will finally see that news data starts collecting in the Data sheet in your spreadsheet. When the process is finished, you will see the text “Execution completed” at the bottom.
2. Then, select the “generateAndSendReport” command and press the run button again. When the process finishes and you see the text “Execution completed”, your newsletter will reach your email within a few minutes.
3. The “clearOldNews” command will prevent our table from bloating by deleting news older than 1 week. However, since we just performed the installation, it is not possible to test this command right now.

Even though we are sure that all our commands work after testing, we need to set “Triggers” for them to work on their own. In this step, we will schedule each task separately.

Click on the Clock Icon (Triggers) on the left side of the Apps Script screen and define the following 3 different tasks using the “Add Trigger” button at the bottom right:

3.1. Task: Collecting News

Repeat this setting 3 times (for Tuesday, Thursday, and Sunday) so that we can collect all current news completely:

• Function to run: fetchNews
• Event Source: Time-driven
• Type of time based trigger: Weekly timer
• Day and Time:
  • Trigger: Tuesday – 13:00 – 14:00
  • Trigger: Thursday – 13:00 – 14:00
  • Trigger: Sunday – 23:00 – 00:00

3.2. Task: Data Cleaning (Memory Management)

We must delete old news so that your Spreadsheet does not bloat and stays up to date.

• Function to run: clearOldNews
• Event Source: Time-driven
• Type of time based trigger: Weekly timer
• Day and Time: Monday – 03:00 – 04:00

3.3. Task: Preparation and Sending of the Report

And the finale! Have your newsletter ready before you start work on Monday morning.

• Function to run: generateAndSendReport
• Event Source: Time-driven
• Type of time based trigger: Weekly timer
• Day and Time: Monday – 08:00 – 09:00

Summary

After setting the triggers, we have nothing left to do but wait for our newsletter to be prepared on Monday. To summarize briefly, the websites we determined at the beginning will be scanned on Tuesday at 13:00, Thursday at 13:00, and Sunday at 23:00, and current news will be collected in our spreadsheet. Before the new report is generated, news older than 1 week will be deleted on Monday at 03:00. At 08:00 on Monday, Gemini will review this news for you and deliver a newsletter consisting of weekly analysis and important news to you via email.

Conclusion: You Now Have a Personal News Center!

Congratulations! Now, every Monday morning, a newsletter specially prepared, visualized, and strategically analyzed for you by Gemini 2.5 will be in your email inbox. Thanks to this AI automation, you will both save time and be able to follow developments in your industry with a professional eye.

In connection with this topic, I recommend reading the article Artificial Intelligence Design Tools: The New Frontiers of Creativity.