Automate News Collection with Google Apps Script: Fetch Daily Updates from Google News to Google Sheets

Retrieve Country, Category and Keywords

function fetchGoogleNewsByCountryAndSection() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("News");

  // Retrieve country, section, and keyword values from the spreadsheet
  const country = sheet.getRange("B1").getValue().toString().toUpperCase(); // Example: "JP"
  const section = sheet.getRange("B2").getValue().toString().toUpperCase(); // Example: "WORLD"
  const keyword = sheet.getRange("B3").getValue().toString(); // Example: "Technology"

• Refer to the spreadsheet sheet name “News”.
• Get the country (e.g., JP), category (e.g., WORLD), and keyword (e.g., technology) from the B1, B2, and B3 cells in the spreadsheet.
• Obtains the country code and determines the language to be displayed based on it (e.g., English for US, Japanese for JP).

Composition of RSS Feed

  // Set the language based on the country code
  let language = 'en'; // Default is English
  if (country === 'JP') {
    language = 'ja';
  } else if (country === 'FR') {
    language = 'fr';
  } else if (country === 'DE') {
    language = 'de';
  } else if (country === 'ES') {
    language = 'es';
  } else if (country === 'IT') {
    language = 'it';
  } else if (country === 'KR') {
    language = 'ko';
  } else if (country === 'CN') {
    language = 'zh-CN';
  }

  // Construct the RSS feed URL
  const rssUrl = `https://news.google.com/rss/headlines/section/topic/${section}?hl=${language}&gl=${country}&ceid=${country}:${language}`;

• Create a Google News RSS feed URL based on the country and category information retrieved.
• By dynamically setting the hl (language) and gl (region) parameters, news from a specific country can be retrieved in a specific language.

Retrieving News Articles

  // Fetch the RSS feed
  const response = UrlFetchApp.fetch(rssUrl);
  const xml = XmlService.parse(response.getContentText());
  const root = xml.getRootElement();
  const channel = root.getChild("channel");
  const items = channel.getChildren("item");

• Use the UrlFetchApp.fetch() function to retrieve news data from the RSS feed URL you created.
• The data retrieved in XML format is parsed to extract news titles, links, publication dates, etc.

Writing to Spreadsheets

  // Write news article data (title, link, publication date) to the sheet
  let rowIndex = 6;
  items.forEach(item => {
    const title = item.getChildText("title");
    const link = item.getChildText("link");
    const pubDate = item.getChildText("pubDate");

    // Keyword filtering (only apply if a keyword is provided)
    if (keyword && !title.includes(keyword)) {
      return; // Skip if the title does not contain the keyword
    }

    sheet.getRange(rowIndex, 1).setValue(title);  // Title (column A)
    sheet.getRange(rowIndex, 2).setValue(link);   // Link (column B)
    sheet.getRange(rowIndex, 3).setValue(pubDate); // Publication date (column C)
    rowIndex++;
  });
}

• Write the retrieved news article data in cell A6 and after in the spreadsheet.
• Existing data is cleared and newly acquired news information is written in sequence.
• If a keyword is set, only articles that contain that keyword in the news title will be displayed.

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.