Mihai-Albert.com

You just want to modify the content of a few cells in an Excel file stored in Sharepoint Online, using C#. A simple goal. Maybe your experience with Sharepoint APIs is fairly limited, so you spend a while researching. There’s questions over at Stack Overflow, that mention “CSOM”, “REST APIs”, “Graph” and various other cryptic terms, but aside the various limitations with each API, a concise and clear sample proves to be elusive.

TL;DR If you’re in a hurry to get to the sample code, skip right to it.

The goal of this post is to use Microsoft Graph to read and write to an Excel file stored in Sharepoint Online. As the main focus is getting to use Graph against Sharepoint Online just to get to an Excel file, in terms of operating against the file itself we’ll be content with just reading and updating the value of a single cell.

What will be discussed next:

• We’ll setup the right assumptions and expectations
• Have a look at what we need to start working towards a solution
• Build in Graph Explorer the queries we’ll eventually use
• Set up the Azure app that will sit behind the queries
• Discuss about the 2 types of Azure Active Directory permissions and why is it relevant to our goal
• Look at the main parts of the code we’ll write
• The actual code that reads and updates the content of one cell
• Briefly talk around some of the limitations around Microsoft Graph

Assumptions / Expectations

• This article is not an intro to Microsoft Graph. For a good overview read the first 2 referenced posts here
• Sharepoint CSOM (client side object model), itself a very much alive technology which pre-dates Microsoft Graph and offers programmatic access to Sharepoint, is not considered in this post. Instead, only access through Microsoft Graph is discussed
• In terms of the language used for the implementation, only C# is considered
• Both .NET Core and .NET Framework are in the scope of this article’s discussion
• We’ll only be looking at an daemon app scenario, that is an app that can run without any user logged on

As for the last assumption mentioned, why care about this type of apps (daemon)? Because they come in handy when handling automated workflows, whose individual parts need to run without user interaction. One such example would be a tool that reads and writes to an Excel file which acts as an IPAM (IP address management) solution, which in turn hands IPs to terraform in order for the latter to provision new VMs. The tool would have to retrieve the IPs marked as available, and – once new VMs are provisioned – mark the respective IPs as allocated in the Excel file. Sky’s the limit – but as stated, all we want for now is to retrieve the contents of a cell and then modify its value within the Sharepoint Online-stored Excel file.

A Start

So how do we go about the task at hand? Luckily there is currently quite some documentation around Microsoft Graph. A good starting point is here. A lot of valuable information is provided, included some key points needed forward:

• We’ll need permissions assigned in order to call the various APIs, as follows (doc here):
  • Files.Read (for read actions)
  • Files.ReadWrite (for read and write actions)
• An OAuth access token will be required in order to authenticate to Microsoft Graph
• Using a session is recommended, in order to make efficient use of the resources. However using the “sessionless” mode (presented here) will result in the changes made to be persisted to the target file. To keep things simple, we’ll use the sessionless mode

How do we test what we can do with this Graph API before starting to write some actual C# code? We’ll use Microsoft’s own Graph Explorer. Do we need to concern ourselves with any of the 3 pieces of information described above (permissions, access tokens, session) while testing in Graph Explorer? Only briefly, as we’re about to see.

Graph Explorer in Action

What we’d like to see first is whether Microsoft Graph can be used for our goal, and if so, what are the REST calls required to get to the data we’re after. The first thing we need to do is login with an identity. Once this is done, a prompt will be displayed, asking for a set of minimum permissions for the Graph application:

The Graph Explorer comes with a couple of sample queries, grouped per technology. We’ll be interested in the “Excel” and the “Sharepoint Sites” categories, as they’ll serve as a start point in our quest.

Since we’ll be running test queries, we need to get specific with the location of our Excel file: our target file is stored in the root site*, under the default “Shared Documents” document library within Sharepoint Online, and its name is “TestRun.xlsx”. We’re interested in the second worksheet in this workbook, called “Second” and we’ll first be reading -and later writing – only the cell on the first row / first column. Here’s the file itself, as rendered in Excel Online:

There are a couple of steps along the way of constructing the REST calls to Microsoft Graph for both reading and writing our target file, as follows:

• If you’ll browse through the “Excel” category of sample queries, you’ll notice that all of the queries require a driveItem id. Why is this so? Because as per the documentation “the driveItem resource represents a file, folder, or other item stored in a drive. All file system objects in OneDrive and SharePoint are returned as driveItem resources“. Therefore our Excel file will have a driveItem id, which must be specified in the call
• The driveItem id chains to a drive id. As per the documentation, “the drive resource is the top level object representing a user’s OneDrive or a document library in SharePoint“. Since our Excel file is stored in the “Shared Documents” document library, we’ll need to get its drive id as part of constructing the REST calls
• Either the worksheet name, or the worksheet id – uniquely identifying the worksheet within the target workbook – is needed

Let’s start resolving the required names/ids hierarchically. We first need the drive id of the “Shared Documents” document library. The Office365 portal used in this article – being a vanilla one that has just been recently created, including a default installation of Sharepoint Online – will contain just this particular document library.

Using the “Enumerate the document libraries” sample query – which does a GET against https://graph.microsoft.com/v1.0/sites/root/drives will yield the drive id we’re after:

Next we need the driveItem id of the Excel file. We can obtain it by searching for our particular file underneath the drive id of the document library, which has just been obtained. For this we’ll build our query by appending the drive id, and issuing a GET against https://graph.microsoft.com/v1.0/drives/b!blLUM58i3kKXJE06kbACzO-GG-zZ-6NBvvfo-vpZ8pQK4D0MFCJqTIMG6ouDJVoa/root/search(q='TestRun.xlsx')?select=name,id,webUrl. Running this as is will most likely get a 403 (forbidden) back, as the permissions granted so far to Microsoft Graph app are limited* (look back at figure 1). Go ahead and grant yourself the Files.Read.All permission under “modify permissions”, consent to the new rights then run the query. You should get back the driveItem id of the Excel file:

Now we can issue a call to get basic information about the “Second” worksheet we’re targeting, within the Excel file. We just do a GET against https://graph.microsoft.com/v1.0/drives/b!blLUM58i3kKXJE06kbACzO-GG-zZ-6NBvvfo-vpZ8pQK4D0MFCJqTIMG6ouDJVoa/items/01XMM4AJ4I4FXD5YTF6ZD34QD4T2FBRUIW/workbook/worksheets('Second'). The outcome is below:

At this point we can construct the API call required to get the contents of the first cell: https://graph.microsoft.com/v1.0/drives/b!blLUM58i3kKXJE06kbACzO-GG-zZ-6NBvvfo-vpZ8pQK4D0MFCJqTIMG6ouDJVoa/items/01XMM4AJ4I4FXD5YTF6ZD34QD4T2FBRUIW/workbook/worksheets('Second')/range(address='A1:A1'). The expected result (as in figure 2) is below:

Updating the cell’s content uses the same exact URL, but instead of GET we’ll issue PATCH, and we’ll also specify the payload containing the new content of our target cell. You’ll need to grant yourself Files.Write.All permissions in the Graph Explorer interface, by using the “modify permissions” option, in a similar way to how it was done for the command that retrieved the data in the first place. The query, payload and partial result can be seen below:

As of now (May 2020), the preview version of the Graph Explorer – which will most likely become the norm going forward – has the ability of showing the code snippets for the constructed API calls in various languages, among them C# as well. There are still quirks with it however – for example sometimes the permissions displayed as required for a particular query are in fact not required to successfully run it – but they’ll probably get ironed out going further. Update 5/15/2020: the former “preview” version of Graph Explorer is now GA.

Armed with all this information, let’s turn to what’s required to get the queries we’ve just built across in our C# code.

Application Prerequisites

Our daemon app will need an identity that can be validated against Office 365 tenant’s underlying identity directory – Azure Active Directory (AAD). Then when it’s going to be used, once authentication is performed and authorization completed in the form of an OAuth access token issued by AAD, it will be able to access the Excel file within Office 365’s Sharepoint Online.

According to the relevant docs, in order to authenticate, this app can provide either a secret or a certificate. To simplify, in our sample we’ll be using a secret.

Let’s register a new application in AAD, whose details we’ll then be able to use in our C# code. The docs (one here) are straightforward in how to get this done. You’ll want the default values, as in “accounts in this organizational directory only” can access this application, and an empty value for the redirect URI. In the end, you’ll end up with something like this:

We’ll also need to create a secret, in order to have a way to authenticate to AAD with the recently created application id. Documentation on how to do this is here, and the outcome for our app is below:

