Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created May 21, 2022 05:53
Show Gist options
  • Save tanaikech/0ba501fecdfd1ac9b0f6997b370586b4 to your computer and use it in GitHub Desktop.
Save tanaikech/0ba501fecdfd1ac9b0f6997b370586b4 to your computer and use it in GitHub Desktop.
Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script

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

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment