09 May 2023 Poblar una hoja de cálculo con Apps Script

Seguramente, alguna vez te has encontrado que necesitas plasmar la información vía web de manera clara y sencilla. La obtención de los datos, su procesado, el almacenamiento y su disposición en cliente puede conllevar tiempo, sobre todo dependiendo del proyecto. En este post os mostraremos como poblar una hoja de cálculo con Apps Script. Por lo tanto, se usará JavaScript para rellenar la hoja de cálculo y actualizaremos la información pulsando un botón.

Para ello utilizaremos los datos sobre el total de población por municipio de Cataluña. El objetivo será identificar los micropueblos de manera automatizada para cada año. y guardaremos un historial de los años anteriores.

Por si no lo sabías, los micropueblos son aquellos municipios con menos de 500 habitantes. Para ver el estado actual de los micropueblos en Cataluña puedes visitar la web oficial del Departament de Territori. Una vez dicho esto ¡Prepárate, porque empezamos!

Abrir un Spreadsheet

Este paso no tiene secreto, solo tenéis que ir a docs.google y abrir un spreadsheet. El siguiente paso será ponerles nombre a nuestras hojas de cálculo. Para ello, nombra tu primera hoja como “Main” y crea otra con el nombre de “HistoryDB”

En nuestra hoja “Main”, hemos llenado las columnas “D” y “E” con el número de población por municipios de los años 2020 y 2021. Esto lo hemos hecho manualmente, ya que la API de Idescat no permite hacer consultas anteriores al año actual. Si quieres seguir paso por paso el post, te recomiendo que te descargues los datos y los enganches en estas columnas. Aquí tienes el enlace. El resultado tendría que quedar así:

A continuación, vamos a Extensiones y Apps Script.

Se abrirá el editor de código donde podremos crear nuestra función. Tienes que ver algo así:

Bien, antes de nada, podemos cambiarle el nombre a nuestra función. Como por ejemplo “updateData()”.  Si te fijas, Apps Script está diseñado para que codifiques propiamente con JavaScript, aunque también te permite utilizar HTML. Para este caso práctico, usaremos solo JS. En el menú superior, tienes una selección de acciones que puedes usar mientras vas redactando tu código para testear. Como, por ejemplo, Ejecutar o Depuración.

Núcleo del código

En este apartado empezaremos a ver el código que nos va a permitir descargar los datos y procesarlos. Lo primero que tenemos que hacer es guardar la relación directa con nuestras hojas de cálculo, en una variable. Para ello usamos:

var main = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");
var historyDB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HistoryDB");

Y hacemos un fetch de los datos de la API de Idescat y lo guardamos en «response»:

var response = UrlFetchApp.fetch("https://api.idescat.cat/emex/v1/dades.json?i=f171&tipus=mun,cat");

Tal como puedes ver, la petición se realiza especificando que queremos obtener los datos en formato json. “f171” corresponde al total de población. Y con «tipus» pedimos los datos de los municipios. Para más información sobre cómo realizar las consultas puedes dirigirte a este enlace.

Una vez hecho el fetch, convertiremos este JSON a un objeto JS y obtenemos el año que hacen referencia los datos. En este caso, la respuesta de nuestra petición será respecto los datos del año 2022.

var data = JSON.parse(response.getContentText());
var yearFile = data["fitxes"]["indicadors"]["i"]["r"];

A continuación, obtenemos el año que tenemos como título en la columna “E1” de nuestra hoja “Main”.

var oldYear = main.getRange("E1").getValue().match(/\d+/g);

Cláusula condicional para actualizar