A Word About Permissions

If you look at the Azure AD Permission Types (link), you’ll notice that there are only 2 types of permissions: delegated permissions (used by apps that have a signed-in user present) and application permissions (used by apps that run without a signed-in user present).

With Graph Explorer, you’re using a specific user to authenticate. By default, you’re granting minimal rights to the Graph Explorer app (figure 1). As per the link above: “For delegated permissions, the effective permissions of your app will be the least privileged intersection of the delegated permissions the app has been granted (via consent) and the privileges of the currently signed-in user“. So even though you can write just fine to that Sharepoint Online file, to which your user account already has access, Graph Explorer can’t. Or in other words, your account has permissions for that particular file, but the access token that Azure Active Directory issues to you for use with the Graph Explorer application does not. This is why one has to amend permissions within Graph Explorer.

For our daemon app however, we’ll be using application permissions. As per the same link: “effective permissions of your app will be the full level of privileges implied by the permission” in the sense that there’s no intersection involved to get to the effective rights, as was with delegated permissions. Access is also not restricted per a set of users for the application permissions, but instead the app gets to use whatever application permissions it has been assigned at a global level. Note that simply having an application permission granted without admin consent is essentially worthless, as “applications must use the admin consent endpoint to request Application Permissions” as per this link. This means that both granting the application permissions and providing admin consent is required before those permissions become effective.

Back to our app, make sure File.ReadWrite.All application permissions are granted to the Microsoft Graph API, and that admin consent is supplied too. The final permissions are below:

Let’s move on now closer to the equivalent C# code.

High Level View of What’s Needed

Looking over the documentation, there are 2 main things that need to be done:

• A GraphServiceClient instance needs to be created. This is detailed here. Creating this instance depends on an authentication provider
• Requests can then be sent across to the Graph endpoint, using the GraphServiceClient instance previously obtained. The docs cover this here

Each application type has a set of authentication providers that it can use for Microsoft Graph. As our application will be a daemon-type app, we’ve got only one authentication provider we can choose according to the docs: the Client Credentials Provider.

3 pieces of information are required in order to use this provider:

• The client id (aka application id): we’ve seen this back in figure 8
• The tenant id: same as the client id, this was shown when we registered the app; this can also be seen in the Azure Portal Azure Active Directory’s Overview tab
• The client secret: Refer back to figure 9

This is pretty much all that’s needed to get our C# code ready.

How about updating the Excel file? The documentation is quite clear, and “most updates in Microsoft Graph are performed using a PATCH method and therefore it is only necessary to include the properties that you want to change in the object you pass“. Our goal is to simply update one cell, so we’ll simply pass the new content of the cell in the respective call.

Note that the Graph client will handle the underlying complexity for us, meaning we won’t be concerned with either retrieving an access token from Azure nor how to use this explicitly to call Microsoft Graph.

Roll Out

At this stage we’re ready to put our code together. We’ll use a C# Console application. The Microsoft Graph SDK needs to be installed first, and the docs cover this well. It consists of 2 NuGet packages:

• Microsoft.Graph
• Microsoft.Graph.Auth (click the prerelease checkbox in the UI, otherwise it won’t be listed, since it’s currently version 1.0.0-preview.4)

If you will look at both packages’ dependencies, you’ll notice that both .NET Core (>1.0, as part of .NET Standard 1.3) and .NET Framework (>4.5) are supported. As such, for the sample code used in this post – which uses a rather limited set of Microsoft Graph’s features – as well as its outcome, there is no difference between targeting either .NET Core or the “classic” .NET Framework when working with the Graph API.

Next using Microsoft.Graph; needs to be added. For the authentication providers, using Microsoft.Identity.Client; and using Microsoft.Graph.Auth; will be required also.

We’ll write to the Excel file using a WorkbookRange variable; specifically we’ll set the Values field. Care must be taken so this is represented as expected, namely JSON, as the documentation states.

Full code follows:

using Microsoft.Graph;
using Microsoft.Graph.Auth;
using Microsoft.Identity.Client;
using System;

