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.
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)
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() { }
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)
: base(sheetsHelper) { }
}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>(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
sheetHelper.Init(settings.JsonCredential);
// Get all rows from the sheet starting with the 1st row, between the 1st and 8th columns
// Leaving the last row blank tells Google Sheets to return all rows from the Sheet
var allRows = sheetHelper.GetRows("TabName", 1, 1, 8);
// Create a Repository for the TestRecord class
var repository = new TestRepository(sheetHelper);
// 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
});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,
// Get the Google Spreadsheet Config Values
var serviceAccount = config["GOOGLE_SERVICE_ACCOUNT"];
var documentId = config["GOOGLE_SPREADSHEET_ID"];
var jsonCredsPath = config["GOOGLE_JSON_CREDS_PATH"];
// In this case the json creds file is stored locally,
// but you can store this however you want to (Azure Key Vault, HSM, etc)
var jsonCredsContent = File.ReadAllText(jsonCredsPath);
// Create a new SheetHelper class
var sheetHelper = new SheetHelper(documentId, serviceAccount, "");
sheetHelper.Init(jsonCredsContent);
// 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");
}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,
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");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);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, "*");Full list of the different fields you can specify in the field mask property are below. To combine any of these you want to use a comma separated string.
| C# Property Name | Google Sheets API Property Name |
|---|---|
DataSourceFormula |
dataSourceFormula |
DataSourceTable |
dataSourceTable |
DataValidation |
dataValidation |
EffectiveFormat |
effectiveFormat |
EffectiveValue |
effectiveValue |
FormattedValue |
formattedValue |
Hyperlink |
hyperlink |
Note |
note |
PivotTable |
pivotTable |
TextFormatRuns |
textFormatRuns |
UserEnteredFormat |
userEnteredFormat |
UserEnteredValue |
userEnteredValue |
var appender = new SheetAppender(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
appender.Init(settings.JsonCredential);
var filepath = @"C:\Input\input.csv";
using (var stream = new FileStream(filepath, FileMode.Open))
{
// Append the csv file to Google sheets, include the header row
// and wait 1000 milliseconds between batch updates
// Google Sheets API throttles requests per minute so you may need to play
// with this setting.
appender.AppendCsv(
stream, // The CSV FileStrem
true, // true indicating to include the header row
1000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
}If you wanted to delete the existing rows in your tab first, you can use
SheetHelper methods to do that. Below is a sample of that,
// Create a SheetHelper class
var sheetHelper = new SheetHelper(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
sheetHelper.Init(settings.JsonCredential);
// Get the total row count for the existing sheet
var rows = sheetHelper.GetRows(new SheetRange("", 1, 1, 1));
// Delete all of the rows
sheetHelper.DeleteRows(1, rows.Count);
// Create the SheetAppender class
var appender = new SheetAppender(sheetHelper);
var filepath = @"C:\Input\input.csv";
using (var stream = new FileStream(filepath, FileMode.Open))
{
// Append the csv file to Google sheets, include the header row
// and wait 1000 milliseconds between batch updates
// Google Sheets API throttles requests per minute so you may need to play
// with this setting.
appender.AppendCsv(
stream, // The CSV FileStrem
true, // true indicating to include the header row
1000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
}var exporter = new SheetExporter(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
exporter.Init(settings.JsonCredential);
var filepath = @"C:\Output\output.csv";
using (var stream = new FileStream(filepath, FileMode.Create))
{
// Query the range A1:G (ie 1st column, 1st row, 8th column and last row in the sheet)
var range = new SheetRange("TAB_NAME", 1, 1, 8);
exporter.ExportAsCsv(range, stream);
}var exporter = new SheetExporter(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
exporter.Init(settings.JsonCredential);
var filepath = @"C:\Output\output.xlsx";
using (var stream = new FileStream(filepath, FileMode.Create))
{
// Query the range A1:G (ie 1st column, 1st row, 8th column and last row in the sheet)
var range = new SheetRange("TAB_NAME", 1, 1, 8);
exporter.ExportAsExcel(range, stream);
}You need to setup a Google API Service Account before you can use this library.
-
Create a service account. Steps to do that are documented below,
https://cloud.google.com/docs/authentication/production#create_service_account
-
After you download the JSON key, you need to decide how you want to store it and load it into the application.
-
Use the service account identity that is created and add that email address to grant it permissions to the Google Sheets Spreadsheet you want to interact with.
-
Configure your code with the following parameters to initialize a SheetHelper object
// 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>(
settings.GoogleSpreadsheetId,
settings.GoogleServiceAccountName,
settings.GoogleMainSheetName);
sheetHelper.Init(settings.JsonCredential);Another good article on how to setup a Google Service Account can be found below on Robocorp's documentation site,