Es importante que nos aseguremos de que los datos se pueden actualizar. Para ello usamos la siguiente condicional “if”:

 if (oldYear != null && oldYear[0] != yearFile){

Todo el bloque de código que veremos a continuación se engloba dentro del «if» anterior. En el caso de que se cumpla la condición que los nuevos datos sean más actuales de los que tenemos guardados, se ejecutará todo el código que veremos a continuación.

Iteraremos por los datos y los vamos guardando en diferentes listas.

    var totalPob = data["fitxes"]["indicadors"]["i"]["v"].split(',')
    var municipi = data["fitxes"]["cols"]
    var len = totalPob.length - 1

    var newPob = []
    var isMicro = []
    for (var i = 0; i <= len-1; i++) {
      newPob.push(totalPob[i])
      if (totalPob[i] <= 500){
        isMicro.push("TRUE")
      } else{
        isMicro.push("FALSE")
      }  
    };

Como podemos observar, primero guardamos en “totalPob” el total de población por municipios. En “municipi” guardamos los municipios con todos sus atributos y en “len” el total de población. En el bucle «for» iteramos por cada valor. En el caso de “newPob» vamos añadiendo en formato de lista todos los valores sobre el total de población por municipio. Mientras que en “isMicro” estamos comprobando si el total de población es menor o igual a 500 (es micropueblo) como “True”, y “False” si es lo contrario.

Lo siguiente que haremos es comprobar que en la primera columna de nuestra hoja “HistoryDB” hay los nombres de los municipios. En caso contrario los añadiremos.

    if (historyDB.getRange(1, 1).getValue() == ""){
        var nameColumn = historyDB.getRange('A1:A' + (names.length + 1));
        nameColumn.setValues([["Nom_Municipi"]].concat(names.map(function(name) {
          return [name];
        })));
    };

Seguidamente, comprobaremos si las columnas que muestran el id del municipio y el nombre ya están creadas.

    if (main.getRange(1, 1).getValue() == ""){
        var idColumn = main.getRange('A1:A' + (id.length + 1));
        idColumn.setValues([["Id_Municipi"]].concat(id.map(function(id) {
            return [id];
        })));
        var nameColumn = main.getRange('B1:B' + (names.length + 1));
        nameColumn.setValues([["Nom_Municipi"]].concat(names.map(function(name) {
            return [name];
        })));
    };

Desplazamos la columna “D” de nuestra hoja “Main” a la hoja de “HistoryDB”. Es decir, nos aseguramos de que los datos correspondientes al 2020 se guarden en nuestra segunda hoja donde tendremos un registro de todos los años con sus respectivos valores.

    var sourceRange = main.getRange(1, 4, main.getLastRow(), 1);
    var sourceData = sourceRange.getValues();
    historyDB.getRange(1, historyDB.getLastColumn() +1, sourceData.length, 1).setValues(sourceData);

Y movemos la columna «E», correspondiente a los datos de 2021, a la columna “D”.

    var rangeToMove = main.getRange(1, 5, main.getLastRow(), 1);
    rangeToMove.copyTo(main.getRange(1, 4, main.getLastRow(), 1));

Actualizar las columnas ya creadas

Actualizamos las columnas de si es o no micropueblo y el total de población por municipios actual (en este caso del 2022).

    var isMicroColumn = main.getRange('C1:C' + (isMicro.length + 1));
    isMicroColumn.setValues([["Micropoble"]].concat(isMicro.map(function(micropoble) {
      return [micropoble];
    })));
    var titol_any = "Poblacio_" + yearFile
    var newPobColumn = main.getRange('E1:E' + (newPob.length + 1));
    newPobColumn.setValues([[titol_any]].concat(newPob.map(function(population) {
      return [population];
    })));

Finalmente, calcularemos la diferencia de población entre el año actual y el anterior y veremos qué municipio ha perdido, o ha incrementado el número de población.

    var valuesOldYear = main.getRange(2, 4, main.getLastRow(), 1).getValues();
    var resultatDiferencia = [];
    var d = 0; 
    for (var i = 0; i <= len-1; i++) {
          var diff = newPob[i] - valuesOldYear[i]
          resultatDiferencia[d]=[]
          resultatDiferencia[d].push(diff);
          d++;
    };

Añadimos la columna que acabamos de calcular, que se encuentra almacenada en la lista “resultatDiferencia”.

    var diferencia = main.getRange('F1:F' + (resultatDiferencia.length + 1));
    diferencia.setValues([["Diferencia"]].concat(resultatDiferencia.map(function(diferencia) {
        return [diferencia];
    })));

Para destacar los resultados pintaremos de diferente color las celdas donde los valores son negativos de color rojo, los positivos en verde y los resultados que han dado 0, los dejaremos en blanco.

    for (var i = 0; i <= len-1; i++) {
        var diff_value = resultatDiferencia[i]
        if (diff_value < 0) {
          main.getRange(i+2,6).setBackground("#F43B0E")
        } else if (diff_value > 0) {
          main.getRange(i+2,6).setBackground("#34CB12")
        } else {
          main.getRange(i+2,6).setBackground("white")
        }
    }}};

Como puedes ver, poblar una hoja de cálculo con Apps Script es bastante sencillo. Con este código hemos podido obtener el total de población actual que hay en la API de Idescat y comparar los resultados con los del año anterior. Por último, se ha guardado el año 2020, en nuestra hoja “HistoryDB”.

Al apretar Ejecutar, podremos ver los cambios en nuestra hoja “Main”. Pero, antes de nada, vamos a crear un botón en nuestra hoja, para que al pulsarlo se ejecute esta función que acabamos de crear.

Crear un botón que ejecute la función

Ves a Insertar/Dibujo:

Se te abrirá una pantalla donde podrás confeccionar tu botón. En nuestro caso, hemos creado uno bien sencillo.

Guardamos. A continuación, nos pedirá si le queremos asignar una secuencia de comandos. Es decir, el nombre de nuestra función. Una vez puesto el nombre de la función que has creado clica en aceptar.

Deberías ver el botón que acabas de crear en tu hoja. Para ejecutar el botón, clica en él con el botón izquierdo. Para moverlo de un sitio a otro, clica con el botón derecho y arrástralo.

Para mostrarte el cambio que hubo del año pasado a este, te muestro como se veían los datos en nuestra hoja “Main” el año pasado.

Aquí tenemos los datos correspondientes a los años 2020 y 2021. Vemos que de los 10 primeros municipios, solo Abella de la Conca ha perdido población (-12), mientras que el resto ha ganado. También podemos ver que, de estos 10 municipios, dos son micropueblos, porque tienen menos de 500 habitantes.

Si ejecutamos el botón que acabamos de crear, el resultado es el siguiente:

Vemos que seguimos teniendo dos micropueblos, de los diez municipios que se nos muestran en pantalla. Pero, tenemos tres de ellos que han perdido población, y un micropueblo (Abella de la Conca) que sigue perdiendo población por segundo año consecutivo.

Y hasta aquí el caso práctico. Esperamos que os haya gustado el proceso de poblar una hoja de cáclulo con Apps Script. En un siguiente post, explicaremos como coger esta información y mostrarla en un visor web.