namespace GraphWithSPOandExcel
{
    class GraphWithSPOandExcel
    {
        public async static void UpdateExcelFileInSPO()
        {
            Console.WriteLine("App started");
            // Build a client application.
            IConfidentialClientApplication confidentialClientApplication = ConfidentialClientApplicationBuilder
                .Create("7138f3c3-d409-4c61-879d-99fde6d79095")
                .WithTenantId("022d92de-141e-4cb1-8578-e9af93f8ea31")
                .WithClientSecret("It[0r.7::Z11jDd[2/xZVhA8gRj_p-Qd")
                .Build();
            ClientCredentialProvider authProvider = new ClientCredentialProvider(confidentialClientApplication);
            // Create a new instance of GraphServiceClient with the authentication provider.
            GraphServiceClient graphClient = new GraphServiceClient(authProvider);

            // Retrieve the contents of the first cell
            WorkbookRange range = await graphClient.Drives["b!blLUM58i3kKXJE06kbACzO-GG-zZ-6NBvvfo-vpZ8pQK4D0MFCJqTIMG6ouDJVoa"].Items["01XMM4AJ4I4FXD5YTF6ZD34QD4T2FBRUIW"].Workbook.Worksheets["Second"].Cell(0,0)
                .Request()
                .GetAsync();
            Console.WriteLine("Value retrieved: {0}", range.Values);
            
            // Build the new value that will be pasted in the first cell
            WorkbookRange newContent = new WorkbookRange
            {
                // Inspired from https://github.com/microsoftgraph/botframework-csharp-excelbot-rest-sample/blob/master/ExcelBot/Workers/CellWorker.cs#L64
                //   [Values = JToken.Parse($"[[\"{value}\"]]")]
                Values = Newtonsoft.Json.Linq.JToken.Parse("[[ \"dog\" ]]")
            };

            // Update the content of the first cell
            range = await graphClient.Drives["b!blLUM58i3kKXJE06kbACzO-GG-zZ-6NBvvfo-vpZ8pQK4D0MFCJqTIMG6ouDJVoa"].Items["01XMM4AJ4I4FXD5YTF6ZD34QD4T2FBRUIW"].Workbook.Worksheets["Second"].Range("A1:A1")
                .Request()
                .PatchAsync(newContent);
                
            Console.WriteLine("Value written: {0}", range.Values);
        }

        static void Main(string[] args)
        {
            GraphWithSPOandExcel.UpdateExcelFileInSPO();

            // Wait now so that the process doesn't terminate, which would render
            //  the async callback mechanism useless
            Console.WriteLine("Press Enter to exit...");
            Console.ReadLine();
        }
    }
}

Current Limitations

Keep in mind that some things for Microsoft Graph are still in preview, and some – even though documented in a way, don’t quite work as intended yet.

For example you don’t get .Format as a composable function for now, meaning you can’t write some concise code to update a cell’s or range’s format. This is captured in this issue and this issue, connected to this StackOverflow issue.

Within the documentation, C# snippets are sometimes shown and sometimes they’re not (eg this link doesn’t show them, but others do, such as this one, yet the code doesn’t compile, for the reasons mentioned previously.

You might also get weird behavior with Graph Explorer, when you can’t select the verb of the query in the drop-down. If that happens, try switching browsers.

We’ll finish with a Q&A section.

Q & A

Q: This article shows the process of locating a file when this is stored in a document library under the default site. But what if the Excel file is stored in a document library that’s not within the default site? How can the driveItemId be found then? Suppose the Excel file is named “TestRunInAlternateSite.xlsx” and it’s stored under the “Shared Documents” document library within the “ACME” site.
A: First you need to identify the respective site. For our example, this can be done using https://graph.microsoft.com/v1.0/sites?search=acme. Note the site id returned, right after the site name, highlighted below in the first picture. Next we’ll need the document library id where the file is stored. We’ll query using the site id obtained previously, to https://graph.microsoft.com/v1.0/sites/1cbd1822-0795-46a3-8d06-0b4ad7f46a57,22419e84-6640-41ff-afc5-96c407d0d7b0/drives in order to list all the document libraries under the “ACME” site, and capture the id of the target one (2nd picture below). Now we’re ready to build the final query: https://graph.microsoft.com/v1.0/sites/1cbd1822-0795-46a3-8d06-0b4ad7f46a57,22419e84-6640-41ff-afc5-96c407d0d7b0/drives/b!Ihi9HJUHo0aNBgtK1_RqV4SeQSJAZv9Br8WWxAfQ17BQlVs52wu2Ta5DaKcPljZS/search(q='TestRunInAlternateSite.xlsx')?select=name,id. Note the driveItemId of the Excel document returned in the 3rd picture below. One final note: you’ll need the Sites.Read.All permission as well (in both Graph Explorer, if testing there, as well as against the Azure app).

