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

submit.md

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.

Update: Modified version of the script which keeps inserting new posts after the header row.

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() {
  // NEW line to get the last feed update
  const lastUpdate = new Date(PropertiesService.getScriptProperties().getProperty('lastUpdate'));

  // 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 && published > lastUpdate) { // NEW extra condition to push only newer items
          ar.push([
            published,
            link.getAttribute("title").getValue(),
            link.getAttribute("href").getValue(),
          ]);
        }
        return ar;
      }, []),
  ];

  // Put the values to Spreadsheet.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  // NEW if new feed items insert rows factoring in Update and Header rows
  if (values.length > 2) {
    sheet.insertRowsAfter(2, values.length-2)
  }
  
  sheet
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
  
  // NEW store the feed update
  PropertiesService.getScriptProperties().setProperty('lastUpdate', update);
}

• When this script is run, the situation of the above sample image is obtained.