How To Read And Write To Google Sheets With c#

I use Google Sheets a lot, they’re handy and useful and it’s nice to have something that allows you to start entering data in a place that’s always accessible. The share-ability is also a big plus.

From time to time I’ve wanted to either access data on a Google sheet or enter data onto a sheet via some c# code, at ask that you’d think would be relatively easy with plenty of documentation available online, but it’s not that easy and I haven’t found the documentation to be particularly clear or useful. So I thought I’d put together a quick guide with some sample code as a reminder to myself that will hopefully also be useful to other people.

I’m writing a book about using C# and Google Sheets, click her for the latest updates

Accessing your Google Sheets

In order to get access to your Google sheets, you’ll need to complete the following steps –

Go to the Google console api page, you’ll need a Google account to access this but you should be able to use the log-in details you use to access your Google sheets.

From here create a new project

Give your project a name and then click the “Create” button. Then click the “Enable Api Services” button, you should see the Google Sheets API about half way down the page under the heading “G Suite”

Click the Google Sheets Option then click Enable, after you’ve done that click the “CREATE CREDENTIALS” button.

On the credentials screen choose the following options, then click the “What credentials do I need?” button

On the next screen enter a title in the “Service account name field” and choose “Project” then “Editor” from the “Role” drop-down list.  Then choose  the JSON option and click “Continue”

A JSON file should now be downloaded to your machine.

Making a specific spreadsheet editable

We’re nearly there with the security access stuff now, we just need to set up a spreadsheet ready to edit. In order to allow your c# code to access a spreadsheet, you have to share it with the account you just created. You can do this by –

Create/open an existing Google sheet

Click the “Share” button on the right-hand side of the sheet

Now open the JSON file we downloaded previously, there should be a “client_email” value in there

You should now enter the email address in this field into the share email field in the share dialog. We should now be good to go!

The c# Code

Adding Data To Cells

I’ve created a number of classes that will hopefully make it easier to add and read data from a Google sheet. Here’s the code to add data, please note that this will only work if you have the helper classes below in your project.

            var gsh = new GoogleSheetsHelper.GoogleSheetsHelper("security-details.json", "18p6CMRLbN6L4IViUIbAxce_3ij6HGlPYXkKUPR5ZkGo");

            var row1 = new GoogleSheetRow();
            var row2 = new GoogleSheetRow();

            var cell1 = new GoogleSheetCell() { CellValue = "Header 1", IsBold = true, BackgroundColor = Color.DarkGoldenrod};
            var cell2 = new GoogleSheetCell() { CellValue = "Header 2", BackgroundColor = Color.Cyan };

            var cell3 = new GoogleSheetCell() { CellValue = "Value 1"};
            var cell4 = new GoogleSheetCell() { CellValue = "Value 2"};

            row1.Cells.AddRange(new List<GoogleSheetCell>() {cell1, cell2});
            row2.Cells.AddRange(new List<GoogleSheetCell>() { cell3, cell4 });

            var rows = new List<GoogleSheetRow>() { row1, row2 };

            gsh.AddCells(new GoogleSheetParameters() {SheetName="Sheet44", RangeColumnStart = 1, RangeRowStart = 1 }, rows);

As well as the helper classes you’ll also need the JSON that was created in the security section above, the path to this file should be passed as the first argument to the GoogleSheetsHelper constructor, the second argument should be the id of the spreadsheet that you shared with the client_email earlier. You can find the id in the URL of your Google spreadsheet. Having the run the code above the following would appear in the sheet named “Sheet44” in your spreadsheet.

The code allows text to be entered in bold and for the background colors of cells to be set. It would be reasonably easy to add other functionality such as the ability to set font families and text colors but I’ll leave that as an exercise for the reader.

Reading Cells

The code to read cell values is as below

var gsh = new GoogleSheetsHelper.GoogleSheetsHelper("Google Sheets-e1ceb012eb0c.json", "18p6CMRLbN6L4IViUIbAxce_3ij6HGlPYXkKUPR5ZkGo");
var gsp = new GoogleSheetParameters() { RangeColumnStart = 1, RangeRowStart = 1, RangeColumnEnd = 3, RangeRowEnd = 100, FirstRowIsHeaders = true, SheetName = "sheet1" };
var rowValues = gsh.GetDataFromSheet(gsp);

The code creates a GoogleSheetsHelper object before reading the first 100 hundred rows in the first three columns on the spreadsheet. The code returns a list of expando objects. So given a spreadsheet that looks like this

The data could be accessed in the following way.

foreach (rowValue in rowValues) {
    var name = rowValue.Name;
    var color = rowValue.FavoriteColor;
    var color = rowValue.Age;
}

If your sheet doesn’t contain headers then you can set the FirstRowIsHeaders property of the GoogleSheetParameters object to false, the data can then be accessed in the following way.

foreach (rowValue in rowValues) {
    var name = rowValue.Column1;
    var color = rowValue.Column2;
    var color = rowValue.Column3;
}

Full source code

Here’s the full code that allows you to use the two bits of functionality above. Please note that the code is not fully polished and will fall over if you point it at a spreadsheet that does not exist or try to write/read data to a nonexistent sheet in a spreadsheet.  As always though, I’d be happy to read and respond to your feedback in the comments.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Dynamic;

namespace GoogleSheetsHelper
{
    public class GoogleSheetsHelper
    {
        static string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static string ApplicationName = "GoogleSheetsHelper";

        private readonly SheetsService _sheetsService;
        private readonly string _spreadsheetId;

