GoogleSheetsWrapper

Badge Statuses

| Badge Name | Badge Status | | ---------- | :------------: | | Current Nuget Version | | | Total Nuget Downloads | | | Open Source License Details | | | Latest Build Status | |

Google Sheets API .NET Wrapper Library

[!IMPORTANT] Using this library requires you to use a Service Account to access your Google Sheets spreadsheets. Please review the authentication section farther down for more details on how to set this up.

This library allows you to use strongly typed objects against a Google Sheets spreadsheet without having to have knowledge on the Google Sheets API methods and protocols.

The following Google Sheets API operations are supported:

• Reading all rows
• Appending new rows
• Deleting rows
• Updating specific cells

All operations above are encapsulated in the SheetHelper class.

There are also base classes, BaseRecord and BaseRepository to simplify transforming raw Google Sheets rows into .NET objects.

A really simple console application using this library is included in this project below,

GoogleSheetsWrapper.SampleClient Project

To setup the sample application, you also need to configure User Secrets in Visual Studio to run it locally.

Extend BaseRecord and BaseRepository

Extending the BaseRecord class you can decorate properties with the SheetFieldAttribute to describe the column header name, the column index and the field type (ie string, DateTime, etc)

[!NOTE] The column index is 1 based and not 0 based. The first column 'A' is equivalent to the column ID of 1.

public class TestRecord : BaseRecord
{
    [SheetField(
        DisplayName = "Name",
        ColumnID = 1,
        FieldType = SheetFieldType.String)]
    public string Name { get; set; }

    [SheetField(
        DisplayName = "Number",
        ColumnID = 2,
        FieldType = SheetFieldType.PhoneNumber)]
    public long? PhoneNumber { get; set; }

    [SheetField(
        DisplayName = "Price Amount",
        ColumnID = 3,
        FieldType = SheetFieldType.Currency)]
    public double? PriceAmount { get; set; }

    [SheetField(
        DisplayName = "Date",
        ColumnID = 4,
        FieldType = SheetFieldType.DateTime)]
    public DateTime? DateTime { get; set; }

    [SheetField(
        DisplayName = "Quantity",
        ColumnID = 5,
        FieldType = SheetFieldType.Number)]
    public double? Quantity { get; set; }

    public TestRecord() { }

    // This constructor signature is required to define!
    public TestRecord(IList<object> row, int rowId, int minColumnId = 1)
        : base(row, rowId, minColumnId)
    {
    }
}

Extending the BaseRepository allows you to define your own access layer to the Google Sheets tab you want to work with.

public class TestRepository : BaseRepository<TestRecord>
{
    public TestRepository() { }

    public TestRepository(SheetHelper<TestRecord> sheetsHelper, BaseRepositoryConfiguration config)
        : base(sheetsHelper, config) { }
}

Core Operations (Strongly Typed)

Before you run the following code you will need to setup a Google Service Account and create a service account key. You also need to decide how to store your environment variables and secrets (ie the Google service account key)

More details can be found here

// You need to implement your own configuration management solution here!
var settings = AppSettings.FromEnvironment();

// Create a SheetHelper class for the specified Google Sheet and Tab name
var sheetHelper = new SheetHelper<TestRecord>(
    // https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID_IS_HERE>/edit#gid=0
    settings.GoogleSpreadsheetId,
    // The email for the service account you created
    settings.GoogleServiceAccountName,
    // the name of the tab you want to access, leave blank if you want the default first tab
    settings.GoogleMainSheetName);

sheetHelper.Init(settings.JsonCredential);

// Create a Repository for the TestRecord class
var repoConfig = new BaseRepositoryConfiguration()
{
    // Does the table have a header row?
    HasHeaderRow = true,
    // Are there any blank rows before the header row starts?
    HeaderRowOffset = 0,
    // Are there any blank rows before the first row in the data table starts?                
    DataTableRowOffset = 0,
};

var repository = new TestRepository(sheetHelper, repoConfig);

// Validate that the header names match the expected format defined with the SheetFieldAttribute values
var result = repository.ValidateSchema();

if(!result.IsValid)
{
    throw new ArgumentException(result.ErrorMessage);
}

// Get all rows from the Google Sheet
var allRecords = repository.GetAllRecords();

