Click here to Skip to main content
15,941,973 members
Articles / Programming Languages / C#

Google Sheets in C# – How to Build Your Own Levels.fyi!

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
17 Feb 2024CPOL8 min read 4.3K   5  
Access Google Sheets via C#; Start simple, optimize later!
This article explores utilizing Google Sheets via C# and .NET, emphasizing spreadsheet potential for web data management and gradual optimization, along with authentication and accessing techniques.

Google Sheets in C# - How to Build Your Own Levels.fyi!

Many developers are surprised to learn that levels.fyi, known for its tech salary data, initially ran on spreadsheets without a backend database. This example shows the potential of spreadsheets in managing web data and how you can start with something that works to optimize it later. We can do the same thing with Google Drive and Google Sheets in C#!

In this article, I’ll guide you through accessing Google Sheets from Google Drive using C# and .NET. I’ll share the code I used in my own personal projects to make some of this work more effectively. Let’s get into it!

What’s in This Article: Google Drive & Google Sheets in C#

Setting Up Your Environment

Before we get too far into things, let’s make sure you have things setup properly. This will save you some time in the long run — I promise!

Google Sheets & Google Drive Nuget Packages

Before we start writing any code, ensure you have the necessary Google API packages installed. For this tutorial, we’re using:

These versions were current at the time of writing, so please adjust according to any future updates.

Authentication with Google Cloud Console

To interact with Google APIs in the same way that I’m illustrating in this tutorial, you’ll need a JSON credential file from the Google Cloud console. This involves creating a service account and sharing your Google Drive files with it, granting the necessary permissions. This step is critical for the API to access your spreadsheets.

To do this, make sure you log in to your Google Cloud console and create a new service account (or leverage an existing one). The reason a service account is beneficial here is that you get an associated email address with it, and you can share your files/folders with the email so that the service account gets permission. Without the correct permissions to your files, the API calls might be structured and called properly but they’ll fail to find any results!

If you’d like more visual guidance on some of these steps, you can follow along with this video tutorial on using Google Sheets in C#:

Implementing the Google Drive and Google Sheets in C#

Configuring Google Drive and Google Sheets APIs

The first step in our C# application involves referencing the Google Sheets and Google Drive APIs through NuGet packages. You did that part though, right? You should have similar entries to this in your csproj file (keeping in mind the versions will likely be different by the time you are reading this):

XML
<PackageReference Include="Google.Apis.Drive.v3" Version="1.66.0.3309" />
<PackageReference Include="Google.Apis.Sheets.v4" Version="1.66.0.3148" />

You’ll also need to ensure you get your credentials loaded up in code. This code illustrates setting up the authentication with our JSON credentials to establish a connection with Google’s services, using the correct scopes:

C#
var credential = GoogleCredential
    .FromJson(
    """
    {
      "type": "service_account",
      "project_id": "api-abc123abc123-456789",
      "private_key_id": "123456789123456789",
      "private_key": "-----BEGIN PRIVATE KEY----KEY HERE------END PRIVATE KEY-----\n",
      "client_email": "project-name@api-aabbcc-123456.iam.gserviceaccount.com",
      "client_id": "your client ID here",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/project-name%40api-aabbcc-123456.iam.gserviceaccount.com",
      "universe_domain": "googleapis.com"
    }
    """)
    .CreateScoped(
        DriveService.ScopeConstants.Drive,
        SheetsService.ScopeConstants.Drive,
        SheetsService.ScopeConstants.Spreadsheets);

Of course, this is a dummy credential file, but you get the idea. Also, consider that you don’t want to keep this in your source code but probably load it from some secure storage if you’re deploying this application (vs just running some code locally for fun)!

Finding Our File

Our main goal is to access and manipulate data within Google Sheets. However, finding the right spreadsheet can be challenging without knowing its unique ID. I don’t know about you, but when I use Google Drive and Google Sheets, I look up files and folders by their name and path… not by some hidden unique identifier.

We can tackle this by implementing a method to search for the spreadsheet using a path-like structure, despite Google Drive not natively supporting this feature:

C#
public sealed class GoogleDriveClient(
    DriveService _driveService)
{
    public async Task<GoogleDriveFile> GetFileOrFolderForPathAsync(
        string fullyQualifiedPath,
        CancellationToken cancellationToken)
    {
        Queue<string> pathPartQueue = new();
        foreach (string pathPart in fullyQualifiedPath.Split(['/', '\\']))
        {
            pathPartQueue.Enqueue(pathPart);
        }

        StringBuilder pathSoFar = new(fullyQualifiedPath.Length);
        string? parentId = null;
        while (pathPartQueue.Count > 0)
        {
            string pathPart = pathPartQueue.Dequeue();
            pathSoFar.Append(pathPart);

            FilesResource.ListRequest listRequest = CreateListRequest(
                _driveService,
                parentId,
                pathPart);

            FileList items = await listRequest
                .ExecuteAsync(cancellationToken)
                .ConfigureAwait(false);
            if (items.Files.Count == 0)
            {
                throw new InvalidOperationException(
                    $"Could not find a match for '{pathSoFar}'.");
            }
            else if (items.Files.Count > 1)
            {
                throw new InvalidOperationException(
                    $"Multiple matches for '{pathSoFar}'.");
            }

            if (pathPartQueue.Count == 0)
            {
                return items.Files[0];
            }

            parentId = items.Files[0].Id;
            pathSoFar.Append('/');
        }

        throw new InvalidOperationException(
            $"Could not find a match for '{fullyQualifiedPath}'.");
    }

    private static FilesResource.ListRequest CreateListRequest(
        DriveService driveService,
        string? parentId,
        string pathPart)
    {
        var listRequest = driveService.Files.List();
        listRequest.SupportsAllDrives = true;
        listRequest.IncludeItemsFromAllDrives = true;
        listRequest.PageSize = 2;
        listRequest.Q = $@"name = '{pathPart}'";

        if (parentId != null)
        {
            listRequest.Q += $@" and '{parentId}' in parents";
        }

        return listRequest;
    }
}

