google-apps-scriptgoogle-sheetsgoogle-sheets-formularssfeed

Saving RSS Feed history using =IMPORTFEED and a Google Sheets Script


I am trying to build a database of the RSS Feed history of several feeds on Google Sheets. In order to do this, I am building on Digital Farmer's answer to the question Google sheet RSS feed automatically Update.

The provided answer retrieves only the article's title but I want to retrieve other valuable data like the article's url, the date, the feed's title and the feed's url.

The function Digital Farmer produced is as follows, which I have adapted to retrieve the data from the feeds I need:

function rssfeed() {
  var ss = SpreadsheetApp.getActive();
  //SiteOne
  ss.getRange('Import RSS!A1').setFormula('=IMPORTFEED("https://e-ficiencia.com/feed/","items Title",False,100)');
  ss.getRange('Import RSS!B1:B').copyTo(ss.getRange('History RSS!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Import RSS!A1').clear({contentsOnly: true, skipFilteredRows: true}); 
  //SiteTwo
  ss.getRange('Import RSS!A1').setFormula('=IMPORTFEED("https://www.climanoticias.com/feed/all","items Title",False,100)');
  ss.getRange('Import RSS!B1:B').copyTo(ss.getRange('History RSS!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Import RSS!A1').clear({contentsOnly: true, skipFilteredRows: true});
  //SiteThree
  ss.getRange('Import RSS!A1').setFormula('=IMPORTFEED("https://www.proinstalaciones.com/actualidad/noticias?format=feed","items Title",False,100)');
  ss.getRange('Import RSS!B1:B').copyTo(ss.getRange('History RSS!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Import RSS!A1').clear({contentsOnly: true, skipFilteredRows: true});
}

Expected behavior

Here's a link to an open spreadsheet where change may be made.


Solution

  • Getting Data from RSS feed to Google Sheet.

    I am with @Cooper in terms of how to tackle or get your desired result. I would suggest using URL Fetch App and XML services to get the context you are looking for. I have created a sample to get you going. You can run this on your sample sheet so you can see, how it works and get you further on your project. I would also suggest headers as markers of the result.

    Sample Code:

    Please keep in mind that there are still vague things on the data you want to get, this sample code you might need some modification to work with your project

    function getURLData() {
     
      var currentData = [];
      var urltoCheck = ["https://e-ficiencia.com/feed/", "https://www.climanoticias.com/feed/all","https://www.proinstalaciones.com/actualidad/noticias?format=feed"];
      for (var i = 0; i < urltoCheck.length; i++){
      var ficiencaData = UrlFetchApp.fetch(urltoCheck[i]);
      var xml = ficiencaData.getContentText()
      let response = XmlService.parse(xml);
      var root = response.getRootElement();
       let channel = root.getChild('channel');
      let items = channel.getChildren('item');
        items.forEach(item => {
          let title = item.getChild('title').getText();
          let pubDateb = item.getChild('pubDate').getText();
          let link = item.getChild('link').getText();
          currentData.push([title,pubDateb,link,urltoCheck[i]])
       
      });
    }
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = ss.getSheetByName("History RSS");
      var currentDataRange = sheet.getRange(sheet.getLastRow() + 1, 1, currentData.length, currentData[0].length);
      currentDataRange.setValues(currentData); 
      
    }
    

    Here is how it should look like:

    enter image description here

    References:

    XML Services

    URL Fetch Servces