function extractTrendingSearches() {
// 1. Visit this url: https://trends.google.com/trends/trendingsearches/daily/rss?geo=US
const url = "https://trends.google.com/trends/trendingsearches/daily/rss?geo=US";
// Fetch the RSS feed
const response = UrlFetchApp.fetch(url);
const feed = XmlService.parse(response.getContentText());
const items = feed.getRootElement().getChildren("channel")[0].getChildren("item");
// Get the sheet to store the data
const sheetName = "Daily Trending Searches";
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
// Get the existing data in the sheet
const dataRange = sheet.getDataRange();
const dataValues = dataRange.getValues();
// Extract unique titles from existing data
const existingTitles = new Set();
dataValues.forEach(row => {
const title = row[0];
if (title) existingTitles.add(title);
});
// Clear only the first three columns
sheet.getRange(1, 1, dataValues.length, 3).clearContent();
// Add headers to the sheet if the first row is empty
if (sheet.getRange("A1").getValue() === "") {
const headers = ["Title", "News Item Title", "News Item URL"];
sheet.appendRow(headers);
}
// Extract the data from each item and add it to the sheet if it is not already in the sheet
let count = 0;
for (let i = 0; i < items.length; i++) {
const item = items[i];
const title = item.getChild("title").getText();
if (!existingTitles.has(title)) {
const newsItem = item.getChild("ht:news_item", XmlService.getNamespace("ht"));
let newsItemTitle = "";
let newsItemUrl = "";
if (newsItem) {
const titleElement = newsItem.getChild("ht:news_item_title", XmlService.getNamespace("ht"));
if (titleElement) {
newsItemTitle = titleElement.getText();
}
const urlElement = newsItem.getChild("ht:news_item_url", XmlService.getNamespace("ht"));
if (urlElement) {
newsItemUrl = urlElement.getText();
}
}
const row = [title, newsItemTitle, newsItemUrl];
sheet.appendRow(row);
count++;
if (count === 10) break; // Stop after extracting 10 items
}
}
}