Q: I’m getting Microsoft.Graph.ServiceException: Code: InvalidArgument Message: The argument is invalid or missing or has an incorrect format. What am I doing wrong?
A: If you’re issuing a PATCH command, check that the payload you’re sending has a valid syntax. Eg using Values = new Newtonsoft.Json.Linq.JArray(new string("IP Address")) instead of the original line in the sample code shown will throw this exception, since the resulting payload is missing a set of square brackets. The easiest way to understand what’s wrong is to cross-check the Graph Explorer request data with what you’re actually sending programmatically down the wire. For the latter, Fiddler can be used.

Q: How can I use Fiddler to gather more insight about the queries sent to the Microsoft Graph endpoint?
A: Once HTTPS decryption has been configured, and the relevant traffic has been captured, look for either the frame containing the reply from Azure Active Directory, in which the response should contain the OAuth access token, or for the frame containing the request to the Graph endpoint itself and analyze the authentication data in the payload sent. The former is part of frame 4 below (itself part of a set of frames: 2, 3, 4), however the request and reply are not pictured. Instead the latter approach can be seen in detail below – the selected frame actually belongs to the first query our sample code is doing to get the contents of the first cell (the subsequent frame contains the update query). Note the OAuth access token highlighted in red, which is sent along with the request. The token is base64 encoded, however the data inside is not encrypted, but only signed. The token’s content can be viewed using a JWT token parser such as https://jwt.ms/. If you’re cross-checking the output of a query you’ve tested in Graph Explorer against your own code, Fiddler can be used to obtain the token requested by your app as it’s seen down the wire, and compared to the one seen in the “Access token” tab in Graph Explorer after they’ve both been decoded. Just keep in mind that in the context of the daemon app we’ve analyzed, there’s the distinction between user permissions and app permissions, discussed previously in this post under this section, and as such the token in Graph Explorer will be issued to a user, while the other one will be issued to the Azure app used.

Q: I’m getting Microsoft.Graph.ServiceException: Code: AccessDenied Message: Either scp or roles claim need to be present in the token. when running my code. What’s wrong?
A: You’re either missing the required permissions for the app or the admin consent for those permissions. Use Fiddler to extract the OAuth token you’re leveraging in order to understand what permissions does Azure know that your app has. Once obtained, send this to a JWT token parser – eg  https://jwt.ms/ – and look at the roles claim. If you’re not seeing the permissions you know you should have (for the sample code described in this post it’s Files.Read.All and/or Files.ReadWrite.All). Note that simply having an application permission granted without admin consent is essentially worthless, as “applications must use the admin consent endpoint to request Application Permissions” as per this link. Additionally, this specific error message indicate that there’s actually not even one permission to be found for the application based on the OAuth token it was issued.

Q: I’m getting Microsoft.Graph.ServiceException: Code: EditModeAccessDenied Message: You do not have sufficient permissions to edit this workbook. when running my code. What’s wrong?
A: This one is pretty self-explanatory. Check if you have Files.ReadWrite.All granted as an application permission. Also check that the permission did became effective; you can use the flow described in the previous question to check this (Fiddler+JWT parser).

Q: How long does it take for the permissions to become effective once granted to an Azure app?
A: It might take a few mins for the permissions to become effective – either if a new application permission has been granted, or admin consent has been assigned – so allow time for this when debugging or testing your code. Allow sufficient time and avoid drawing conclusions regarding the exact minutes it takes: from my tests, new permissions could be effective in as little as a few seconds, or as much as 10 minutes.

Q: Even though I have access to a specific file in a specific Sharepoint Online document library, Graph Explorer returns “403 Forbidden”. Why?
A: If you look at the Azure AD Permission Types (link), you’ll notice that there are only 2 types of permissions: delegated permissions (used by apps that have a signed-in user present) and application permissions (used by apps that run without a signed-in user present). With Graph Explorer, you’re using a specific user to authenticate. By default, you’re granting minimal rights to the Graph Explorer app (figure 1). As per the link above: “For delegated permissions, the effective permissions of your app will be the least privileged intersection of the delegated permissions the app has been granted (via consent) and the privileges of the currently signed-in user“. So even though you can write just fine to that Sharepoint Online file, to which your user account already has access, Graph Explorer can’t. Or in other words, your account has permissions for that particular file, but the access token that Azure Active Directory issues to you for use with the Graph Explorer application does not. The outcome is the error message you get. To fix it, just allocate the required permissions by clicking on “modify permissions”. Once you consent to the new permissions, Graph Explorer will run just fine.

