The post Google Sheets in C# – How to Build Your Own Levels.fyi! appeared first on Dev Leader.
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!
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 CSharp
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):
<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:
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:
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 CSharp
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:
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 CSharp
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 CSharp
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, consider subscribing to my free weekly software engineering newsletter and check out my free videos on YouTube!
Want More Dev Leader Content?
- Follow along on this platform if you haven’t already!
- Subscribe to my free weekly software engineering and dotnet-focused newsletter. I include exclusive articles and early access to videos: SUBSCRIBE FOR FREE
- Looking for courses? Check out my offerings: VIEW COURSES
- E-Books & other resources: VIEW RESOURCES
- Watch hundreds of full-length videos on my YouTube channel: VISIT CHANNEL
- Visit my website for hundreds of articles on various software engineering topics (including code snippets): VISIT WEBSITE
- Check out the repository with many code examples from my articles and videos on GitHub: VIEW REPOSITORY
Top comments (0)