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.

Click Enable API. Search for and enable the Google Drive API and the Google Sheets API.

Create credentials for a Web Server to access Application Data.

Name the service account and grant it a Project Role of Editor.

Download the JSON file.

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.

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:

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.

staticvoidMain(string[]args){// ...ReadEntries();}

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.

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!