How to read data from an Excel file in SharePoint using CSOM

Question

How to retrieve data from a spreadsheet in SharePoint using the Graph API. I already have an App in Azure AD with the necessary permissions, and I have the clientId, tenantId, and clientSecret.

I'm using the code below for testing, but the context.ExecuteQuery() is returning a 401 error.

public async void Test()
{

    string clientId = "xxxxx";
    string clientSecret = "xxxxx";
    string tenantId = "xxxxxx";

    string siteUrl = "https://xxxx.sharepoint.com/sites/ProjetoDSS";
    string driveName = "BKP";
    string fileName = "bkp_2023-10-05_13_05_29.xlsb";

    string authority = $"https://login.microsoftonline.com/{tenantId}";
    var appConfidential = ConfidentialClientApplicationBuilder.Create(clientId)
        .WithClientSecret(clientSecret)
        .WithAuthority(new Uri(authority))
        .Build();

    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

    var authResult = appConfidential.AcquireTokenForClient(new[] { "https://graph.microsoft.com/.default" })
        .ExecuteAsync().GetAwaiter().GetResult();

    using (var context = new ClientContext(siteUrl))
    {
        context.AuthenticationMode = ClientAuthenticationMode.Default;
        context.ExecutingWebRequest += (sender, e) =>
        {
            e.WebRequestExecutor.RequestHeaders["Authorization"] = "Bearer " + authResult.AccessToken;
        };

        Web web = context.Web;
        context.Load(web);

        try
        {
            context.Load(web, a => a.ServerRelativeUrl);

            context.ExecuteQuery();

            FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, "/sites/ProjetoDSS/11_10_2023_08_51.xlsb");
            context.ExecuteQuery();

            var filePath = @"c:\abc\Test\11_10_2023_08_51.xlsb";
            using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create))
            {
                fileInfo.Stream.CopyTo(fileStream);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Erro: {ex.Message}");
        }
    }
}

Answer 1

Hi @Dan Quenaz Magdyan Silva Pimentel，

As far as I know,

CSOM should be used for SharePoint API, so it requires SharePoint API related permissions. If you are using Graph API then use .NET SDK instead of csom.

If you persist, please refer to the document:

And your error is 401，it's likely related to authentication and permissions.

Ensure the following:

The Azure AD app registration associated with your SharePoint site has the necessary permissions to access SharePoint resources, especially files.

You have granted admin consent for the required permissions.

Users or service principals have consented to the permissions, especially if you are acquiring tokens on behalf of users.

Check that the "siteUrl" is accurate and corresponds to a SharePoint site where the app registration has access.

Here is a link for your reference:

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/using-csom-for-dotnet-standard

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng