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.

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.

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

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.

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.

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.

 

Leave a Comment