I recently did a project for a client that needed a way to share a bit of live data with an external organization. He originally asked for a simple website with authentication and an API. I could have built this, but for the frequency and volumes of data they were using, I suggested a simpler approach: a Google Sheet and a recurring job that updates it.

Any online spreadsheet is a great way to share a small amount of data. Users can filter, sort, make formulas, export the data, or even write their own integrations. It’s also possible (depending on how changes are handled) to manually annotate or override data that’s been exported. Implementing all of this in a custom web page is possible, but some of it is pretty tricky.

The project was a success, and I’m happy with the results. Working with the Google Sheets API was not as straightforward as I had expected though. It was hard to find documentation, or even many examples showing how to use it. I’m not sure why this is. In this post I’ll share some of the things I learned.

Google.Apis.Sheets.v4 Package

The Google.Apis.Sheets.v4 package is the official .NET client library for the Google Sheets API. You can install it with NuGet.

It has official documentation, but I found it to be almost useless. I recommend the Google Sheets API documentation instead. It has much more information about how the API works and what the arguments mean. Methods and properties in .NET client library match the Rest API documentation exactly, I assume this is a result of the client library being auto-generated.

My first tip is to make sure you’re using the exact name for an API endpoint. For example, these two APIs, although seemingly similar, take different arguments, and behave totally differently:

  • service.Spreadsheets.BatchUpdate()
  • service.Spreadsheets.Values.BatchUpdate()

Another thing to be aware of is that the methods above don’t actually call the service, they create a request object. They almost look like they can be used in a fluent style, but this is only possible for the simplest get calls. I found it much easier to assign the request object into a variable so that I could set its properties directly. For example:

1
2
3
4
5
6
7
8
9
10
var range = "!A1:A3";
var change = new ValueRange
{
    Range = range,
    MajorDimension = "ROWS",
    Values = [["one", "two", "three"]]
};
var request = service.Spreadsheets.Values.Update(change, "SHEET_ID", range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var result = await request.ExecuteAsync();

Notice how I passed an array of arrays to the Values property? The API uses lists of lists for most values. This is necessary when the data includes multiple rows, but a bit annoying when updating single rows or cells. Fortunately it’s pretty easy to make an inline Array in modern C#, and an Array implements IList.

Each request object does have a Configure() method that allows you to set properties inline, but I found it even more verbose than working with the request object directly.

Authentication

There are a bunch of ways to authenticate to the Google APIs, but I found the choices overwhelming. For my purposes (a background service that needs to authenticate as a single user without a human present) a service account seemed like the best choice.

You can generate a service account in the Google Cloud Console. You’ll need a project. Also make sure to enable the Google Sheets API for your project. The UI in the console changes all the time, so you’ll be better off looking up instructions in the official documentation.

Once the account is created, you can download the JSON key file, and use this in your client code like this:

1
2
3
4
5
6
7
8
var credential = GoogleCredential
    .FromJson("<your JSON here>") // or use .FromFile() and pass the path
    .CreateScoped(SheetsService.ScopeConstants.Spreadsheets);

using var service = new SheetsService(new BaseClientService.Initializer
    {
        HttpClientInitializer = credential
    });

When retrieving this file, also take note of the service account email address (the property client_email in the JSON key file). You will need to share any google documents with this email address for the account to have access to them.

Spreadsheet, Sheet, and Range Identifiers

There are three kinds of identifiers you need to be aware of (documentation here):

  • Spreadsheet - This is the top-level document identifier. You’ll find it in the URL for the Google Sheets Spreadsheet.
  • Sheet - This is the tab within the Spreadsheet, also identified by the gid in the URL. It’s usually passed as the first part of the range (before the !). I’ve found that an empty string refers to the first (auto-generated) sheet in the document (gid 0), or you can use the name of the sheet. I haven’t had success passing the gid (instead of the name) even though the documentation says it’s supported.
  • Range - Most of the API calls require a range. This refers to a rectangular group of Cells, and there are two ways to specify them:
    • A1 notation is the most familiar and most supported. For example Sheet1!A1:D1 will refer to the first four Cells in the first Row of the Sheet named “Sheet1”.
    • R1C1 notation is another syntax that can be used with some api methods. The above example would look like Sheet1!R1C1:R1C4 in R1C1 notation. It can be pretty handy when doing computed updates because you don’t have to convert the column index to a letter/letters. Unfortunately, it doesn’t seem to be supported by all the API methods.

If you need to compute the column letter(s) for a given index, you can use this code:

1
2
3
4
5
6
7
8
9
10
private static string GetColumnNameFromIndex(int columnIndex)
{
    const int numberOfLetters = 26;
    if (columnIndex >= numberOfLetters)
        return GetColumnNameFromIndex(columnIndex / numberOfLetters - 1) +
               GetColumnNameFromIndex(columnIndex % numberOfLetters);

    const char firstColumnLetter = 'A';
    return ((char)(firstColumnLetter + columnIndex)).ToString();
}

Update Types

There are three kinds of changes you can make to a sheet:

  • Update - this is the simplest, allowing you to update a single range of cells. Use these sparingly. Batch updates will be much more efficient where they’re possible.
  • Batch Update - this method takes an array of range update blocks and processes them all together.
  • Append - This method allows you to stick rows on the end of the document. It requires a range, but it’s okay to use row 1. It will automatically stick the new values at the end of the sheet for you.

ETags

All change requests and get responses include an ETag property. In an ideal world, you would include the ETag from your get request when you send an update so that any changes that have occurred since are taken into account. Unfortunately, this property never seems to be set during get requests, and I couldn’t figure out how to make it work.

In all of my testing, the APIs were responding quickly enough that it likely wouldn’t matter. If you have a much longer process between gets and updates, you may need to figure this out. Please comment below if you do!

Performance / Update Size

I couldn’t find any documentation on the limits of the API, so I did some rough testing instead. I found that a batch update with 10,000 cell updates in it (each as a separate change in the request) took only 1.9 seconds. This goes way beyond what my client could ever need.

Conclusion

Google sheets are a great way to share data from an automated process. Although it took a little while to figure out how to read and write data at all, once I had it working, the Google Sheets API worked great. Even better, my client was thrilled with the results.