Actually pulling SharePoint List from Power Query/Power BI is much faster and straightforward. But in some cases, you may still need to follow the traditional ETL way. Hope below script can help you if you need it.
Firstly we need to register the app in Azure AD (Check Here). After create your app, write down 3 ids: tenant id, client id, and the secret. Note the secret will only be exposed to you when you create the app, then it will be masked (though you can always create a new one.)
To allow your app to access the SharePoint list, you need to enable the "Site.Read.All" permission under the Application Permission category, as demonstrated below. Keep in mind though, the "Site.Read.All" permission needs AAD admin's consent.
That is all about the app setup. To query the list, we need to exchange the token by our tenant id, client id, and the secret:
public class RespToken
{
public string token_type { get; set; }
public int expires_in { get; set; }
public int ext_expires_in { get; set; }
public string access_token { get; set; }
}
public static string GetAccessToken(string tenentId, string clientId, string clientSecret)
{
string token = string.Empty;
string uriToken = string.Format("https://login.microsoftonline.com/{0}/oauth2/v2.0/token", tenentId);
string contentType = "application/x-www-form-urlencoded";
string grantType = "client_credentials";
string scope = "https://graph.microsoft.com/.default";
string tmpBody = "grant_type={0}&client_id={1}&client_secret={2}&scope={3}";
string body = string.Format(tmpBody, grantType, clientId, clientSecret, scope);
byte[] contents = Encoding.UTF8.GetBytes(body);
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uriToken);
request.ContentType = contentType;
request.Method = "POST";
request.ContentLength = contents.Length;
using (Stream writeStream = request.GetRequestStream())
{
writeStream.Write(contents, 0, contents.Length);
}
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream readStream = response.GetResponseStream())
{
string responseBody = string.Empty;
using (StreamReader sr = new StreamReader(readStream))
{
responseBody = sr.ReadToEnd();
}
JavaScriptSerializer Serializer = new JavaScriptSerializer();
var r = Serializer.Deserialize(responseBody);
token = r.access_token;
}
}
return token;
}
Then we follow the routine "search site => search list => get list item":
static void Main(string[] args)
{
string tenentId = string.Empty;
string clientId = string.Empty;
string clientSecret = string.Empty;
string siteName = string.Empty;
string siteId = string.Empty;
string listId = string.Empty;
Console.WriteLine("Tenent Id: ");
tenentId = Console.ReadLine();
Console.WriteLine("Client Id: ");
clientId = Console.ReadLine();
Console.WriteLine("Client Secret: ");
clientSecret = Console.ReadLine();
if (string.IsNullOrEmpty(tenentId) || string.IsNullOrEmpty(clientId) || string.IsNullOrEmpty(clientSecret))
{
Console.WriteLine("empty value found in mandatory parameters");
return;
}
string token = GetAccessToken(tenentId, clientId, clientSecret);
Console.WriteLine("Token acquired, query site");
Console.WriteLine("Input site name for search: ");
siteName = Console.ReadLine();
string uriSite = $"https://graph.microsoft.com/v1.0/sites?search={siteName}";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uriSite);
request.Method = "GET";
request.Headers.Add(HttpRequestHeader.Authorization, token);
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream s = response.GetResponseStream())
{
using (StreamReader r = new StreamReader(s))
{
string contents = r.ReadToEnd();
var j = JObject.Parse(contents);
siteId = j["value"]
.FirstOrDefault(x => x["name"].ToString().ToLower() == "msgraph")["id"]
.ToString();
}
}
}
if (string.IsNullOrEmpty(siteId))
{
Console.WriteLine("error happens, cannot obtain site id");
return;
}
Console.WriteLine($"site id acquired, {siteId}");
string uriList = $"https://graph.microsoft.com/v1.0/sites/{siteId}/lists";
request = (HttpWebRequest)WebRequest.Create(uriList);
request.Method = "GET";
request.Headers.Add(HttpRequestHeader.Authorization, token);
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream s = response.GetResponseStream())
{
using (StreamReader sr = new StreamReader(s))
{
string content = sr.ReadToEnd();
var jObject = JObject.Parse(content);
listId = jObject["value"]
.FirstOrDefault(x => x["name"].ToString().ToLower() == "events")["id"]
.ToString();
}
}
}
if (string.IsNullOrEmpty(listId))
{
Console.WriteLine("error occurred, cannot obtains list id");
return;
}
Console.WriteLine($"list id: {listId}");
Console.ReadLine();
}