The code above uses a path to a file or folder in Google Drive to be able to query each “path part” until the file or folder is found. If no such file or folder is found or there are multiple matches, an exception is thrown.

Accessing Google Sheets Spreadsheets in C#

Now that we have the file, we have the magical identifier that we need to access our Google Sheet spreadsheet in C#! Let’s look at the following code:

C#
public sealed class SpreadsheetProcessor(
    GoogleDriveClient _googleDriveClient,
    SheetsService _sheetsService)
{
    public async Task ProcessSpreadsheetAsync(
        string pathToFileOnGoogleDrive,
        CancellationToken cancellationToken)
    {
        var file = await _googleDriveClient.GetFileOrFolderForPathAsync(
            pathToFileOnGoogleDrive,
            cancellationToken);

        var spreadsheet = await _sheetsService
            .Spreadsheets
            .GetByDataFilter(
                new()
                {
                    IncludeGridData = true,
                },
                file.Id)
            .ExecuteAsync(cancellationToken)
            .ConfigureAwait(false);

        foreach (var rowData in sheet.Data[0].RowData)
        {
            // TODO: do something with the row data!
        }
    }
}

In the code above, we look for the spreadsheet at the particular path. From there, we open it up with the sheets service because we have the ID of the file that was returned. Once we have a spreadsheet object, we can use the Google Sheets API to interact with it accordingly.

Future Considerations for Using Google Drive and Google Sheets in C#

In the code that we saw above, it’s by no means optimized for performance. If you have to do multiple lookups, even for the same file path… you’ll be stuck doing multiple queries. If you want to minimize the number of API calls, you could consider caching the paths to ID mapping locally. This would allow you to shortcut if you had a partial path match and skip having to do multiple path-part lookups — or jump right to the file ID if you’ve seen the exact path already.

You could expand this logic to work with other file types from Google as well! Instead of just spreadsheets, what about documents? When we consider other document types, something you might have noticed is that there aren’t extensions for spreadsheets (at least in my experience) on Google Drive. So what happens to our API calls if we have a document and a spreadsheet with the same name? Is it even possible in Google Drive? Try it out!

Wrapping Up Google Sheets in C#

This guide has walked you through the basics of using the Google Sheets API in C# to access and manipulate data stored in Google Drive. You can now build applications leveraging data stored in your Google Drive by coding them up in dotnet!

The initial setup and authentication we used involved getting a JSON credential from Google Cloud console. Beyond that, we had to navigate down to the file in question by going each part of a path at a time — unless you want to work with the file IDs directly!

Whether you’re looking to build a data-driven website or manage content schedules (which is what I was doing!), these Google APIs can help you out! Feel free to experiment with these concepts in your projects and explore the full capabilities of the Google Sheets and Drive APIs in your .NET applications. If you found this useful and you’re looking for more learning opportunities, you can check out my free videos on YouTube!

Frequently Asked Questions: Google Drive & Google Sheets in C#

Can I use Google Sheets as a database for my C# application?

Yes! You can use Google Drive APIs and Google Sheets APIs to manage a dynamic data store for your C# applications by accessing and manipulating spreadsheets. While this may not be a suggested scalable approach for large high-performance applications, it could certainly work for smaller use cases to get started!

What packages do I need to work with Google Sheets in C#?

If following the tutorial in this article, you’ll need the Google Sheets API (v4) and Google Drive API (v3) packages. These NuGet packages are essential for interacting with Google’s services in a C# application.

How do I authenticate my C# application with Google’s services?

Authentication requires a JSON credential file from the Google Cloud console. The article outlines creating a service account, obtaining the JSON credentials, and sharing your Google Drive files with this account for the necessary permissions.

How can I find a specific file in Google Drive using C#?

You can search for a spreadsheet using a path-like structure, despite Google Drive not supporting this feature natively. This allows developers to locate files by name and path instead of unique identifiers. This is accomplished by going to each part of the path to look for a sub-folder before reaching the target file.

What are potential future considerations for using Google Drive and Google Sheets in C#?

Consider optimizing performance by caching paths to ID mappings locally to minimize API calls. The article suggests expanding the logic to work with other file types and handling documents and spreadsheets with the same name.

How can Google Sheets and Google Drive APIs enhance my C# projects?

Using these APIs, you can build data-driven applications, manage content schedules, and leverage Google Drive’s storage capabilities within your .NET applications, enabling a wide range of functionalities from data analysis to content management.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader Microsoft
United States United States
I'm a software engineering professional with a decade of hands-on experience creating software and managing engineering teams. I graduated from the University of Waterloo in Honours Computer Engineering in 2012.

I started blogging at http://www.devleader.ca in order to share my experiences about leadership (especially in a startup environment) and development experience. Since then, I have been trying to create content on various platforms to be able to share information about programming and engineering leadership.

My Social:
YouTube: https://youtube.com/@DevLeader
TikTok: https://www.tiktok.com/@devleader
Blog: http://www.devleader.ca/
GitHub: https://github.com/ncosentino/
Twitch: https://www.twitch.tv/ncosentino
Twitter: https://twitter.com/DevLeaderCa
Facebook: https://www.facebook.com/DevLeaderCa
Instagram:
https://www.instagram.com/dev.leader
LinkedIn: https://www.linkedin.com/in/nickcosentino

Comments and Discussions

 
-- There are no messages in this forum --