Google Spreadsheets and .NET Core

You don’t always need a fully functional database for every project. If you’re prototyping and your data model is still changing, chances are you could use a Google Spreadsheet for easier and quicker set up.

In this tutorial we will use Google’s API client library for working with Sheets in .NET Core.

Get access to your spreadsheets

You can use any Google Sheet you already have lying around for this project, but if you don’t already have one, make a copy of this spreadsheet of contact information for US legislators. Ian Webster used this same data to build his Twilio application that makes it easier for citizens to call Congress.

The Google API uses OAuth 2.0 for authentication and our spreadsheet will only be accessible to authenticated users. Let’s create a service account to do this.

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API and the Google Sheets API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.

google-developer-console.gif

This file has all the necessary credentials your application will need for editing a spreadsheet on your behalf. Open client_secret.json and find the client_email entry. Copy this value and on your spreadsheet click the “Share” button and paste the email you just copied. This will give your project the correct access rights on that file.

We’re done here, let’s get started with reading your spreadsheet’s data with .NET Core. You can clone this project from here if you just want to run it.

Read data from a Google Spreadsheet with .NET Core

Open your favourite terminal application and create a new .NET Console Application that uses the current framework, which at the time of writing is 1.1. You will find instructions for installing .NET Core on your chosen environment at the bottom of this page.

Still in terminal add a dependency for the Google’s API client library from Nuget.

Move the json file you downloaded from the Google APIs Console into here and rename it to client_secret.json.

Open the project on your favourite IDE and edit the “*.csproj” file. I will be using Visual Studio Code throughout this tutorial.

In that file add a new ItemGroup to say we want to copy this file to our output directory, so when the application is compiled this file doesn’t get ignored and is packaged together with it.

You can run the application now to check that all your configuration is ok. Go back to your terminal and run dotnet run.

Open Program.cs and create the following variables at the top of the class making sure you resolve the dependency for SheetService:

Replace the SpreadSheetId value with the ID of your spreadsheet. You can get that from the URL bar on your browser.

Also replace the sheet variable with the name of the current sheet you’re using. You can find that at the bottom of your spreadsheet.

In the Main method instantiate a new Google Credential that uses our json file’s data and a new SheetsService.

Create a new method in this class called ReadEntries and specify which columns you want returned and loop through the results to display them in the console.

At the bottom of the Main class add a call to this new method so when the class runs, it fetches all the entries from the spreadsheet.

Run it again in terminal with dotnet run and you should see all the rows returned.


We know how to get data from a spreadsheet, but how about editing it?

Insert, Update, and Delete from a Google Spreadsheet with .NET Core

Because we’re already created the authentication for the API, inserting a new row should be straightforward. Let’s create a new method in the class for creating an entry and call that on the Main method too.

But I just realised I made a typo in row D in this important spreadsheet. I’ll create a new method for updating it and will call it in the Main method instead of calling CreateEntry().

00.png

Much better, but come to think of it I think this entire record just doesn’t make sense in this important document, so I’ll just get rid of it.

CRUD? Done!

Using Google Spreadsheets with .NET Core opens up innumerous possibilities for quick prototyping or internal projects as you can also use it with .NET MVC applications. That dataset that just keeps on changing because the client can’t decide what to store? Let them change the database for you.

This is just the tip of the iceberg as I’m sure you can come up with some great ways of using Google Spreadsheets as a database for your project. If you come up with something cool, hit me up on Twitter @marcos_placona or via email on marcos@twilio.com. I can’t wait to see what you’ll build!

  • Matthew Blott

    I’m not sure if I’ll ever get round to using this but this is an excellent tutorial. Well done!

    • Marcos Placona

      Thanks Mathew. I too was under the same impression, but it does make things easier when you’re trying to quickly whip something up. Good to have under your toolbelt

  • André Cruz

    Hello, how you can insert multiple rows?. I´m using a foreach for a datatable but i got an Error that says “Invalid values [0][0]”. Thanks for the post.