Please Help Me Solve this Google App Script Function

Joined
Mar 5, 2023
Messages
36
Reaction score
12
It seems that your code is correctly importing data from Google Trends into Google Sheets for the first column, but not for the second and third columns. Based on your code, it seems that you are trying to extract the "News Item Title" and "News Item URL" from each item in the RSS feed. However, it is possible that some of the items in the feed do not have these elements, which may explain why you are not getting data for the second and third columns.

To fix this, you can add some additional checks to make sure that the elements exist before trying to extract their text. Here is an updated version of the code that should work:


Code:
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
    }
  }
}

In this version of the code, I added some if statements to check if the "ht:news_item_title" and "ht:news_item_url" elements exist before trying to extract their text. If they do not exist, the corresponding variables are left as empty strings.

I hope this helps! :)
 
Joined
Mar 15, 2023
Messages
5
Reaction score
0
It looks like the code is only extracting data for the first column because the getChildren() method is only called on the item element, which only contains the title information. To extract the news item title and URL information, you'll need to call getChildren() on the newsItem element, and then call getText() on the child elements for the title and URL.

Here's the modified code that should extract data for all three columns:

Java:
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"));
      const newsItemTitle = newsItem ? newsItem.getChild("ht:news_item_title", XmlService.getNamespace("ht")).getText() : "";
      const newsItemUrl = newsItem ? newsItem.getChild("ht:news_item_url", XmlService.getNamespace("ht")).getText() : "";

      const row = [title, newsItemTitle, newsItemUrl];
      sheet.appendRow(row);

      count++;
      if (count === 10) break; // Stop after extracting 10 items
    }
  }
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,708
Messages
2,569,346
Members
44,650
Latest member
LuckyVivo

Latest Threads

Top