MENU
  1. ホーム
  2. Office Scripts
  3. OfficeScripts 最終行まで繰り返し処理する（ループ処理）

OfficeScripts 最終行まで繰り返し処理する（ループ処理）

広告
Office Scripts

OfficeScript（オフィススクリプト）で最終行まで処理を繰り返す方法を紹介します。

処理速度があがる方法や空白行まで処理を行う方法なども紹介していますので参考にしてみてください。

ドノ

こちらではfor構文を用いた処理方法を紹介しています

最終行の取得についてはこちらの記事で紹介してますので参考にしてみてください。

あわせて読みたい
OfficeScripts 最終行と最終列の取得｜空白と書式ありに対応 OfficeScript（オフィススクリプト）で最終行と最終列を取得する方法を紹介します。 テーブルを1行目から順番に処理していくときなどに利用できます。ここでは途中に空…
目次

最終行まで1行ずつデータ処理（for構文）

こちらでは最終行までの繰り返し処理は下記のようなステップをとります。

  1. 処理するテーブルの開始行を設定
  2. テーブルの最終行を取得（列を指定）
  3. 開始行から最終行まで繰り返し処理

for構文による繰り返し処理（最終行まで）

最終行まで1行ずつ処理する基本的なスクリプトはこちらです。

シート名・開始行インデックス・最終行取得用の列を設定して for構文の中に行いたい処理を記載します。
※開始行インデックスは0始まりなので注意。1行目から処理したい場合は 0 とする。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('シート名');              // 任意のシート名を設定

  //処理開始行を指定
  const startRow = "開始行インデックス"                                   // 任意の行を設定

  // 指定列の最終行を取得（列指定の例、A:A、B:Bなど）
  const columnRange = sheet.getRange("列番号:列番号").getUsedRange();    // 任意の列を設定
  const lastRow = columnRange.getLastCell().getRowIndex();

  // 最終行まで繰り返し処理
  for (let i = startRow; i <= lastRow; i++) { 
    
    //ここに繰り返し処理した内容を記載します。

  }
}

最終行までのデータ処理例

処理の具体例として下図データの B列の単価 と C列の個数 をかけて D列に売上 を計算するスクリプトを載せておきます。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('Sheet1');                // シート名はSheet1

  //テーブルの開始行を指定
  const startRow = 1                                            // 計算処理は2行目からなので 1

  // 指定列の最終行を取得（列指定の例、A:A、B:Bなど）
  const columnRange = sheet.getRange("A:A").getUsedRange();     // 列A列で最終行を取得
  const lastRow = columnRange.getLastCell().getRowIndex();

  // 最終行まで繰り返し処理
  for (let i = startRow; i <= lastRow; i++) { 
    const unitPrice = sheet.getCell(i, 1).getValue() as number; // B列の値（単価）。B列の列番号は1
    const quantity = sheet.getCell(i, 2).getValue() as number;  // C列の値（個数）。C列の列番号は2
    const sales = unitPrice * quantity;                         // 売上げ計算
    sheet.getCell(i, 3).setValue(sales);                        // D列に売上を入力
  }
}

上記のスクリプトを実行すると下図のようにきちんと最終行まで計算が実行されています。

Sponsored link

途中の空白までデータ処理（for構文）

こちらでは空白行までの繰り返し処理は下記のようなステップをとります。

  1. 処理するテーブルの開始行を設定
  2. テーブル内の空白行を取得（列を指定）
  3. 開始行から最終行まで繰り返し処理

for構文による繰り返し処理（空白行まで）

空白行まで1行ずつ処理する基本的なスクリプトはこちらです。

シート名・開始行インデックス・最終行取得用の列を設定して for構文の中に行いたい処理を記載します。
※開始行インデックスは0始まりなので注意。1行目から処理したい場合は 0 とする。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('シート名');              // 任意のシート名を設定

  // 処理開始行を指定
  const startRow = "開始行インデックス"                          // 任意の行を設定

  // 空白行を取得する列を指定（例、A、Bなど）
  const targetColumn = "列番号"                                 // 任意の列を設定

  // 処理開始行から下に移動して空白行を取得
  const blankRow = sheet.getRange(targetColumn + startRow).getRangeEdge(ExcelScript.KeyboardDirection.down).getRowIndex();

  // 空白行まで繰り返し処理
  for (let i = startRow; i <= lastRow; i++) { // 1行目はヘッダーと仮定
    
    //ここに繰り返し処理した内容を記載します。

  }
}

空白行までのデータ処理例

処理の具体例として下図データの B列の単価 と C列の個数 をかけて D列に売上 を計算するスクリプトを載せておきます。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('Sheet1');              // シート名はSheet1

  // 処理開始行を指定
  const startRow = 1                                           // 計算処理は2行目からなので 1

  // 空白行を取得する列を指定（例、A、Bなど）
  const targetColumn = "A"                                     // 列A列で空白行を取得

  // 処理開始行から下方向に移動して空白行を取得
  const blankRow = sheet.getRange(targetColumn + startRow).getRangeEdge(ExcelScript.KeyboardDirection.down).getRowIndex();

  // 空白行まで繰り返し処理
  for (let i = startRow; i <= blankRow; i++) {
    const unitPrice = sheet.getCell(i, 1).getValue() as number; // B列の値（単価）。B列の列番号は1
    const quantity = sheet.getCell(i, 2).getValue() as number;  // C列の値（個数）。C列の列番号は2
    const sales = unitPrice * quantity;                         // 売上げ計算
    sheet.getCell(i, 3).setValue(sales);                        // D列に売上を入力
  }
}

上記のスクリプトを実行すると下図のようにきちんと空白行まで計算が実行されています。

Sponsored link

繰り返し処理の速度向上

ドノ

こちらでは私が大容量データをfor構文で処理していた時に行った処理速度向上の方法を紹介します。

私が行った処理は「 セルからデータの取得 と セルへのデータ出力 を一度に行う」ようにしたことです。

これにより、数倍～数十倍に処理速度があがりました（あくまで私の一例ですが。。）

その場合の処理のステップは以下の通りです。

  1. 処理するテーブルの開始行を設定
  2. テーブルの最終行を取得（列を指定）
  3. 処理に使う列データを一度に取得
  4. 開始行から最終行まで繰り返し処理
  5. 処理結果をテーブルに一度に出力

for構文による繰り返し処理（速度向上Ver）

シート名・開始行インデックス・最終行取得用の列を設定して for構文の中に行いたい処理を記載します。
※開始行インデックスは0始まりなので注意。1行目から処理したい場合は 0 とする。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('シート名');              // 任意のシート名を設定

  //処理開始行を指定
  const startRow = 開始行インデックス                             // 任意の行インデックスを設定（０始まり）

  // 指定列の最終行を取得（列指定の例、A:A、B:Bなど）
  const columnRange = sheet.getRange("列:列").getUsedRange();    // 任意の列を設定
  const lastRow = columnRange.getLastCell().getRowIndex();

  // 繰り返し処理に使うデータを取得。下記では2つの列だけですが増やしてもOK
  const data1 = sheet.getRange("列" + (startRow + 1) + ":列" + (lastRow + 1)).getValues(); // 任意の列のデータ1
  const data2 = sheet.getRange("列" + (startRow + 1) + ":列" + (lastRow + 1)).getValues(); // 任意の列のデータ2

  // 計算結果を格納する配列を作成
  const result: [][] = [];

  // 最終行まで繰り返し処理
  for (let i = 0; i < data1.length; i++) {

    // 繰り返し処理を行い、計算結果をresultに格納する
  }

  // 計算結果を出力
  sheet.getRange("列" + (startRow + 1) + ":列" + (lastRow + 1)).setValues(result);
}

データ処理例

処理の具体例として下図データの B列の単価 と C列の個数 をかけて D列に売上 を計算するスクリプトを載せておきます。

function main(workbook: ExcelScript.Workbook) {

  // シート名を指定してワークシートを取得
  const sheet = workbook.getWorksheet('Sheet1');              // シート名はSheet1

  //処理開始行を指定
  const startRow = 1                                          // 2行目を設定

  // 指定列の最終行を取得（列指定の例、A:A、B:Bなど）
  const columnRange = sheet.getRange("A:A").getUsedRange();    // A列を設定
  const lastRow = columnRange.getLastCell().getRowIndex();

  // 繰り返し処理に使うデータを取得。下記では2つの列だけですが増やしてもOK
  const data1 = sheet.getRange("B" +(startRow+1)+ ":B" +(lastRow+1)).getValues(); // B列のデータ1
  const data2 = sheet.getRange("C" +(startRow+1)+ ":C" +(lastRow+1)).getValues(); // C列のデータ2

  // 計算結果を格納する配列を作成
  const result:number [][]= [];

  // 最終行まで繰り返し処理
  for (let i = 0 ; i < data1.length; i++) {
    const unitPrice = data1[i][0] as number;               // B列の値（単価）
    const quantity = data1[i][0] as number;                // C列の値（個数）
    const sales = unitPrice * quantity;                    // 売上計算
    result.push([sales]);                                  // 計算結果を格納
  }

  // 計算結果をD列に出力
  sheet.getRange("D" + (startRow + 1) + ":D" + (lastRow + 1)).setValues(result);
}

上記のスクリプトを実行すると下図のようにきちんと最終行まで計算が実行されています。

getValuesコマンドを使用して大容量データを扱おうとすると「応答ペイロードのサイズが制限を超えました」というエラーが発生する場合があります。その際は上述の1行ずつ処理する方法で実行してください。

Sponsored link

Office Scripts
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！

この記事を書いた人

ドノのアバター ドノ

仕事、お金、旅行のことなどメモしてます。
仕事ではTableauを勉強中！
初心者にもわかりやすいように学んだことを発信していきます。

関連記事

コメント

コメントする

コメントは日本語で入力してください。(スパム対策)

CAPTCHA

目次