Utiliza las macros de ONLYOFFICE para copiar columnas en hojas de cálculo separadas

Las macros de ONLYOFFICE abren un número ilimitado de posibilidades, transformando tareas complejas que llevarían horas en procesos completados con un solo clic. Recientemente, hemos recibido una solicitud de un usuario de ONLYOFFICE para una macro que podría automatizar su flujo de trabajo mediante la copia de datos de manera eficiente.

Este artículo explica cómo desarrollamos esta macro y explora las implicaciones más amplias de las macros de ONLYOFFICE.

Creación de la macro

  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

Para empezar, obtenemos la hoja de cálculo activa y la selección en las variables oWorksheet y oRange.

Ahora pasaremos a las 3 funciones que componen esta macro, a saber: getColHeader(), copyColumns() y copyValues().

En primer lugar, la función getColHeader().

  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];
    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

La función getColHeader() toma un único parámetro, colNumber, que especifica el número de columna. Esta función busca un valor válido en la primera fila de la columna especificada. Si existe un valor válido, devuelve ese valor; en caso contrario, devuelve -1.

A continuación veremos cómo funciona la función copyColumns().

  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        for (let j = 1; j <= 2; j++) {
          const col = oRange.GetCols(j);
          col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

La función copyColumns() recorre las columnas de la hoja de cálculo actual, empezando por la tercera columna. Para cada columna, recupera el encabezado de la columna utilizando la función getColHeader(). Si el encabezado es válido, copia los valores y el formato de esa columna a la hoja de destino especificada por el encabezado, utilizando la función copyValues().

Además, se asegura de que las dos primeras columnas, que se consideran columnas comunes, también se copien en cada hoja de destino. El proceso continúa hasta que se encuentra una cabecera no válida, momento en el que finaliza el bucle. Esta es la función principal de la macro y llama a otras funciones de ayuda.

Ojo: En este código asumimos que hay dos columnas comunes, y las columnas específicas que se copiarán por separado en cada hoja comienzan a partir de la tercera columna. Estos valores pueden ajustarse en función de su caso de uso específico.

Ahora la función copyValues().

const copyValues = (range, sheetName, copyType = 0) => {

La función copyValues() copia los valores y el formato del rango de origen a la hoja de destino. Toma tres parámetros: range, que es el objeto range que contiene los datos de las celdas, sheetName, que es el nombre de la hoja de destino, y copyType, que especifica si la columna a copiar es una columna predeterminada o una columna específica.

    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

En primer lugar, la función recupera el valor y el formato (nombre de fuente, tamaño, negrita, cursiva) de la celda de origen.

const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }
    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

A continuación, se recupera la hoja de destino utilizando la variable sheetName, obtenida de la función getColHeader().

Si la hoja de destino no existe, se registra un error y se interrumpe la ejecución de la macro.

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }
      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

Por último, si todo va bien y no se produce ningún error, la función copyValues() establece el valor y el formato en la celda correspondiente de la hoja de destino.

copyColumns();

Terminamos la macro invocando la función copyColumns(), que es el punto de entrada a la macro.

El código completo de la macro

Este es el código de toda la macro:

// Macro Workflow and Info
// This macro automates the process of copying specific columns to designated sheets
// and 'common columns' to all sheets. It ensures data and formatting consistency
// across multiple sheets. You can specify the common columns, which must start from the left and be continuous.
// For specific columns, as long as you have a column apart from the common ones with a valid header and a sheet with the same name exists, the macro will take that column into consideration/.
//If you have a secific column for which a sheet doesnt exist, the console will give you an error.
// For any other problems, be sure to check the console logs.
// Example Scenario:
// Suppose we have columns 'Name', 'Type', 'Calcium', and 'Magnesium' in a sheet called 'FOOD'.
// We also have sheets named 'Calcium' and 'Magnesium'.
// Selecting all data in the 'FOOD' sheet and running the macro will:
// 1. Copy columns 'Name' and 'Type' to both 'Calcium' and 'Magnesium' sheets.
// 2. Copy the 'Calcium' column to the 'Calcium' sheet.
// 3. Copy the 'Magnesium' column to the 'Magnesium' sheet.

(function () {
  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

  /**
   * Gets the value in the first row of the Column
   *
   * @param {number} colNumber
   * @returns {string}

   */
  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];

    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

  /**
   * Iterates through all valid columns and copies them to target sheet.
   */
  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        // Copy the common rows in every sheet.
        for (let j = 1; j <= 2; j++) { const col = oRange.GetCols(j); col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

  /**
   * Copies the values and formatting from the source range to the target sheet.
   * @param {object} range - The range object containing the cell data.
   * @param {string} sheetName - The name of the target sheet to copy the values to.
   * @param {number} copyType - Indicates type of copy operation. 1 for fixed column copy, default or dyanamic.
   */
  const copyValues = (range, sheetName, copyType = 0) => {
    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

    const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }

    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }

      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

  copyColumns();
})();

Ahora, ¡vamos a ver cómo funciona nuestra macro!

Ya está. Acabamos de demostrar cómo incluso las funcionalidades más complejas pueden ser automatizadas usando macros de ONLYOFFICE. La API de ONLYOFFICE es una herramienta poderosa, capaz de realizar una amplia gama de tareas y proporcionar un inmenso potencial para el desarrollo de macros y plugins aún más avanzados. Con esta API, los usuarios pueden aprovechar toda la potencia de ONLYOFFICE para mejorar su productividad y agilizar sus flujos de trabajo.

Si tienes alguna pregunta o concepto innovador, te animamos a que lo compartas con nosotros. Valoramos tu opinión y esperamos colaborar contigo. Te deseamos mucha suerte en tus esfuerzos exploratorios.