CODEWITHSUNDEEP
javascriptxmlgoogle-apps-scriptgoogle-sheetsrss
Data Science Acolyte
Data Science Acolyte

Reputation: 333

Creating a Function to Process an RSS Feed in Google Sheets

I am trying to create a function that I can import into Google Sheets to view the latest bills from this website. A problem that I am having is that when I create only one variable to be appended to the Google Sheet this code will work and append the first cell. But when I create multiple variables using the same logic, but for different parts of the xml file that this link brings you to, it gives me this error even when I create completely different variables for the original document and root: TypeError: Cannot read property 'getValue' of null. Would anyone be able to show me what I am doing wrong so that I can at least get it so that all of these items can be appended to the Google Sheet through solving for this error and show me a way to do a loop to get all these items?

function getData() {
//get the data from boardgamegeek
var url = 'https://legis.delaware.gov/rss/RssFeeds/IntroducedLegislation';
var xmlrss = UrlFetchApp.fetch(url).getContentText();

var document = XmlService.parse(xmlrss);
var root = document.getRootElement();

//Clear out existing content

var sheet = SpreadsheetApp.getActiveSheet();
var rangesAddressesList = ['A:F'];
sheet.getRangeList(rangesAddressesList).clearContent();

//set variables to data from rss feed
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var title = root.getChild('channel').getChild('item').getChild('title').getText();
var session = root.getChild('channel').getChild('item').getChild('derss:legislativeSession').getText();
var link = root.getChild('channel').getChild('item').getChild('link').getText();
var category = root.getChild('channel').getChild('item').getChild('category').getText();
var description = root.getChild('channel').getChild('item').getChild('description').getText();
var pubDate = root.getChild('channel').getChild('item').getChild('pubDate').getText();

sheet.appendRow(["Session", "Title", "Category", "Pub Date", "Description", "Link"]);
sheet.appendRow([session, title, category, pubDate, description, link]);
}

Upvotes: 0

Views: 686

Answers (1)

Tanaike
Tanaike

Reputation: 201338

I believe your goal as follows.

  • You want to retrieve the values of legislativeSession, title, category, pubDate, description, link in order using Google Apps Script.
  • You want to put the retrieved values to Google Spreadsheet.

Modification points:

  • In the case of derss:legislativeSession, derss is the name space. So in this case, it is required to use the name space.
  • When I saw your XML data, there are many item tags. But in your script, 1st item is trying to be retrieved.
  • When the values from all items are retrieved, when appendRow is used in a loop, the process cost will become high.

When above points are reflected to your script, it becomes as follows.

Modified script:

function getData() {
  var url = 'https://legis.delaware.gov/rss/RssFeeds/IntroducedLegislation';
  var xmlrss = UrlFetchApp.fetch(url).getContentText();

  // Set the object for retrieving values in order.
  var retrieveNames = {legislativeSession: "Session", title: "Title", category: "Category", pubDate: "PubDate", description: "Description", link: "Link"};

  // Parse XML data.
  var document = XmlService.parse(xmlrss);
  var root = document.getRootElement();
  
  // Retrieve itmes.
  var item = root.getChild('channel').getChildren("item");

  // Retrieve name space of "derss".
  var derssNs = root.getChild('channel').getNamespace("derss");

  // By retrieving values from each item, create an array for putting values to Spreadsheet.
  var values = item.reduce((ar, e) => ar.concat(
    [Object.keys(retrieveNames).map(k => e.getChild(...(k == "legislativeSession" ? [k, derssNs] : [k])).getText())]
  ), [Object.values(retrieveNames)]);

  // Put the created array to Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangesAddressesList = ['A:F'];
  sheet.getRangeList(rangesAddressesList).clearContent();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • In this modified script, it supposes that the active sheet is the 1st sheet. If your actual situation is different, please modify above script.

References:

Upvotes: 1

Related Questions