SharePoint Online のリストデータをカスタムスクリプトで取得する方法について

データの準備

以下のコードを実行して CSV を生成して実験用のリストを用意する。

import datetime as dt
import pandas as pd
import random as rd
from faker import Faker

title = []
data1 = []
data2 = []
data3 = []
data4 = []
data5 = []

choices_int_data = [i for i in range(100)]
choices_text_data = ["aaaa" + str(i).zfill(4) for i in range(100)]

start_datetime = dt.datetime(
    2024, 1, 1, 0, 0, 0, tzinfo=dt.timezone(offset=dt.timedelta(hours=9))
)

fake = Faker(["en_US", "ja_JP"])

for i in range(100000):
    title.append(i)
    data1.append(rd.choices(choices_int_data)[0])
    data2.append(rd.choices(choices_text_data)[0])
    data3.append((start_datetime + dt.timedelta(seconds=i)).isoformat())

    data5.append(fake.name())


data_1 = [1 for _ in range(1)]
data_10 = [10 for _ in range(10)]
data_100 = [100 for _ in range(100)]
data_1000 = [1000 for _ in range(1000)]
data_4999 = [4999 for _ in range(4999)]
data_5000 = [5000 for _ in range(5000)]
data_5001 = [5001 for _ in range(5001)]
data_10000 = [10000 for _ in range(10000)]
data_numbers = (
    data_1
    + data_10
    + data_100
    + data_1000
    + data_4999
    + data_5000
    + data_5001
    + data_10000
)
data_other = [-1 for _ in range(100000 - len(data_numbers))]

data4 = rd.sample(data_numbers + data_other, len(data_numbers + data_other))

df = pd.DataFrame(
    data={
        "title": title,
        "data1": data1,
        "data2": data2,
        "data3": data3,
        "data4": data4,
        "data5": data5,
    }
)

df.to_csv("test_data_v2.csv", index=None)

SharePoint Online のカスタムスクリプトでリストのデータを取得するための認証

OAuth を使用しない場合、API をコールするときに X-RequestDigest ヘッダーを指定する必要があるので以下のようなコードで取得する。

SharePoint REST エンドポイントを使用して基本的な操作を完了する

async function getFdvAsync(siteUrl) {
    const url = `https://${siteUrl}/_api/contextinfo`;
    const res = await fetch(url, {
        method: "POST",
        headers: {
        Accept: "application/json;odata=nometadata",
        },
    });

    if (!res.ok) {
        throw new Error("存在しないサイトです");
    }
    const contextinfo = await res.json();
    let fdv = contextinfo.FormDigestValue;
    return fdv;
}

API をリクエストするヘッダーは以下のようなものを共通で用意しておく。

let yoursiteurl = "${your-tenant}.sharepoint.com/sites/${your-site-name}"

let fdv =  await getFdvAsync(yoursiteurl);
let headers = { 
    Accept: "application/json;odata=nometadata", 
    "Content-Type": "application/json;odata=verbose", 
    "X-RequestDigest": fdv
}

Accept で application/json;odata=nometadata を指定するのは Items でデータを取得するときにメタデータを取得しないようにするため。 verbose を指定すると詳細なメタデータを取得することができる。

Items で取得する場合

REST を使用してリストとリスト アイテムを操作する

シンプルには以下のように指定することで取得ができる。
この場合、デフォルトでは 100 件のデータが取得できる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/Items`;

let res = await fetch(url, {
    method: "GET",
    headers: headers,
    }
).then(r=>r.json());
console.log(res);

この時、取得されたデータに res['odata.nextLink'] があり、そのリンクを使用することで次のページのデータを取得することができる。
(odata.nextLink は nometadata を指定したとき取得できるプロパティで verbose を指定した場合は res.d.__next で取得できる)

$filter を指定することでフィルタリングが可能。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/Items?$filter=field_4 eq 10`;

let res = await fetch(url, {
    method: "GET",
    headers: headers,
    }
).then(r=>r.json());
console.log(res);

$filter で検索条件に引っかかるアイテムが5000件以下であれば200の応答でデータを取得することができる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/Items?$filter=field_4 eq 5000`;

let res = await fetch(url, {
    method: "GET",
    headers: headers,
    }
).then(r=>r.json());
console.log(res);

$filter で検索条件に引っかかるアイテムが5001件以上の場合5000件問題に抵触しエラーが返る。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/Items?$filter=field_4 eq 5001`;