// Get the first record
var firstRecord = allRecords.First();

// Update the PriceAmount field and save it back to Google Sheets
firstRecord.PriceAmount = 99.99;
repository.SaveField(firstRecord, (r) => r.PriceAmount);

// Delete the first record from Google Sheets
repository.DeleteRecord(firstRecord);

// Add a new record to the end of the Google Sheets tab
repository.AddRecord(new TestRecord()
{
    Name = "John",
    Number = 2021112222,
    PriceAmount = 250.50,
    Date = DateTime.Now(),
    Quantity = 123
});

Weakly Typed Operations

If you don't want to extend the BaseRecord class, you can use non typed operations with the SheetHelper class. Below is an example of that,

// You need to implement your own configuration management solution here!
var settings = AppSettings.FromEnvironment();

// Create a SheetHelper class for the specified Google Sheet and Tab name
var sheetHelper = new SheetHelper<TestRecord>(
    // https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID_IS_HERE>/edit#gid=0
    settings.GoogleSpreadsheetId,
    // The email for the service account you created
    settings.GoogleServiceAccountName,
    // the name of the tab you want to access, leave blank if you want the default first tab
    settings.GoogleMainSheetName);

sheetHelper.Init(settings.JsonCredential);

// Append new rows to the spreadsheet
var appender = new SheetAppender(sheetHelper);

// Appends weakly typed rows to the spreadsheeet
appender.AppendRows(new List<List<string>>()
{
    new List<string>(){"7/1/2022", "abc"},
    new List<string>(){"8/1/2022", "def"}
});

// Get all the rows for the first 2 columns in the spreadsheet
var rows = sheetHelper.GetRows(new SheetRange("", 1, 1, 2));

// Print out all the values from the result set
foreach (var row in rows)
{
    foreach (var col in row)
    {
        Console.Write($"{col}\t");
    }
    
    Console.Write("\n");
}

Batch Updates

Google Sheets API has a method to perform batch updates. The purpose is to enable you to update mulitple values in a single operation and also avoid hitting the API throttling limits. GoogleSheetsWrapper lets you use this API. One important thing to understand is you need to specify which properties you want updated with the field mask paramater. A few examples are below,

Update UserEnteredValue and UserEnteredFormat

var updates = new List<BatchUpdateRequestObject>();
updates.Add(new BatchUpdateRequestObject()
{
    Range = new SheetRange("A8:B8"),
    Data = new CellData()
    {
        UserEnteredValue = new ExtendedValue()
        {
            StringValue = "Hello World"
        },
        UserEnteredFormat = new CellFormat()
        {
            BackgroundColor = new Color()
            {
                Blue = 0,
                Green = 1,
                Red = 0,
            }
        }
    }
});

// Note the field mask is for both the UserEnteredValue and the UserEnteredFormat below,
sheetHelper.BatchUpdate(updates, "userEnteredValue, userEnteredFormat");

Update only UserEnteredValue

The GoogleSheetsWrapper library defaults to only updating the UserEnteredValue. This is to allow you to keep your existing cell formating in place. However, we give you the option like above to override that behavior.

var updates = new List<BatchUpdateRequestObject>();
updates.Add(new BatchUpdateRequestObject()
{
    Range = new SheetRange("A8:B8"),
    Data = new CellData()
    {
        UserEnteredValue = new ExtendedValue()
        {
            StringValue = "Hello World"
        }
    }
});

// Note the field mask parameter not being specified here defaults to => "userEnteredValue"
sheetHelper.BatchUpdate(updates);

Update all Cell Properties

The Google Sheets API lets you use the * wildcard to specify all properties to be updated. Note when you do this, if any values are null / empty, this will clear out those settings when you save them! Example below,

var updates = new List<BatchUpdateRequestObject>();
updates.Add(new BatchUpdateRequestObject()
{
    Range = new SheetRange("A8:B8"),
    Data = new CellData()
    {
        UserEnteredValue = new ExtendedValue()
        {
            StringValue = "Hello World"
        }
    }
});

// Note setting the field mask to "*" tells the API to save all property values, even if they are null / blank
sheetHelper.BatchUpdate(updates, "*");

All Batch Operation Field Mask Options

Full list of the different fields you can specify in the field mask property are below. To combin