GoogleSheetsWrapper 2.0.20
See the version list below for details.
dotnet add package GoogleSheetsWrapper --version 2.0.20
NuGet\Install-Package GoogleSheetsWrapper -Version 2.0.20
<PackageReference Include="GoogleSheetsWrapper" Version="2.0.20" />
paket add GoogleSheetsWrapper --version 2.0.20
#r "nuget: GoogleSheetsWrapper, 2.0.20"
// Install GoogleSheetsWrapper as a Cake Addin #addin nuget:?package=GoogleSheetsWrapper&version=2.0.20 // Install GoogleSheetsWrapper as a Cake Tool #tool nuget:?package=GoogleSheetsWrapper&version=2.0.20
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 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 |
Append a CSV to Google Sheets
var appender = new SheetAppender(
// 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);
appender.Init(settings.JsonCredential);
var filepath = @"C:\Input\input.csv";
using (var stream = new FileStream(filepath, FileMode.Open))
{
// OPTION 1: Default to CultureInfo.InvariantCulture and "," as the delimiter
// 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
csvHasHeaderRecord: true, // true indicating the CSV file has a header record as the first row
skipWritingHeaderRow: false, // false indicating to not skip writing the header record to the Google sheets file
batchWaitTime: 1000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
// OPTION 2: Create your own CsvConfiguration object with full control on Culture and delimiter
var csvConfig = new CsvConfiguration(CultureInfo.CurrentCulture)
{
HasHeaderRecord = true,
Delimiter = ",",
};
// 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
csvConfig, // The custom CsvConfiguration object defined previously
batchWaitTime: 1000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
}
Import a CSV to Google Sheets and Purge Existing Rows
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(
// 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);
// Get the total row count for the existing sheet
var rows = sheetHelper.GetRows(new SheetRange("", 1, 1, 1));
// Delete all of the rows
if (rows.Count > 0)
{
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))
{
// OPTION 1: Default to CultureInfo.InvariantCulture and "," as the delimiter
// 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
// OPTION 2: Create your own CsvConfiguration object with full control on Culture and delimiter
var csvConfig = new CsvConfiguration(CultureInfo.CurrentCulture)
{
HasHeaderRecord = true,
Delimiter = ",",
};
// 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
csvConfig, // The custom CsvConfiguration object defined previously
1000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
}
Export Google Sheet to CSV
// Create a SheetHelper class
var sheetHelper = new SheetHelper(
// 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);
var exporter = new SheetExporter(sheetHelper);
var filepath = @"C:\Output\output.csv";
// OPTION 1: Default to CultureInfo.InvariantCulture and "," as the delimiter
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);
}
// OPTION 2: Create your own CsvConfiguration object with full control on Culture and delimiter
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);
// This gives you full control on the CSV file CultureInfo and Delimiter value
var csvConfig = new CsvConfiguration(CultureInfo.CurrentCulture)
{
Delimiter = ";",
};
// Export with the customer CsvConfiguration value
exporter.ExportAsCsv(range, stream, csvConfig);
}
Export Google Sheet to Excel File
var exporter = new SheetExporter(
// 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);
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);
}
Authentication
[!IMPORTANT] You need to setup a Google API Service Account before you can use this library.
If you have not yet created a Google Cloud project, you will need to create one before you create a service account. Documentation on this can be found below,
https://developers.google.com/workspace/guides/create-project
Create a service account. Steps to do that are documented below,
https://cloud.google.com/docs/authentication/production#create_service_account
Enable the
Google Sheets API
for your Google Cloud project. Details on this can be found below,For the service account you created, create a new service account key. When you do this, it will also download the key, choose the JSON key format.
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>(
// 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);
Another good article on how to setup a Google Service Account can be found below on Robocorp's documentation site,
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.1
- CsvHelper (>= 33.0.1)
- DocumentFormat.OpenXml (>= 3.0.2)
- Google.Apis.Sheets.v4 (>= 1.68.0.3476)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
2.0.23 | 197 | 12/13/2024 |
2.0.22 | 1,005 | 10/31/2024 |
2.0.21 | 88 | 10/31/2024 |
2.0.20 | 367 | 9/28/2024 |
2.0.19 | 90 | 9/28/2024 |
2.0.18 | 355 | 8/19/2024 |
2.0.17 | 31,673 | 7/20/2024 |
2.0.16 | 147 | 6/8/2024 |
2.0.15 | 151 | 6/3/2024 |
2.0.14 | 105 | 6/3/2024 |
2.0.13 | 118 | 5/29/2024 |
2.0.12 | 123 | 5/29/2024 |
2.0.11 | 410 | 1/20/2024 |
2.0.10 | 126 | 1/20/2024 |
2.0.9 | 3,088 | 1/8/2024 |
2.0.8 | 124 | 1/6/2024 |
2.0.7 | 129 | 1/6/2024 |
2.0.6 | 264 | 12/19/2023 |
2.0.5 | 279 | 11/28/2023 |
2.0.4 | 328 | 11/26/2023 |
2.0.3 | 127 | 11/25/2023 |
2.0.2 | 148 | 11/14/2023 |
2.0.1 | 117 | 11/14/2023 |
2.0.0 | 117 | 11/13/2023 |
1.0.58 | 23,188 | 11/13/2023 |
1.0.57 | 160 | 11/6/2023 |
1.0.56 | 129 | 11/6/2023 |
1.0.55 | 176 | 10/21/2023 |
1.0.54 | 1,571 | 5/9/2023 |
1.0.53 | 174 | 5/6/2023 |
1.0.52 | 48,152 | 2/7/2023 |
1.0.51 | 1,926 | 1/1/2023 |
1.0.50 | 296 | 12/29/2022 |
1.0.49 | 318 | 11/28/2022 |
1.0.48 | 311 | 11/28/2022 |
1.0.47 | 326 | 11/28/2022 |
1.0.46 | 311 | 11/28/2022 |
1.0.45 | 326 | 11/28/2022 |
1.0.44 | 333 | 11/22/2022 |
1.0.43 | 315 | 11/22/2022 |
1.0.42 | 323 | 11/22/2022 |
1.0.41 | 326 | 11/22/2022 |
1.0.40 | 339 | 11/22/2022 |
1.0.39 | 476 | 11/20/2022 |
1.0.38 | 321 | 11/19/2022 |
1.0.37 | 1,132 | 8/25/2022 |
1.0.36 | 410 | 8/18/2022 |
1.0.35 | 510 | 8/2/2022 |
1.0.34 | 419 | 6/18/2022 |
1.0.33 | 518 | 5/19/2022 |
1.0.32 | 402 | 5/17/2022 |
1.0.31 | 657 | 3/8/2022 |
1.0.30 | 443 | 3/8/2022 |
1.0.29 | 447 | 1/19/2022 |
1.0.28 | 302 | 12/20/2021 |
1.0.27 | 462 | 10/31/2021 |
1.0.26 | 314 | 10/28/2021 |
1.0.25 | 321 | 10/26/2021 |
1.0.24 | 347 | 10/17/2021 |
1.0.23 | 397 | 10/17/2021 |
1.0.22 | 354 | 10/16/2021 |
1.0.21 | 421 | 9/12/2021 |
1.0.20 | 419 | 8/26/2021 |
1.0.19 | 326 | 8/25/2021 |
1.0.18 | 316 | 8/25/2021 |
1.0.17 | 339 | 8/22/2021 |
1.0.16 | 333 | 8/22/2021 |
1.0.15 | 313 | 8/22/2021 |
1.0.14 | 338 | 8/21/2021 |
1.0.13 | 353 | 8/21/2021 |
1.0.12 | 346 | 8/21/2021 |
1.0.11 | 303 | 8/21/2021 |
1.0.10 | 314 | 8/21/2021 |
1.0.9 | 318 | 8/11/2021 |
1.0.8 | 315 | 8/9/2021 |
1.0.7 | 322 | 8/9/2021 |
1.0.6 | 347 | 8/8/2021 |
1.0.5 | 317 | 8/6/2021 |
1.0.4 | 349 | 8/5/2021 |
1.0.3 | 353 | 8/3/2021 |
1.0.2 | 342 | 8/3/2021 |
1.0.1 | 391 | 8/1/2021 |
1.0.0 | 311 | 8/1/2021 |