let res = await fetch(url, {
    method: "GET",
    headers: headers,
    }
).then(r=>r.json());
console.log(res);

{
    "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
    "message": {
        "lang": "ja-JP",
        "value": "この操作は、リストビューのしきい値を超えているため、実行できません。"
    }
}

RenderListDataAsStream でデータを取得する場合

REST を使用してリストとリスト アイテムを操作する

シンプルな指定では以下のようなリクエストでデータを取得することができる。
デフォルトでは 30 件のデータを取得することができる。
次の値を取得する場合は Paged=TRUE を指定して得られる NextHref を使って次のリクエストを送信する。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
console.log(res);

フィルタリングなどをする場合は URL Parameter で指定する場合と、 ViewXml で指定する方法の 2つがある。

URL Parameter で指定

REST を使用してリストとリスト アイテムを操作する

シンプルには以下のように指定できる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=10&FilterType1=Number`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
console.log(res);

5000件までのデータの場合は問題なく 200 でデータの取得ができる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=5000&FilterType1=Number`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
console.log(res);

RenderListDataAsStream は Items の場合とは違い、 5001 件以上のデータがヒットした場合でも問題なくデータの取得ができる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=5001&FilterType1=Number`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
console.log(res);

この場合、以下のようにデータを取得することで対象の全てのデータを取得することができる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=5001&FilterType1=Number`;

let ans = [];

while(true){

    let res = await fetch(url, {
        method: "POST",
        headers: headers,
        body: JSON.stringify({
            parameters:{
                "__metadata":{
                    "type":"SP.RenderListDataParameters"
                    },
                RenderOptions: 0
            }
        })
    }).then(r=>r.json());
    ans = [...ans, ...res.Row];

    if(!res.NextHref)break;
    url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream` + res.NextHref;
}
console.log(ans.length);

一度にたくさんのデータを取得する場合は RowLimit を指定することになるが、5001 以上を設定すると 500 のエラーが返る。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?RowLimit=5001`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
console.log(res);

{
    "odata.error": {
        "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
        "message": {
            "lang": "ja-JP",
            "value": "この操作は、リストビューのしきい値を超えているため、実行できません。"
        }
    }
}

RowLimit を指定した場合、条件にヒットするデータが 5000 件以内であれば 1 回のリクエストで全て取得することができるが、ヒットするデータが 5001 件以上になる場合は 1 回のリクエストで取得できるデータは分割されてしまう。
例えば以下のようにパラメータを指定すると 5001 件のデータがヒットするためデータは分割される。
ただし、注意点として取得されるデータは 5000 件と 1 件に分割されるのではなく、はじめのリクエストで取得されるのは 238 件しか取得されない。
なぜ 238 件なのかというと全データの先頭 5000 件の中に以下の条件に合致するデータが 238 件しかなかったため。
つまり、5000 件区切りで検索されその値が返却されるということになる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=5001&FilterType1=Number&RowLimit=5000`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
// 238 件のデータ
console.log(res);

以下のように条件にヒットするデータが 5000 件以内の場合は 1 回で取得できるので、5001 件を超えるか超えないかによってデータの取得効率にかなり差が出ることになる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=5000&FilterType1=Number&RowLimit=5000`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
// 5000 件のデータ
console.log(res);

URL パラメータでイコール以外の操作を指定することができるとドキュメントにあるが実際には無視されるらしく、指定を行ってもイコールとしか判定がされない。

ContentByQueryWebPart.FilterFieldQueryOperator Enumeration (Microsoft.SharePoint.Publishing.WebControls)

Reddit ではあるが無視されることが書かれており、実際に指定しても Leq などは使えなかった。

Reddit - Dive into anything

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream?FilterField1=field_4&FilterValue1=100&FilterType1=Number&FilterOp1=Leq&RowLimit=5000`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0
        }
    })
}).then(r=>r.json());
// 100 件のデータが取得されてしまう
console.log(res);

そのため、Leq などを指定したい場合は ViewXml を使用する必要がある。

ViewXml で指定

ViewXml のリファレンスは以下。
View element (List)

シンプルに RowLimit を指定する場合は以下。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream`;

let viewXml = `
<View>
    <RowLimit>5000</RowLimit>
</View>`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0,
            ViewXml: viewXml
        }
    })
}).then(r=>r.json());
console.log(res);

