Use macros do ONLYOFFICE para copiar colunas em planilhas separadas

As macros do ONLYOFFICE abrem possibilidades infinitas, transformando tarefas complexas que levariam horas em processos concluídos com um único clique. Recentemente, recebemos uma solicitação de um usuário do ONLYOFFICE para uma macro que pudesse automatizar seu fluxo de trabalho, copiando dados de forma eficiente.

Este artigo se aprofunda nas especificidades dessa solicitação, detalhando como desenvolvemos uma macro para atender às suas necessidades e explorando as implicações mais amplas das macros ONLYOFFICE.

Construindo a macro

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

Começando, obtemos a planilha ativa e a seleção nas variáveis ​​oWorksheet e oRange.

Agora passaremos pelas 3 funções que compõem esta macro, a saber: getColHeader(), copyColumns() e copyValues().

Primeiro, a função getColHeader().

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

A função getColHeader() usa um único parâmetro, colNumber, que especifica o número da coluna. Esta função verifica um valor válido na primeira linha da coluna especificada. Se existir um valor válido, ele retornará esse valor; caso contrário, retornará -1.

A seguir, veremos como funciona a função 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;
      }
    }
  };

A função copyColumns() itera pelas colunas da planilha atual, começando na terceira coluna. Para cada coluna, ele recupera o cabeçalho da coluna usando a função getColHeader(). Se o cabeçalho for válido, ele copia os valores e a formatação dessa coluna para a planilha de destino especificada pelo cabeçalho, usando a função copyValues().

Além disso, garante que as duas primeiras colunas, consideradas colunas comuns, também sejam copiadas para cada planilha de destino. O processo continua até que um cabeçalho inválido seja encontrado, momento em que o loop termina. Esta é a função principal da macro e chama outras funções auxiliares.

Nota: Neste código, assumimos que existem duas colunas comuns e que as colunas específicas a serem copiadas separadamente em cada planilha começam a partir da terceira coluna. Esses valores podem ser ajustados dependendo do seu caso de uso específico.

Agora, a função copyValues().

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

A função copyValues() copia os valores e a formatação do intervalo de origem para a planilha de destino. São necessários três parâmetros: range, que é o objeto de intervalo que contém os dados da célula, sheetName, que é o nome da planilha de destino, e copyType, que especifica se a coluna a ser copiada é uma coluna padrão ou uma coluna 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;

Primeiro, a função recupera o valor e a formatação (nome da fonte, tamanho, negrito, itálico) da célula de origem.

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 seguir, a planilha de destino é recuperada usando a variável sheetName, obtida da função getColHeader().

Se a planilha de destino não existir, um erro será registrado e a execução da macro será abortada.

    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, se tudo estiver bem e nenhum erro ocorrer, a função copyValues() define o valor e a formatação na célula correspondente da planilha de destino.

copyColumns();

Finalmente, finalizamos a macro invocando a função copyColumns(), que é o ponto de entrada na macro.

O código de macro completo

Aqui está o código para toda a 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();
})();

Agora, vamos ver como nossa macro funciona!

É isso! Acabamos de demonstrar como até as funcionalidades mais complexas podem ser automatizadas usando macros ONLYOFFICE. A API ONLYOFFICE é uma ferramenta poderosa, capaz de realizar uma ampla gama de tarefas e fornecer imenso potencial para o desenvolvimento de macros e plugins ainda mais avançados. Com esta API, os usuários podem aproveitar todo o poder do ONLYOFFICE para aumentar sua produtividade e agilizar seus fluxos de trabalho.

Se você tiver alguma dúvida ou conceitos inovadores, encorajamos você a compartilhar conosco. Valorizamos sua opinião e esperamos colaborar com você. Boa sorte em seus empreendimentos exploratórios!