Please Help Me Solve this Google App Script Function

[KKKK]

I'm attempting to write an app script that will import data from Google Trends into Google Sheets. It is working well for the first column but not for the second and third columns. Please help me. Here is the code: https://docs.google.com/document/d/1wcu5LQXlPnQeXggM2MXH5NenGsz1NLxb0Nmaj1mxt5I/edit?usp=sharing

 

[thatstooeasyman]

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:

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!

 

[iamSaiph]

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:

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