「東雲忠太郎」の平凡な日常のできごと

2024.02.26

VB.NETでSharePointリストの列1でグループ化して列2の最大値を求めるコード

カテゴリ： VB.NET

Imports System.Net.Http

Imports System.Text

Imports System.Threading.Tasks

Imports Newtonsoft.Json.Linq

Module Module1

Sub Main()

Dim siteUrl As String = "https://your-sharepoint-site-url"

Dim username As String = "your-username"

Dim password As String = "your-password"

Dim listName As String = "YourListName"

Dim field1Name As String = "Column1" ' 列1の名前

Dim field2Name As String = "Column2" ' 列2の名前

GetMaxValuePerGroup(siteUrl, username, password, listName, field1Name, field2Name).Wait()

End Sub

Async Function GetMaxValuePerGroup(siteUrl As String, username As String, password As String, listName As String, field1Name As String, field2Name As String) As Task

Dim credentials As String = Convert.ToBase64String(Encoding.ASCII.GetBytes($"{username}:{password}"))

Using client As New HttpClient()

client.DefaultRequestHeaders.Authorization = New System.Net.Http.Headers.AuthenticationHeaderValue("Basic", credentials)

Dim endpointUrl As String = $"{siteUrl}/_api/web/lists/getbytitle('{listName}')/items?$select={field1Name},{field2Name}&$orderby={field1Name}&$top=5000"

Dim response As HttpResponseMessage = Await client.GetAsync(endpointUrl)

If response.IsSuccessStatusCode Then

Dim responseContent As String = Await response.Content.ReadAsStringAsync()

Dim json As JObject = JObject.Parse(responseContent)

Dim items As JArray = json("value")

' 列1でグループ化して列2の最大値を求める

Dim groupedData = From item In items

Group item By Key = item(field1Name).ToString() Into Group

Select New With {

.Key = Key,

.MaxValue = Group.Max(Function(x) Convert.ToInt32(x(field2Name)))

}