Q: Where can I find additional info about Microsoft Graph?
A: There’s a 30-day walkthrough here https://developer.microsoft.com/en-us/graph/blogs/announcing-30-days-of-microsoft-graph-blog-series/#. For details around the Excel API, things are pretty well documented, but just make sure you notice the versions being discussed. Eg as of now (May 2020) there’s a lot of documentation, but some of it targets the beta version.

Q: What other APIs are there to interact with Excel files stored in Sharepoint Online?
A: There’s CSOM, but that’s not supported by .NET Core for now. Also, I couldn’t get it to work for now with the simple task of updating a single cell as this post did with Graph, but maybe that’s my fault (SO question here).

Q: Can I use the older .xls Excel file format with Microsoft Graph?
A: No. The documentation here is quite clear about this: “The Excel REST API supports only Office Open XML file formatted workbooks. The .xls extension workbooks are not supported.“

Q: Do I really need Files.Read.All or Files.ReadWrite.All when working with Graph Explorer?

A: Files.Read and File.ReadWrite respectively would have been sufficient for reading and writing to an Excel file that we already had access to, in the context of Graph Explorer. These 2 permissions grant the respective type of access to the files the logged-in account has access to, and they would have both been suited for their respective scenario. However for the application permissions that our app requires this is not really applicable, as an app doesn’t have files that someone shared with it, and as such, File.Read.All (just for reading) and File.ReadWrite.All (for writing, and for reading) respectively are needed. As such the .All permissions were used in Graph Explorer, to have consistency within the article and keep things simple.

Q: Can I use named, double-colon-ended references to avoid getting the drive id and driveitem ids?
A: With sites, it’s possible to use the named double-colon entry style, as described here. However I’m not aware of a way to use it for drives and their contents.

Q: In the context of Sharepoint Online, how would you know you’re dealing with a Sharepoint CSOM-based implementation, as opposed to a Microsoft Graph one?
A: Just by looking over the wire, the endpoint used for CSOM will contain client.svc in the URL, as described here, and you’ll be able to see this invoked with a tool like Fiddler. For Graph however, an endpoint of the form https://graph.microsoft.com/.... will be seen, as per the documentation here. If you have access to the C# project or source code, you can look at the NuGet packages installed, with CSOM using Microsoft.SharePointOnline.CSOM (and its dependencies), while Graph uses Microsoft.Graph and Microsoft.Graph.Auth (and their respective dependencies).

Q: Assuming I have the target file opened in Excel Online, and code similar to the one discussed in this post is run, will the change be reflected in real time, or would one have to refresh Excel Online in order to see it?
A: The change done programmatically will be reflected in Excel Online, without a need to refresh, but it might take around 30 seconds for it to happen. Here’s the outcome for our sample code:

Q: I’m getting Microsoft.Graph.ServiceException: Code: InvalidArgument
Message: The argument is invalid or missing or has an incorrect format. when trying to update a Range in an Excel file using Microsoft Graph. I have the necessary permissions. What can be wrong?
A: If you look back at the code in this post, you’ll notice that unlike cells, whose column and row are 0-based, the ranges start from 1. As such, using a range that references eg “A0” will throw the exception mentioned.

Q: I’m trying to look inside the Excel file to make sure the changes are reflected, but every time I enter the URL, the browser downloads the file. How can I get it rendered within the browser?
A: Just add ?web=1 to the URL and it’ll be opened in Excel Online.

Q: I’m using the UsedRange() method described here to only get the range of actual cells that have been written to in a worksheet. However when running my code I get the following exception: The cell count in range has exceeded the maximum supported number. What’s wrong?
A: Check that there’s not a large number of empty columns in your worksheet aside from the “real” data. Look for columns that have an AutoFilter set against them, as those will count as well. Remove all the empty columns (select all, then delete) and the method should work as expected, unless the file is really huge. You can safely ignore cells that just have custom colors (eg whose background is filled with a specific color), as those won’t be treated as part of the used range anyway.