OfficeScripts | 配列

こんにちは。ふらです。今日はOfficeScriptsを使った、配列の利用について紹介したいと思います。

OfficeScritpsの配列でよく使う配列のコード集

function main(workbook: ExcelScript.Workbook) {

    //1次元配列の初期値を設定
    let array_data1: number[] = [1, 2, 3];

    //1次元配列の要素を取得
    console.log(array_data1[0]);

    //1次元配列へ要素を追加する(末尾に追加)
    array_data1.push(4);
    array_data1.push(5);

    //1次元配列へ要素を追加する(先頭に追加)
    array_data1.unshift(0);

    //1次元配列の要素を削除する(末尾)
    array_data1.pop()

    //1次元配列の要素を削除する(先頭)
    array_data1.shift()

    console.log(array_data1); // [1, 2, 3, 4]

    // -----------

    //2次元配列の初期値を設定
    const array_data2: string[][] = [
        ["01", "02", "03", "04", "05",],
        ["06", "07", "08", "09", "10",],
        ["11", "12", "13", "14", "15",],
        ["16", "17", "18", "19", "20",],
        ["21", "22", "23", "24", "25",]
    ];
    console.log(array_data2);

    //2次元配列の要素を取得
    console.log(array_data2[0][0]);

    //シートのデータを2次元配列として取得する
    const sh = workbook.getWorksheet("Sheet1");
    const values = sh.getUsedRange().getValues();
    console.log(values);

    //2次元配列のデータ加工例
    for (let i = 0; i < values.length; i++) {

        //1列目の文字列先頭にすべて●を入れる
        if (values[i][0]) {
            values[i][0] = "●" + values[i][0];
        }

    }

    //加工した2次元配列をシートに貼り付け
    sh.getRangeByIndexes(0, 0, values.length, values[0].length).setValues(values);
    sh.getRange("A1").select();

}

OfficeScritpsでは2次元配列が必須

VBA を使う方だと、各セルのデータを読み取って条件判定して、処理して...ということをよくされているのではないのでしょうか。
（私はよくしていました。）

for文で各セルを回すので、コードは読みやすいし、使いやすいです。

OfficeScripts ではこの手が使えません。for文内で「getValue」をすると、とても処理速度が遅くなります。こんな警告メッセージもでます。

Invoking read methods inside of a loop could lead to slow performance of the script. For more information, please visit https://aka.ms/office-scripts-performance(Office Scripts Error)

さてさてそれではセルのデータを読み取ってデータ処理ができない！というのを解決してくれるのが2次元配列です。

一度全てのセル情報を読み取って2次元配列に格納して、2次元配列を一度でシートに書き込む。こうすることで、
格段に処理が速くなります。

ただ2次元配列は理解が難しいし、後で見返して混乱することも多々あるので、プログラミング初心者の方にはハードルが高いかも
しれませんが、OfficeScriptsをやっていきたい！となると通らなければ行けない道だと思いますので...

一緒に頑張りましょう。

どのくらい処理速度に差があるのか？

良い例と悪い例で比較をしてみましょう。下記は200行のデータをかける2したときにデータ処理速度を比較したものです。

//2次元配列で、データ取得して書き込み。良い例
function main(workbook: ExcelScript.Workbook) {

    //シート取得
    let sh = workbook.getActiveWorksheet();
    //セル有効範囲の取得
    const rangeValues = sh.getUsedRange().getValues();

    //データを回す
    const start = performance.now();
    for (let j = 3; j < rangeValues.length; j++) {
        //対象データを変数格納
        let range = rangeValues[j][0];
        //データ処理と書き込み
        workbook.getActiveWorksheet().getCell(j, 1).setValue(Number(range)*2);
    }
    const end = performance.now();
    workbook.getActiveWorksheet().getCell(1, 2).setValue(end - start);

}

//1つずつgetValueして、データ取得して書き込み。悪い例
function main(workbook: ExcelScript.Workbook) {

    //シート取得
    let sh = workbook.getActiveWorksheet();

    //データを回す
    const start = performance.now();
    for (let j = 3; j < 200; j++) {
        //対象データを変数格納
        let range = sh.getCell(j, 0).getValue();
        //データ処理と書き込み
        workbook.getActiveWorksheet().getCell(j, 1).setValue(Number(range) * 2);
    }
    const end = performance.now();
    workbook.getActiveWorksheet().getCell(1, 2).setValue(end - start);

}

2次元配列を使った良い例では、0.0225秒で処理が完了しましたが、getValue()を使った悪い例では40秒も時間がかかりました。違いは歴然ですね。OfficeScritpsはまだまだコードの情報も少なく勉強しにくい
言語であることは否めませんが、ご興味のある方は、ぜひ私の投稿も参考資料の一部にして頂けますと幸いです。

---

#OfficeScripts #Officeスクリプト #プログラミング #自動化 #業務効率化 #エクセル #M365 #ExcelOnline #osts