Build an AI topic-to-content automation with Google Sheets, Gemini and Telegram.

What does this automation actually prove?

The lab proves that a configuration value can control a complete automated workflow without requiring the learner to edit JavaScript for every topic. Apps Script reads the topic and supporting settings from a spreadsheet. It retrieves matching entries from a live Google News RSS feed, sends selected source information to Gemini, formats a Telegram message, publishes it and records the result in a separate log sheet.

Call it a topic-to-content automation or a live-feed classroom automation. Do not call it a real-time trend detector, a verified news-analysis engine or a system that guarantees the latest story. The feed can include older items, and one headline does not provide enough evidence for strong factual conclusions.

The six-stage workflow

The spreadsheet controls the workflow, Apps Script coordinates it, external services perform defined jobs, and the log preserves evidence. A scheduled trigger comes last, after every stage succeeds manually.

Set up the workbook and protect credentials

CONFIG sheet

Changing CONFIG!B2 later should change the fetched sources and generated output without any code edit. That is the simplest proof that configuration and implementation have been separated.

CONTENT_LOG sheet

Create these headings in row 1: Date, Topic, Source Headline, Source URL, Generated Headline, Post, Hashtags, Image Prompt and Status. The final function writes both successful and failed runs here. The Image Prompt field stores text only. This classroom version does not call an image-generation service.

Script Properties

Open Apps Script, then Project Settings and Script Properties. Add GEMINI_API_KEY, TELEGRAM_BOT_TOKEN and TELEGRAM_CHAT_ID. Never place real values in source code, screenshots, repositories, slides or classroom chats. Google documents Script Properties as a store for application-wide configuration, including credentials. For stricter production requirements, use a dedicated secret-management service and a documented rotation process.

GEMINI_API_KEY       = replace_with_your_key
TELEGRAM_BOT_TOKEN   = replace_with_the_BotFather_token
TELEGRAM_CHAT_ID     = replace_with_the_numeric_chat_id

The token value does not start with the word bot. Telegram's URL adds that prefix: https://api.telegram.org/bot${botToken}/sendMessage.

Use the corrected build order

The original classroom handout placed the fetch test before the fetch helper. Students then ran a function that called code that did not yet exist. The corrected sequence always defines a helper before the test that calls it.

Fetch topic-related stories before testing the fetch

function fetchRecentStories(topic) {
  const query = encodeURIComponent(topic);
  const url = `https://news.google.com/rss/search?q=${query}` +
    `&hl=en-NG&gl=NG&ceid=NG:en`;

  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() !== 200) {
    throw new Error(`News fetch failed: HTTP ${response.getResponseCode()}`);
  }

  const channel = XmlService.parse(response.getContentText())
    .getRootElement().getChild('channel');
  const items = channel.getChildren('item');

  return items.slice(0, 8).map(item => ({
    title: item.getChildText('title'),
    link: item.getChildText('link'),
    published: item.getChildText('pubDate')
  }));
}

function testFetchRecentStories() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('CONFIG');
  if (!sheet) throw new Error('CONFIG sheet was not found.');
  const topic = sheet.getRange('B2').getValue().toString().trim();
  if (!topic) throw new Error('Enter a topic in CONFIG!B2.');
  const stories = fetchRecentStories(topic);
  Logger.log(`STORIES FOUND: ${stories.length}`);
  stories.forEach((story, index) => Logger.log(`${index + 1}. ${story.title}`));
}

Run testFetchRecentStories(), not the helper. A successful fetch proves that the URL responded and the XML could be parsed. It does not prove freshness, importance or factual completeness.

Give the generator data and strict factual boundaries

The generator is a helper. Its first job should be rejecting a direct run with a useful error instead of allowing a vague JavaScript failure.

