Use ONLYOFFICE macros to copy columns into separate spreadsheets

ONLYOFFICE macros open up endless possibilities, transforming complex tasks that would take hours into processes completed with a single click. Recently, we received a request from an ONLYOFFICE user for a macro that could automate their workflow by copying data efficiently.

This article dives into the specifics of that request, detailing how we developed a macro to meet their needs and exploring the broader implications of ONLYOFFICE macros.

Building the macro

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

Starting off, we get the active worksheet and the selection in the oWorksheet and oRange variables.

Now, we will go through the 3 functions that make up this macro, namely: getColHeader(), copyColumns() and copyValues().

First up, the getColHeader() function.

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

The getColHeader() function takes a single parameter, colNumber, which specifies the column number. This function checks for a valid value in the first row of the specified column. If a valid value exists, it returns that value; otherwise, it returns -1.

Next up, we will see how the copyColumns() function works.

  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;
      }
    }
  };

The copyColumns() function iterates through the columns of the current worksheet, starting from the third column. For each column, it retrieves the column header using the getColHeader() function. If the header is valid, it copies the values and formatting from that column to the target sheet specified by the header, using the copyValues() function.

Additionally, it ensures that the first two columns, which are considered common columns, are also copied to every target sheet. The process continues until an invalid header is encountered, at which point the loop terminates. This is the main function of the macro and calls other helper functions.

Note: In this code, we assume there are two common columns, and the specific columns to be copied separately into each sheet start from the third column onward. These values can be adjusted depending on your specific use case.

Now, the copyValues() function.

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

The copyValues() function copies the values and formatting from the source range to the target sheet. It takes three parameters: range, which is the range object containing the cell data, sheetName, which is the name of the target sheet, and copyType, which specifies if the column to be copied is a default column or a specific column.

    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;

First, the function retrieves the value and formatting (font name, size, bold, italic) of the source cell.

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;
    }

Next, the target sheet is retrieved using the sheetName variable, obtained from the getColHeader() function.

If the target sheet does not exist, an error is logged, and the macro execution is aborted.

    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);
      }
    }
  };

Lastly, if everything is alright, and no errors occur, the copyValues() function sets the value and formatting in the corresponding cell of the target sheet.

copyColumns();

Finally, we end the macro by invoking the copyColumns() function, which is the entry point into the macro.

The full macro code

Here is the code for the entire 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();
})();

Now, let’s see how our macro works!

That’s it! We just demonstrated how even the most complex functionalities can be automated using ONLYOFFICE macros. The ONLYOFFICE API is a powerful tool, capable of performing a wide range of tasks and providing immense potential for developing even more advanced macros and plugins. With this API, users can harness the full power of ONLYOFFICE to enhance their productivity and streamline their workflows.

If you have any questions or innovative concepts, we encourage you to share them with us. We value your input and look forward to collaborating with you. Best of luck in your exploratory endeavors!