RenderOptions を指定することで取得できるデータにメタデータなどを追加することができる。 0 は最もシンプルなデータを取得する。

REST を使用してリストとリスト アイテムを操作する

フィルターを指定する場合は以下。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream`;

let viewXml = `
<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name="field_4" />
                <Value Type="Number">100</Value>
            </Eq>
        </Where>
    </Query>
    <RowLimit>5000</RowLimit>
</View>`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0,
            ViewXml: viewXml
        }
    })
}).then(r=>r.json());
console.log(res);

URL Parameter ではできない Leq などの設定や複数の条件の設定することができる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream`;

let viewXml = `
<View>
    <Query>
        <Where>
            <And>
                <Leq>
                    <FieldRef Name="field_4" />
                    <Value Type="Number">100</Value>
                </Leq>
                <Geq>
                    <FieldRef Name="field_4" />
                    <Value Type="Number">0</Value>
                </Geq>
            </And>
        </Where>
    </Query>
    <RowLimit>5000</RowLimit>
</View>`;

let res = await fetch(url, {
    method: "POST",
    headers: headers,
    body: JSON.stringify({
        parameters:{
            "__metadata":{
                "type":"SP.RenderListDataParameters"
                },
            RenderOptions: 0,
            ViewXml: viewXml
        }
    })
}).then(r=>r.json());
console.log(res);

日付と時刻の絞り込みについて

日付の絞り込みはやや特殊なのでメモしておく。

Items で絞り込み

UTC の ISO フォーマットで日付を指定することでフィルタができる この時 2024-01-01T00:00:00%2B09:00 というように + はエンコードしておく。
もちろん 2024-01-01T00:00:00Z でもよい。
きちんとタイムゾーンを考慮して絞り込みをしてくれる。

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/Items?$filter=field_3 eq '2024-01-01T00:00:00%2B09:00'`;

let res = await fetch(url, {
    method: "GET",
    headers: headers,
    }
).then(r=>r.json());
console.log(res);

REnderListDataAsStream で絞り込み

時刻まで指定して絞り込みを行うことはできない。
日単位で絞り込みを行い、取得したデータを Javascript 等でさらに絞り込む必要がある。

RowLimit で Paged を指定しない場合 NextHref が取得できない場合がある。

<Value Type="DateTime">2024-01-01T00:00:00Z</Value> は 2024/1/1 と解釈されるようで、一番始めに取得される値は以下のようになる。 

field_3: "2024/01/01 0:00"
field_3.: "2023-12-31T15:00:00Z"

また、日付で以降などの検索をするとデータが5000より多くなる場合がほとんどになると思うので、他のフィールドの値のフィルターと組み合わせるのが無難。 

let url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream`;

// このように指定することで表示の 2024/1/1のデータを取得
let viewXml = `
<View>
    <Query>
        <Where>
            <And>
                <Geq>
                    <FieldRef Name="field_3" />
                    <Value Type="DateTime">2024-01-01T00:00:00Z</Value>
                </Geq>
                <Leq>
                    <FieldRef Name="field_3" />
                    <Value Type="DateTime">2024-01-01T00:00:00Z</Value>
                </Leq>
            </And>
        </Where>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>`;


let ans = [];

while(true){

    let res = await fetch(url, {
        method: "POST",
        headers: headers,
        body: JSON.stringify({
            parameters:{
                "__metadata":{
                    "type":"SP.RenderListDataParameters"
                    },
                RenderOptions: 0,
                ViewXml: viewXml
            }
        })
    }).then(r=>r.json());
    ans = [...ans, ...res.Row];
    console.log(res.Row.length);
    if(!res.NextHref)break;
    url = `https://${yoursiteurl}/_api/web/lists/GetByTitle('test_data_v2')/RenderListDataAsStream` + res.NextHref;
}
console.log(ans.length);

コメント

コメントを投稿

このブログの人気の投稿

better-sqlite3 を AWS の Lambda で動かす

