Unlocking Google Sheets with C#: Your Guide to Data Management
Written on
Chapter 1: Introduction to Google Sheets in C#
Many developers might be surprised to discover that levels.fyi, a popular platform for tech salary information, originally operated solely on spreadsheets without any backend infrastructure. This case illustrates the power of spreadsheets for managing web data and emphasizes how you can start with a functioning solution and later refine it. In this article, I'll show you how to connect to Google Sheets through Google Drive using C# and .NET. I'll share some of the code I've utilized in my own projects to enhance this process. Let's dive in!
Subscribe to my free weekly software engineering newsletter!
Section 1.1: Preparing Your Development Environment
Before we proceed further, let's ensure your environment is properly configured. This preparation will save you time in the long run—trust me!
Google Sheets and Drive NuGet Packages
Before writing any code, confirm that you have the necessary Google API packages installed. For this tutorial, we will utilize:
- Google Sheets API (v4)
- Google Drive API (v3)
These versions were accurate at the time of writing, so make sure to adjust based on any future changes.
Authenticating via Google Cloud Console
To work with Google APIs as demonstrated here, you need a JSON credential file from the Google Cloud Console. This entails creating a service account and sharing your Google Drive files with it to grant the necessary permissions. This step is crucial for the API's access to your spreadsheets.
Log in to your Google Cloud console, create a new service account (or use an existing one), and share your files or folders with the associated email address. This way, the service account will have the required permissions. Without these permissions, even correctly structured API calls will fail to yield any results!
If you seek a more visual explanation of these steps, you can follow along with this video tutorial on using Google Sheets in C#:
The video titled "Google Sheets and .NET Core with C#" provides an in-depth look at implementing Google Drive and Google Sheets in C#.
Section 1.2: Configuring Google Drive and Sheets APIs
The initial step in our C# application is to reference the Google Sheets and Google Drive APIs through NuGet packages. You should have similar entries in your csproj file (note that versions may differ by the time you are reading this):
You also need to load your credentials in the code. Here’s an example of how to set up authentication using our JSON credentials to connect to Google's services with the appropriate 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": "[email protected]",
"client_id": "your client ID here",
"universe_domain": "googleapis.com"
}
""")
.CreateScoped(
DriveService.ScopeConstants.Drive,
SheetsService.ScopeConstants.Drive,
SheetsService.ScopeConstants.Spreadsheets);
This credential is a placeholder, so remember to keep your actual credentials secure, especially when deploying the application.
Chapter 2: Searching for Your File
Our primary aim is to access and manipulate data in Google Sheets. However, locating the right spreadsheet can be tricky without its unique ID. When I navigate Google Drive and Sheets, I typically search for files and folders by their names and paths rather than their hidden unique identifiers.
To address this, we can develop a method that searches for the spreadsheet using a path-like structure, despite Google Drive not inherently supporting this functionality:
public sealed class GoogleDriveClient(
DriveService _driveService)
{
public async Task GetFileOrFolderForPathAsync(
string fullyQualifiedPath,
CancellationToken cancellationToken)
{
Queue 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 allows us to query each "path part" until the desired file or folder is located. If no match is found or there are multiple matches, an exception will be raised.
Section 2.1: Accessing Google Sheets in C#
Now that we've found the file, we can access our Google Sheet spreadsheet in C#! Here’s the relevant 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 spreadsheet.Data[0].RowData)
{
// TODO: process the row data!}
}
}
In this code, we locate the spreadsheet at the specified path. From there, we open it using the Sheets service since we have the file's ID. With the spreadsheet object in hand, we can now interact with it using the Google Sheets API.
Chapter 3: Future Considerations
The code we've explored is not optimized for performance. If you need to perform multiple lookups for the same file path, you may end up executing multiple queries. To reduce API calls, consider caching the mappings from paths to IDs locally. This would allow you to quickly access a file ID if you have seen an exact path before.
You can also extend this logic to accommodate other file types from Google! Consider how document types interact—there are often no extensions for spreadsheets in Google Drive. What if you have both a document and a spreadsheet with the same name? Test it out!
Wrapping Up: Google Sheets and C#
This guide has provided an overview of using the Google Sheets API in C# for accessing and manipulating data stored in Google Drive. You can now create applications that leverage your Google Drive data through .NET!
The initial setup involved obtaining a JSON credential from the Google Cloud Console. After that, we navigated to the required file by traversing each part of a path—unless you prefer to work directly with file IDs!
Whether you aim to build a data-driven website or manage content schedules, these Google APIs can assist you! Feel free to explore these concepts in your projects and unlock the full potential of Google Sheets and Drive APIs in your .NET applications. If you found this information valuable and seek 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 me on this platform if you haven't already! Subscribe to my free weekly software engineering and .NET-focused newsletter for 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
Explore my website for a wealth of articles on various software engineering topics, including code snippets:
VISIT WEBSITE
Check out the repository with numerous code examples from my articles and videos on GitHub:
VIEW REPOSITORY