function generateContentWithGemini(data) {
  if (!data) {
    throw new Error(
      'Do not run generateContentWithGemini directly. ' +
      'Run testTrendToContent or runContentBot instead.'
    );
  }
  if (!data.stories || !data.stories.length) {
    throw new Error('No stories were supplied to Gemini.');
  }

  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('GEMINI_API_KEY');
  if (!apiKey) throw new Error('GEMINI_API_KEY is missing.');

  // Verify a currently supported model in the official Gemini documentation.
  const model = 'gemini-3.5-flash';
  const url = `https://generativelanguage.googleapis.com/v1beta/models/` +
    `${model}:generateContent`;

  const sourceText = data.stories.map((story, i) =>
    `${i + 1}. ${story.title}\nPublished: ${story.published}\nURL: ${story.link}`
  ).join('\n\n');

  const prompt = `Create one short social post from the supplied headlines.
Treat each supplied headline as the only verified factual information.
Preserve attribution. Do not invent statistics, quotations, causes,
predictions or conclusions. Clearly frame commentary as commentary.

Topic: ${data.topic}
Country/context: ${data.country}
Audience: ${data.audience}
Tone: ${data.tone}

Sources:\n${sourceText}

Return JSON with sourceIndex, headline, post, hashtags and imagePrompt.`;

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-goog-api-key': apiKey },
    payload: JSON.stringify({
      contents: [{ parts: [{ text: prompt }] }],
      generationConfig: { responseMimeType: 'application/json' }
    }),
    muteHttpExceptions: true
  });
  if (response.getResponseCode() !== 200) {
    throw new Error(`Gemini failed: HTTP ${response.getResponseCode()}`);
  }

  const body = JSON.parse(response.getContentText());
  const generated = JSON.parse(body.candidates[0].content.parts[0].text);
  let sourceIndex = Number(generated.sourceIndex) - 1;
  if (sourceIndex < 0 || sourceIndex >= data.stories.length) sourceIndex = 0;
  const source = data.stories[sourceIndex];

  return {
    sourceHeadline: source.title,
    sourceUrl: source.link,
    headline: generated.headline,
    post: generated.post,
    hashtags: generated.hashtags,
    imagePrompt: generated.imagePrompt
  };
}

Structured JSON helps the next function find required fields, but valid JSON does not guarantee factual or suitable content. Google explicitly recommends validating structured output against application requirements. Check the source selection, attribution, length, required fields and prohibited claims before delivery.

Only the complete bot publishes and writes CONTENT_LOG

Test Telegram separately first. Send the new bot a message before calling getUpdates, then obtain the numeric chat ID from the returned update. Telegram documents getUpdates and sendMessage as Bot API methods. Keep the test destination private while learning.

function sendToTelegram(message) {
  if (!message) throw new Error('A Telegram message is required.');
  const props = PropertiesService.getScriptProperties();
  const botToken = props.getProperty('TELEGRAM_BOT_TOKEN');
  const chatId = props.getProperty('TELEGRAM_CHAT_ID');
  if (!botToken || !chatId) throw new Error('Telegram settings are missing.');

  const response = UrlFetchApp.fetch(
    `https://api.telegram.org/bot${botToken}/sendMessage`,
    {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify({ chat_id: chatId, text: message }),
      muteHttpExceptions: true
    }
  );
  if (response.getResponseCode() !== 200) {
    throw new Error(`Telegram failed: HTTP ${response.getResponseCode()}`);
  }
}

function writePublishedRow(logSheet, topic, content) {
  logSheet.appendRow([
    new Date(), topic, content.sourceHeadline, content.sourceUrl,
    content.headline, content.post, content.hashtags,
    content.imagePrompt, 'PUBLISHED'
  ]);
}

The complete orchestration function

function runContentBot() {
  const spreadsheet = SpreadsheetApp.getActive();
  const configSheet = spreadsheet.getSheetByName('CONFIG');
  const logSheet = spreadsheet.getSheetByName('CONTENT_LOG');
  if (!configSheet) throw new Error('CONFIG sheet was not found.');
  if (!logSheet) throw new Error('CONTENT_LOG sheet was not found.');

  const topic = configSheet.getRange('B2').getValue().toString().trim();
  const tone = configSheet.getRange('B3').getValue().toString().trim();
  const audience = configSheet.getRange('B4').getValue().toString().trim();
  const country = configSheet.getRange('B5').getValue().toString().trim();
  if (!topic) throw new Error('Enter a topic in CONFIG!B2.');

  try {
    const stories = fetchRecentStories(topic);
    if (!stories.length) throw new Error('No stories found for this topic.');

    const content = generateContentWithGemini({
      topic, tone, audience, country, stories
    });
    const telegramMessage = `${content.headline}\n\n` +
      `${content.post}\n\n${content.hashtags}\n\n` +
      `Source: ${content.sourceHeadline}`;

    sendToTelegram(telegramMessage);
    writePublishedRow(logSheet, topic, content);
    Logger.log('FULL AUTOMATION COMPLETED SUCCESSFULLY');
  } catch (error) {
    logSheet.appendRow([
      new Date(), topic, '', '', '', '', '', '',
      `FAILED: ${error.message}`
    ]);
    throw error;
  }
}

