Parsear un XML en Google Sheet
Tags: opendata, google, sheet, excel
Published: 2019-10-12 13:03:17

---
title: Parsear un XML en Google Sheet
published: true
description: Ejemplo de cómo descargar un XML y parsearlo en Google Sheet
tags: opendata, google, sheet, excel
---

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:

![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/ey59wcwvs8zfi2zmb4pa.png)