Parsing XML Data in Google Apps Script using IMPORTXML

GasTips
Google Apps Script / spreadsheet

Gists

This is a sample flow for parsing XML data in Google Apps Script using IMPORTXML. Recently, it seems that ContentService.MimeType.XML has been removed by the Google side. By this, in the current stage, the XML data cannot be directly loaded by the Web Apps URL with IMPORTXML. From this current situation, I would like to introduce a workaround. In this workaround, the XML data in Google Apps Script is parsed by IMPORTXML of the built-in function of Google Spreadsheet. By this workaround, I thought that this will be useful for testing a custom XML data using IMPORTXML.

The flow of this workaround is as follows.

  1. Create a XML data in Google Apps Script on Google Drive as a XML file.
  2. Publicly share the XML file as a viewer.
  3. Create a formula of IMPORTXML for loading the XML data from the file.
    • In this case, the XML data is retrieved using an API key.

By this flow, the XML data can be parsed with IMPORTXML.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet.

Before you use this script, please set your API key to apiKey. About the method for retrieving API key, please check this official document. In this case, Drive API is used. So, after you got your API key, please enable Drive API at the API console. Please be careful about this.

const apiKey = "###"; // Please set your API key.

const getFormula = (fileId, apiKey) =>
  `=IMPORTXML("https://www.googleapis.com/drive/v3/files/${fileId}?alt=media&key=${apiKey}","//*")`;

// Update a XML file.
// When you want to update XML data, please use this function.
function secondRun() {
  const updateXmlData =
    '<?xml version="1.0" encoding="UTF-8"?><sample>Updated sample value</sample>'; // Please set updated XML data.

  const fileId = PropertiesService.getScriptProperties().getProperty("fileId");
  if (!fileId) {
    throw new Error("No file ID.");
  }
  DriveApp.getFileById(fileId).setContent(updateXmlData);
  const formula = getFormula(fileId, apiKey);
  const range = SpreadsheetApp.getActiveSheet().getRange("A1");
  range.clearContent();
  SpreadsheetApp.flush();
  range.setFormula(formula);
}

// First, please run this function. By this, a XML file is created and a formula is put to the cell "A1".
// Create a file on Google Drive as a XML file.
function firstRun() {
  const sampleXml =
    '<?xml version="1.0" encoding="UTF-8"?><sample>sample value</sample>'; // Please set XML data.

  const file = DriveApp.createFile(
    "sampleA3.xml",
    sampleXml,
    "application/xml"
  );
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  const fileId = file.getId();
  const formula = getFormula(fileId, apiKey);
  SpreadsheetApp.getActiveSheet().getRange("A1").setFormula(formula);
  PropertiesService.getScriptProperties().setProperty("fileId", fileId);
}

  • First, please run firstRun(). By this, a XML file is created and a sample formula is put to the cell “A1” of the active sheet.

  • When you want to change XML data, please run secondRun(). By this, the XML file is updated, and the formula is also updated and new XML data is loaded.

Note

  • This is a simple sample script for explaining this workaround. So, please modify this script for your actual situation.

 Share!