runContentBot() reads CONFIG, calls the fetch helper, supplies the full data object to Gemini, formats the result, sends it to Telegram and writes the final status. Never log tokens, API keys or full sensitive payloads.

Run the complete bot manually and check both Telegram and CONTENT_LOG. Change only CONFIG!B2, run it again and confirm that the source and output change. Add a time-driven trigger only after those checks pass. Delete any five-minute demonstration trigger afterward so it does not continue posting.

Three classroom errors and what they reveal

Two Telegram checks

An empty {"ok":true,"result":[]} response usually means the bot has not received a message yet, or there are no pending updates. Send the bot a message and call getUpdates again. If delivery fails, confirm that the Script Property contains the raw BotFather token without a bot prefix and that the numeric chat ID is present.

Accuracy, safety and production limitations

• Google News RSS can return older entries. Add an explicit date threshold before claiming recency.
• A headline is limited evidence. Public or consequential content needs source review and, where appropriate, verification against the original publisher and additional authoritative sources.
• The classroom version produces an image prompt as text. It does not generate or publish an image.
• The basic version has no human approval queue. Use a private Telegram destination during training and add an approval gate before public business publishing.
• A production system needs rate-limit handling, retry limits, duplicate protection, monitoring, retention rules and a credential-rotation process.
• Do not process private student, customer or employee information in this exercise.

Gemini model names, API versions, quotas and response options can change. Check the current Gemini API documentation before teaching or running the lab. The Telegram Bot API also evolves, so validate endpoint requirements against its official reference.

Official references: Gemini generateContent API, Gemini structured output, Apps Script Properties Service, and Telegram Bot API.

Student assignment and completion evidence

1. Build CONFIG and CONTENT_LOG with the fields shown above.
2. Store placeholder-free credentials in Script Properties and test each service independently.
3. Run the full bot manually and show the Telegram output beside its matching CONTENT_LOG row.
4. Change only the topic cell and demonstrate a different source and generated result.
5. Explain why the two helper functions should not be run directly.
6. Explain why HTTP 200 from Gemini does not prove that Telegram or the spreadsheet step succeeded.
7. Create one time-driven test run, capture the evidence, then remove the short-interval trigger.
8. Identify one claim in the generated post that still requires human review.

A complete submission includes the code, redacted screenshots, the relevant log row and a short explanation of each stage. Remove or conceal API keys, bot tokens, chat IDs, email addresses and unrelated personal information before sharing.

Frequently asked questions

Why is fetchRecentStories not defined?

The fetch test ran before the helper existed in the saved project. Paste and save the helper first, then run the test function.

Why does the generator say stories is undefined?

You ran a helper that expects a data object. Run the test or main orchestration function so it supplies the topic, settings and stories.

Why can Gemini return HTTP 200 while CONTENT_LOG remains empty?

The connection or generation test does not write to the sheet. The complete runContentBot() workflow contains the logging step.

Does this automation generate images?

No. It stores a text prompt for a possible future image workflow. Image generation and Telegram sendPhoto are outside the classroom Codes 1–8.

Does the feed prove that a story is trending?

No. It returns topic-related entries and may include older stories. Rigorous trend detection requires freshness rules, stronger signals and verification.

Continue learning

Use the Automation Workflow Design guide to add ownership, duplicate control, recovery and monitoring. Use the AI and Prompt Engineering guide to improve task definition, context, constraints and review. The JENECONK AI Training Academy connects these ideas to guided practice and assessed assignments.