Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet / Google workspace

Gists

This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.

At Google Workspace Update Blog, the XML data is provided. By this, the retrieved XML data is parsed with XmlService, and the data is put to Google Spreadsheet. Recently, I got a request for this. So I created this sample script. When this was useful for your situation, I’m glad.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set the sheet name of the Spreadsheet. And, please run the function with the script editor. By this, the XML data is retrieved from Google Workspace Update Blog and it is parsed with XmlService, and the data is put to the Spreadsheet.

function myFunction() {
  // Retrieve and parse XML data from Google Workspace Update Blog.
  const url = "http://feeds.feedburner.com/GoogleAppsUpdates";
  const res = UrlFetchApp.fetch(url);
  const root = XmlService.parse(res.getContentText()).getRootElement();
  const ns1 = root.getNamespace();
  const update = root.getChild("updated", ns1).getValue();
  const values = [
    ["Update", new Date(update), ""],
    ["Published", "Title", "Link"],
    ...root
      .getChildren("entry", ns1)
      .reduce((ar, e) => {
        const published = new Date(e.getChild("published", ns1).getValue());
        const link = e
          .getChildren("link", ns1)
          .find((f) => f.getAttribute("rel").getValue() == "alternate");
        if (link) {
          ar.push([
            published,
            link.getAttribute("title").getValue(),
            link.getAttribute("href").getValue(),
          ]);
        }
        return ar;
      }, [])
      .reverse(),
  ];

  // Put the values to Spreadsheet.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}
  • When this script is run, the situation of the above sample image is obtained.

 Share!