Crea un mapa de calor de datos en una hoja de cálculo usando una macro de ONLYOFFICE

En el análisis de datos, reconocer las variaciones en los valores numéricos de un vistazo es crucial para la toma de decisiones. Formatear manualmente cada celda puede ser un proceso lento y propenso a errores. Esta macro de ONLYOFFICE resuelve ese problema ajustando dinámicamente los colores de las celdas, lo que facilita la interpretación eficiente de datos complejos.

Creación de la macro

var sheet = Api.GetActiveSheet();
var range = sheet.GetSelection();
var data = range.GetValue();

Para comenzar, inicializamos la hoja activa, la selección y el rango dentro de las variables, sheet, range y data, respectivamente.

Manejo de errores “No se seleccionaron datos”

Justo después de esto, verificamos si realmente se ha seleccionado algún dato. Si no se ha seleccionado nada, terminamos la ejecución de la macro con un mensaje adecuado: “ No se seleccionaron datos”.

//We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }

Extracción de los índices de filas y columnas a partir de la selección

Si hay datos presentes, procedemos a obtener sus parámetros—los índices de inicio y fin de columnas, e inicio y fin de filas.

//Indexes indicating where rows and columns start and end
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

Hay muchas formas de obtener estos índices, pero una de ellas se muestra en el siguiente fragmento de código:

• range.GetCells() – accedemos a las celdas de la selección, y al añadir .Row() o .Col(), obtenemos el índice de la primera fila y la primera columna.
• Una vez que tenemos eso, podemos usar data.length, que nos da la cantidad de filas en nuestros datos. Luego, sumamos eso a la variable firstRowIndex para obtener el índice de la última fila. Hacemos lo mismo con las columnas, usando data[0].length para obtener el número de columnas y luego sumamos el valor de firstColIndex.

¿Por qué data[0]?

Como no sabemos cuántas filas tenemos, y cada data[0], data[1], etc., representa una fila, lo único de lo que podemos estar seguros es de que nuestros datos siempre tendrán al menos una fila. Esto significa que data[0] siempre será válido si la veriable data contiene algún valor.

Recolección de números desde las celdas seleccionadas de la hoja de cálculo

A continuación, creamos la matriz values y la llenamos con los números de nuestra selección.

var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

 

Después de inicializar el array de valores, interamos a través de cada celda en nuestra selección.

Por defecto, el valor de cada celda se trata como una cadena de texto, por lo que, cuando comprobamos si el valor es un número, primero necesitamos usar el método parseFloat() para convertirlo en un número.

Si la cedana dentro de la celda representa un número, parseFloat lo convertirá en un número. Si no, lo convertirá en un NaN (no es un número).

La función isNaN(…) verifica si el valor dentro de los paréntesis no es un número. Si colocamos un “!” antes de isNaN(…), lo que hacemos es comprobar si es un número.

Si el valor es un número, inicializamos la variable de valor y almacenamos el número en ella. Luego agregamos este valor la matriz  values.

Después de la iteración, terminamos con la matriz  values, que contiene todos los números de las celdas seleccionadas.

La razón por la que necesitamos esta matriz es para poder encontrar el número mínimo y máximo de las celdas seleccionadas usando los métodos de JavaScript Math.min() y Math.max().

Encontrando el valor mínimo y máximo

//Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

Aplicando colores

Ahora que tenemos toda la información necesaria, podemos aplicar colores personalizados a las celdas que contienen números.

Ya tenemos tanto el número mínimo como el máximo de las celdas seleccionadas, así que podemos iterar por las celdas una vez más.

for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //We want colors to go from green to red
      }
    }
  }

Al crear un color utilizando el sistema RGB, necesitamos los parámetros de rojo, verde y azul para generar cualquier color deseado. En este caso, el color debe ir de verde a rojo, lo que significa que el parámetro azul permanece en 0, mientras que los parámetros rojo y verde varían según el tono que se quiera lograr.

Si el número es pequeño—más cercano al valor mínimo—el color será más verde (el valor mínimo corresponde al verde puro). Por el contrario, si el número es grande, el color se desplazará hacia el rojo (el valor máximo corresponde al rojo puro).

Para determinar qué tan grande o pequeño es el número, usamos el método de normalización Min-Max para obtener una “proporción” entre 0 y 1. Un número mayor da como resultado una proporción más cercana a 1, mientras que un número menor genera una proporción más cercana a 0. Esta proporción se calcula usando las variables minValue y maxValue.

Luego podemos usar esta proporción para determinar los parámetros de rojo y verde. A diferencia de la proporción, que va de 0 a 1, tanto los valores de rojo como de verde van de 0 a 255, por lo que multiplicamos la proporción en consecuencia.

Dado que el rojo debe ser más dominante en números grandes, lo calculamos multiplicando la proporción por 255 y redondeando al número entero más cercano.

Para el verde, el cálculo es diferente. El verde debe ser más dominante en números pequeños, así que usamos 1 − proporción, multiplicándolo por 255. Cuando el número es más grande, la proporción se acerca a 1, lo que hace que 1 − proporción sea más pequeño y, a su vez, reduzca el valor del verde.

Una vez que obtenemos los parámetros de rojo y verde, usamos Api.CreateColorFromRGB(r, g, b) para crear el color y .SetFillColor(color) para aplicarlo a la celda.

El código completo de la macro

(function () {
  var sheet = Api.GetActiveSheet();
  var range = sheet.GetSelection();
  var data = range.GetValue();


  //We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }


  //Indexes indicating where rows and columns start and end
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;


  console.log(firstColIndex + " " + lastColIndex); //Testing if we got the right column indexes, first should be on spot, last should be higher by 1
  console.log(firstRowIndex + " " + lastRowIndex); //Testing if we got the right row indexes, first should be on spot, last should be higher by 1


  var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }


  //Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //We want colors to go from green to red
      }
    }
  }
})();

¡Ejecutemos la macro y veamos cómo funciona!

Antes de ejecutar la macro:

Después de ejecutar la macro:

Ahora puedes visualizar fácilmente la distribución de los datos en tu hoja de cálculo, haciendo que el análisis sea más intuitivo. Ya sea que estés trabajando en la versión de escritorio o en la versión web de ONLYOFFICE, esta macro se integra perfectamente en tu flujo de trabajo.

No pierdas la oportunidad de explorar todo el potencial de la API de ONLYOFFICE. Con una amplia gama de métodos, puedes dar vida a tus ideas de automatización. Si tienes alguna pregunta o concepto innovador, no dudes en compartirlo con nosotros. Valoramos tus aportes y esperamos poder colaborar contigo. ¡Mucho éxito en tus proyectos exploratorios!

Sobre el autor