0

I'm trying to extract the trending searches from Google Trends. The first column is working well. But the second and third columns are not providing outputs into Google Sheets. Here's the 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 = "Trends";

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

}

}

}

I have tried a lot to get the output into the google sheet. First column output is good. But I'm not getting output for the second and third columns.

TheWizEd
  • 7,517
  • 2
  • 11
  • 19
Games Deluxe
  • 1
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. In order to correctly understand your question, can you provide the sample output situations you expect? – Tanaike Feb 28 '23 at 23:21
  • This will be the output: https://docs.google.com/spreadsheets/d/13cG3ueCvWaugwulsUTP3jGXdhwLrefcEdg_gT2KLvwc/edit?usp=sharing Here, the "Title" Column is showing the result. But the "News Item Title" & the "News Item URL" column not showing the result. If you want to check it, Create a Google Sheets, Then click on extensions/google app script, after that paste this code on the app script. Save and run the project, you will see the output on the Sheet. – Games Deluxe Mar 01 '23 at 06:17
  • Thank you for replying. I have to apologize for my poor English skill again. Is your provided Spreadsheet your sample input situation? If my understanding is correct, where can I see your expected output situation? It seems that there are 2 values of `news_item_title` for each `title`. So, I cannot imagine your expected situation. First, I would like to correctly understand your question. I apologize that I am trying to correctly understand your question. – Tanaike Mar 01 '23 at 07:15
  • Ok, I'm trying to explain in detail. (https://trends.google.com/trends/trendingsearches/daily/rss?geo=US) this is the Google Trends RSS Feed URL. I want to extract the "Title", "News Item Title", "News Item URL" From the RSS Feed. See The screenshot: https://ibb.co/kcxrmY3 I want to save the output into Google Sheets. This is the output that I get from the script: https://ibb.co/nfM0fCv Here, the "Title" column has output. But the "News Item Title" & "News Item URL" has no output. I need to solve this issue. I hope you understand now. Please help me to solve this issue. Thank you. – Games Deluxe Mar 01 '23 at 16:23
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, from your reply, I cannot still understand your expected result. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. If you cannot understand my 2nd comment, please tell me. – Tanaike Mar 02 '23 at 01:16
  • In short, I want to see the daily trending searches into my google sheets. So that I can write articles for them. To write articles for a topic, I need to read the original article. For this reason, I need the URL of that article. I don't understand that, why you don't understand this matter. Think, if you want to write article on any topic, you need to read some articles around it before writing a new one. I want to automate this process through Google app script and Google Sheets. I want to see all the information in my google sheet. – Games Deluxe Mar 02 '23 at 08:51
  • If I can extract the whole information in the google sheet it would be much better. Can you write a kind of script, that will do the following: 1. Extract the google trends daily search result. 2. Then it will automatically store the data into google sheets. 3. In the first column of the sheet, there will be the search term. 4. In the second column, there will be the original article's Title. 5. In the third column, there will be the original Article URL, so that I can visit the article directly from the Google Sheets. – Games Deluxe Mar 02 '23 at 09:00

0 Answers0