        public GoogleSheetsHelper(string credentialFileName, string spreadsheetId)
        {
            var credential = GoogleCredential.FromStream(new FileStream(credentialFileName, FileMode.Open)).CreateScoped(Scopes);

            _sheetsService = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            _spreadsheetId = spreadsheetId;
        }

        public List<ExpandoObject> GetDataFromSheet(GoogleSheetParameters googleSheetParameters)
        {
            googleSheetParameters = MakeGoogleSheetDataRangeColumnsZeroBased(googleSheetParameters);
            var range = $"{googleSheetParameters.SheetName}!{GetColumnName(googleSheetParameters.RangeColumnStart)}{googleSheetParameters.RangeRowStart}:{GetColumnName(googleSheetParameters.RangeColumnEnd)}{googleSheetParameters.RangeRowEnd}";

            SpreadsheetsResource.ValuesResource.GetRequest request =
                _sheetsService.Spreadsheets.Values.Get(_spreadsheetId, range);

            var numberOfColumns = googleSheetParameters.RangeColumnEnd - googleSheetParameters.RangeColumnStart;
            var columnNames = new List<string>();
            var returnValues = new List<ExpandoObject>();

            if (!googleSheetParameters.FirstRowIsHeaders)
            {
                for (var i = 0;i<=numberOfColumns;i++)
                {
                    columnNames.Add($"Column{i}");
                }
            }

            var response = request.Execute();

            int rowCounter = 0;
            IList<IList<Object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                foreach (var row in values)
                {
                    if (googleSheetParameters.FirstRowIsHeaders && rowCounter == 0)
                    {
                        for (var i = 0; i <= numberOfColumns; i++)
                        {
                            columnNames.Add(row[i].ToString());
                        }
                        rowCounter++;
                        continue;
                    }

                    var expando = new ExpandoObject();
                    var expandoDict = expando as IDictionary<String, object>;
                    var columnCounter = 0;
                    foreach (var columnName in columnNames)
                    {
                        expandoDict.Add(columnName, row[columnCounter].ToString());
                        columnCounter++;
                    }
                    returnValues.Add(expando);
                    rowCounter++;
                }
            }

            return returnValues;
        }

        public void AddCells(GoogleSheetParameters googleSheetParameters, List<GoogleSheetRow> rows)
        {
            var requests = new BatchUpdateSpreadsheetRequest {Requests = new List<Request>()};

            var sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName);

            GridCoordinate gc = new GridCoordinate
            {
                ColumnIndex = googleSheetParameters.RangeColumnStart - 1,
                RowIndex = googleSheetParameters.RangeRowStart - 1,
                SheetId = sheetId
            };

            var request = new Request {UpdateCells = new UpdateCellsRequest {Start = gc, Fields = "*"}};

            var listRowData = new List<RowData>();

            foreach (var row in rows)
            {
                var rowData = new RowData();
                var listCellData = new List<CellData>();
                foreach (var cell in row.Cells)
                {
                    var cellData = new CellData();
                    var extendedValue = new ExtendedValue {StringValue = cell.CellValue};

                    cellData.UserEnteredValue = extendedValue;
                    var cellFormat = new CellFormat {TextFormat = new TextFormat()};

                    if (cell.IsBold)
                    {
                        cellFormat.TextFormat.Bold = true;
                    }

                    cellFormat.BackgroundColor = new Color { Blue = (float)cell.BackgroundColor.B/255, Red = (float)cell.BackgroundColor.R/255, Green = (float)cell.BackgroundColor.G/255 };

                    cellData.UserEnteredFormat = cellFormat;
                    listCellData.Add(cellData);
                }
                rowData.Values = listCellData;
                listRowData.Add(rowData);
            }
            request.UpdateCells.Rows = listRowData;

            // It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
            requests.Requests.Add(request);

            _sheetsService.Spreadsheets.BatchUpdate(requests, _spreadsheetId).Execute();
        }

        private string GetColumnName(int index)
        {
            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            var value = "";

            if (index >= letters.Length)
                value += letters[index / letters.Length - 1];

            value += letters[index % letters.Length];
            return value;
        }

        private GoogleSheetParameters MakeGoogleSheetDataRangeColumnsZeroBased(GoogleSheetParameters googleSheetParameters)
        {
            googleSheetParameters.RangeColumnStart = googleSheetParameters.RangeColumnStart - 1;
            googleSheetParameters.RangeColumnEnd = googleSheetParameters.RangeColumnEnd - 1;
            return googleSheetParameters;
        }

        private int GetSheetId(SheetsService service, string spreadSheetId, string spreadSheetName)
        {
            var spreadsheet = service.Spreadsheets.Get(spreadSheetId).Execute();
            var sheet = spreadsheet.Sheets.FirstOrDefault(s => s.Properties.Title == spreadSheetName);
            int sheetId = (int)sheet.Properties.SheetId;
            return sheetId;
        }
    }

    public class GoogleSheetCell
    {
        public string CellValue { get; set; }
        public bool IsBold { get; set; }
        public System.Drawing.Color BackgroundColor { get; set; } = System.Drawing.Color.White;
    }

    public class GoogleSheetParameters
    {
        public int RangeColumnStart { get; set; }
        public int RangeRowStart { get; set; }
        public int RangeColumnEnd { get; set; }
        public int RangeRowEnd { get; set; }
        public string SheetName { get; set; }
        public bool FirstRowIsHeaders { get; set; }    }

    public class GoogleSheetRow
    {
        public GoogleSheetRow() => Cells = new List<GoogleSheetCell>();

        public List<GoogleSheetCell> Cells { get; set; }
    }
}

I’m writing a book about using C# and Google Sheets, click her for the latest updates