Google Spreadsheets and .NET Core

Time to read: 3 minutes

March 24, 2017
Written by
Marcos Placona
Twilion

spreadsheets_dotnetcore

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.

If you would like to see a full integration of Twilio APIs in a .NET Core application then checkout this free 5-part video series. It's separate from this blog post tutorial but will give you a full run down of many APIs at once.

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.

dotnet new console —name ImportantStuff -f netcoreapp1.1
cd ImportantStuff
dotnet restore

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

dotnet add package Google.Apis.Sheets.v4

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.


<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Google.Apis.Sheets.v4">
      <Version>1.22.0.806</Version>
    </PackageReference>
  </ItemGroup>
  <ItemGroup>
    <None Update="client_secret.json">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

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: 
static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
static readonly string ApplicationName = "Current Legislators";
static readonly string SpreadsheetId = "1P_0tngt7o02xgXr9T-wSaVXz-_JIJZekGTGRLoWLnA4";
static readonly string sheet = "legislators-current";
static SheetsService service;

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.

GoogleCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
    credential = GoogleCredential.FromStream(stream)
        .CreateScoped(Scopes);
}

// Create Google Sheets API service.
service = new SheetsService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = ApplicationName,
});

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.

static void ReadEntries()
{
    var range = $"{sheet}!A:F";
    SpreadsheetsResource.ValuesResource.GetRequest request =
            service.Spreadsheets.Values.Get(SpreadsheetId, range);

    var response = request.Execute();
    IList<IList<object>> values = response.Values;
    if (values != null && values.Count > 0)
    {
        foreach (var row in values)
        {
            // Print columns A to F, which correspond to indices 0 and 4.
            Console.WriteLine("{0} | {1} | {2} | {3} | {4} | {5}", row[0], row[1], row[2], row[3], row[4], row[5]);
        }
    }
    else
    {
        Console.WriteLine("No data found.");
    }
}

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.


static void Main(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.

static void CreateEntry() { 
    var range = $"{sheet}!A:F";
    var valueRange = new ValueRange();

    var oblist = new List<object>() { "Hello!", "This", "was", "insertd", "via", "C#" };
    valueRange.Values = new List<IList<object>> { oblist };

    var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
    appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
    var appendReponse = appendRequest.Execute();
}

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().

static void UpdateEntry()
{
    var range = $"{sheet}!D543";
    var valueRange = new ValueRange();

    var oblist = new List<object>() { "updated" };
    valueRange.Values = new List<IList<object>> { oblist };

    var updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
    updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
    var appendReponse = updateRequest.Execute();
}

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.

static void DeleteEntry()
{
    var range = $"{sheet}!A543:F";
    var requestBody = new ClearValuesRequest();

    var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range);
    var deleteReponse = deleteRequest.Execute();
}

 

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!

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.