動かない... AWS の Lambda 上で Sqlite を動かしたいと思ったので試してみたが、素直に動かせなかったので動かせるようになるまでの記録を残しておく。 現象 nodejs で Sqlite を動かすために better-sqlite3 をインストールし、 AWS の Lambda にデプロイして実行すると以下のようなエラーが発生し実行ができなかった。 /lib64/libm.so.6: version `GLIBC_2.29' not found どうも nodejs 18 が動いている Amazon Linux 2 の /lib64/libm.so.6 に GLIBC_2.29 が入っていないが、 better-sqlite3 では 2.29 を使っているため実行エラーになってしまっている様子。 ちなみに Amazon Linux 2 の /lib64/libm.so.6 の確認は以下のように行った。 # Amazon Linux 2 のコンテナを起動 > docker run -it --rm --entrypoint "bash" public.ecr.aws/lambda/nodejs:18-x86_64 以下をコンテナ内で実行 > yum install binutils > strings /lib64/libm.so.6 | grep GLIBC_ GLIBC_2.2.5 GLIBC_2.4 GLIBC_2.15 GLIBC_2.18 GLIBC_2.23 GLIBC_2.24 GLIBC_2.25 GLIBC_2.26 GLIBC_PRIVATE GLIBC_2.15 ... 解決方法 AWS Lambda の nodejs 18.X ランタイムが動いている Amazon Linux 2 用に better-sqlite3 をビルドし直した。 ビルド方法 AWS Lambda nodejs 18.X の実行コンテナイメージは公開されているので、それを使ってビルドした。 今回対象としている better-sqlite3 のバージョンは 8.3.0。 Amazon Linux 2 が更新されない限り、これ以降のバージョンを使う場合で...
続きを読む

Python の oletools で日本語の VBA を出力する方法

VBA はどこにある? 以下の記事がとても参考になる。 実務であまり役に立たないVBAの内部の話 - Qiita マクロの実体は vbaProject.bin というバイナリファイルの中にあるらしく、これを解析すると VBA を取得することができる。 このファイルは Compound File Binary Format というファイル形式で仕様は効果されているらしい。 時間があるときに確認してみたい。 [MS-CFB]: Compound File Binary File Format oletools で VBA を出力する方法 vbaProject.bin を自分で解析して VBA を出力するのは大変なので oletools というライブラリを使用する。 このライブラリは olefile というパーサを使いやすくしたもの。 ただし、このライブラリで VBA を出力すると以下の記事にもあるように日本語は文字化けをしてしまう。 ExcelマクロのVBAソースコードをAzure DevOpsでバージョン管理する方法 この記事では extract_macros を自作することで対応をしていたが、以下のように bytes2str を置き換えることでも日本語することができた。 記述量も少なく手軽に実行できるので記事にしておく。 import os import oletools.olevba as vba # ------------------------------------------------------------------ # 定数 OUT_DIR = "./out" """出力するフォルダ""" # ------------------------------------------------------------------ # 日本語出力用の設定 def _bytes2str ( bytes_string : bytes , encoding = "utf-8" ): # ShiftJis でデコードする return bytes_strin...
続きを読む

Amazon Linux 2 に PostgreSQL をインストールする

Amazon Linux 2 に PostgreSQL をインストールして外部のクライアントからアクセスするまでを忘れないようにメモとして残しておく。 環境 AMI Amazon Linux 2 Kernel 5.10 AMI 2.0.20221004.0 x86_64 HVM gp2 インスタンスタイプ t3a.small ストレージ 16GB EC2内での手順 sudo amazon-linux-extras install -y postgresql14 sudo yum install -y postgresql-server postgresql-devel パッケージ 説明 postgresql-server initdb や pg_ctl などのユーティリティ postgresql-devel ヘッダや pg_config などの開発関連 export PGSETUP_INITDB_OPTIONS = "--encoding=UTF8 --locale=ja_JP.UTF-8 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8" オプション 説明 --encoding initdbでPostgreSQLクラスタのデフォルト文字セット(エンコーディング) --locale ロケールのサポートはアルファベット、並び換え、数字の書式など文化的嗜好を配慮したアプリケーションを対象にします。 --lc-collate 文字列の並び換え順 --lc-ctype 文字の分類（文字とはどんなもの？大文字小文字を区別しない？） sudo postgresql-setup --initdb sudo systemctl start postgresql.service sudo systemctl enable postgresql.service 以下のコマンドで PostgreSQL が実行されているかを確認できる。 systemctl status --no-pager postgresql.service インストールが完了すると...
続きを読む