ONLYOFFICEマクロを使用して、列を別々のスプレッドシートにコピーする方法

ONLYOFFICEのマクロは、何時間もかかる複雑な作業をワンクリックで完了させる、無限の可能性を広げます。最近、ONLYOFFICEユーザーから、効率的にデータをコピーしてワークフローを自動化できるマクロが欲しいという要望をいただきました。

この記事では、そのリクエストの具体的な内容を掘り下げ、どのようにニーズを満たすマクロを開発したかを詳述し、ONLYOFFICEマクロのより広い意味を探ります。

マクロの構築

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

最初に、アクティブなワークシートと選択範囲を oWorksheet 変数と oRange 変数に取得します。

次に、このマクロを構成する3つの関数、すなわちgetColHeader()、copyColumns()、copyValues()を説明します。

まずはgetColHeader()関数です。

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

getColHeader() 関数は、列番号を指定する colNumber というパラメータを 1 つ受け取ります。この関数は、指定した列の最初の行に有効な値があるかどうかをチェックします。有効な値が存在する場合はその値を返し、そうでない場合は -1 を返します。

次は、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;
      }
    }
  };

copyColumns() 関数は、現在のワークシートの列を 3 列目から順に反復処理します。各列について、getColHeader() 関数を使用して列ヘッダを取得します。ヘッダが有効な場合は、copyValues() 関数を使用して、その列の値と書式設定をヘッダで指定されたターゲット・シートにコピーします。

さらに、共通列とみなされる最初の 2 列も、すべてのターゲット・シートにコピーされるようにします。この処理は無効なヘッダに遭遇するまで続けられ、その時点でループは終了します。これはマクロのメイン関数で、他のヘルパー関数を呼び出します。

注：このコードでは、共通の列が2つあり、各シートに個別にコピーされる特定の列は3列目以降からであると仮定しています。これらの値は、特定のユースケースに応じて調整することができます。

さて、copyValues() 関数です。

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

copyValues() 関数は、コピー元の範囲の値と書式をコピー先のシートにコピーします。range はセル・データを含む範囲オブジェクト、sheetName はターゲット・シートの名前、copyType はコピーされる列がデフォルトの列か特定の列かを指定します。

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

次に、getColHeader()関数から取得したsheetName変数を使用してターゲットシートを取得します。

ターゲット・シートが存在しない場合、エラーが記録され、マクロの実行は中断されます。

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

最後に、問題がなく、エラーも発生しなければ、copyValues() 関数はターゲット・シートの対応するセルに値と書式を設定します。

copyColumns();

最後に、マクロのエントリポイントである copyColumns() 関数を呼び出してマクロを終了します。

マクロ全体のコード

マクロ全体のコードです：

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

では、マクロがどのように機能するか見てみましょう！

以上です！ONLYOFFICEマクロを使って、最も複雑な機能さえも自動化できることをご紹介しました。ONLYOFFICE APIは強力なツールであり、幅広いタスクを実行することができ、さらに高度なマクロやプラグインを開発するための計り知れない可能性を提供します。このAPIにより、ユーザーはONLYOFFICEのフルパワーを活用し、生産性を高め、ワークフローを合理化することができます。ご質問や革新的なコンセプトがありましたら、ぜひ私たちと共有してください。