Parsear un XML en Google Sheet

En este post vamos a ver cómo podemos descargar y parsear un fichero XML alojado en Internet para posteriormente poder hacer algún análisis típico (buscar por fecha, contar, etc)

En concreto el fichero a tratar va a ser un XML que publica el Ayuntamiento de Madrid con las incidencias que se han producido en la M30 (calle que circunvala la ciudad) alojado en http://www.mc30.es/images/xml/historicousuarios.xml

Preparación

En una hoja en blanco de Google Sheet seleccionaremos la opción Herramientas/Editor de secuencia de comandos lo cual nos abrirá un tab nuevo con un IDE propio de Google y una función mínima ya escrita.

este código lo reemplazaremos por el código siguiente (que explicaremos a continuación):

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createAddonMenu().addItem('Historico M30', 'dumpM30').addToUi();     
}

function populateHeader(sheet){
   sheet.appendRow([ "Fecha", "Vehiculos","VelocidadMedia","DistanciaMedia","TiempoMedio" ]);
}


function dumpM30(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.clearContents();

  populateHeader(sheet);

  var url = 'http://www.mc30.es/images/xml/historicousuarios.xml';   
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);


  var entries = document.getRootElement().getChildren('Historico');
  var entriesArray = Array.prototype.slice.call(entries, 0);

  var pattern = /(\d{2})\/(\d{2})\/(\d{4})/;
  entriesArray.sort(function(a,b) {
    var aCat = new Date( a.getChild("Fecha").getText().replace(pattern,'$3-$2-$1')); 
    var bCat = new Date( b.getChild("Fecha").getText().replace(pattern,'$3-$2-$1')); 
    if (aCat > bCat) return 1;
    if (aCat < bCat) return -1;
    return 0;
  });

  for (var i = 0; i < entries.length; i++) {    
    var item = entriesArray[i];    
    var fecha = entriesArray[i].getChild('Fecha').getText();
    var usuariosCalle30 = entriesArray[i].getChild('UsuariosCalle30').getText().split(' ')[0];    
    var velocidadMedia = entriesArray[i].getChild('velocidadMedia').getText().replace('.',',').split(' ')[0];    
    var distanciaMediaRecorrida = entriesArray[i].getChild('distanciaMediaRecorrida').getText().split(' ')[0];    
    var tiempoMediodeRecorrido = entriesArray[i].getChild('tiempoMediodeRecorrido').getText().replace(/ /g,'').replace('.',',').replace('min','').replace('seg.','');

    sheet.appendRow([       fecha,usuariosCalle30,velocidadMedia,distanciaMediaRecorrida,tiempoMediodeRecorrido
    ]);

  }
}

y guardaremos el proyecto como HistoricoM30 (o el nombre que prefieras).

Nueva opción de menú

Cuando la hoja se inicialize Google llamaará al método onOpen y nosotros añadiremos una opción en el menú principal con una subopción dumpM30 que el usuario podrá seleccionar.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createAddonMenu().addItem('Historico M30', 'dumpM30').addToUi();     
}

Descarga de XML

Para descargar un fichero usaremos:

  var url = 'http://www.mc30.es/images/xml/historicousuarios.xml';   
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);

  var entries = document.getRootElement().getChildren('Historico');

de esta manera la variable entries nos permitirá acceder a todos los hijos del elemento raiz Historico. Para hacer más cómodo la iteración de estos elementos lo convertiremos a un array de Javascript

var entriesArray = Array.prototype.slice.call(entries, 0);

Tratamiento

A partir de aquí el tratamiento es sencillo:

• ordenamos el array por la fecha
• para cada registro extraemos los campos de interés y los vamos añadiendo fila a fila

Usuario

Una vez que hayamos guardado el proyecto deberemos refrescar la hoja para que nos aparezca la opción en el menú principal (al refrescar, automáticamente se cerrará el tab del IDE así que asegurate de haber guardado bien todo antes por si acaso)

Cuando seleccionemos la nueva opción, nuestro script empezará a insertar filas en la hoja (debido al elevado número de registros del fichero esto tardará un buen rato), tal como muestra la imagen: