Google Sheets Read Write Update Operations using Dotnet Core

Google Sheets provides easy access from any anywhere using any device and it also integrates with other products that we use every day. The great thing is that It’s really easy to perform Read Write & Update Operations on Google Sheets from a Dotnet Core Application.

In this Tutorials, I’m going to perform CRUD Operations on Google Sheets in a .Net Core Console Application. Actually, we can not start using Google Spreadsheets directly from our C# Code, some configurations are required to work with Google Sheet from any programming language. I took help to write this Tutorial from Google Sheets Documentation & also from Twilio Blog’s post on Google Sheets.

Here’re the steps to perform CRUD Operations on a Google Spreadsheet:

Create a Spreadsheet on Google Sheets
Create Project on Google Developer Console
Enable Google Sheets API & Create Credentials File
Create a .Net Core Project
Install Google Sheets Library from NuGet
Perform Read Write & Update Operations

Let’s get started!

I have created a Spreadsheet having developers names & their expertise percentage on different programming languages Here. You can create your own Spreadsheet or you can ‘make a copy’ of this sheet from File Menu.

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.

Creating Project

First of all we need to crate a Project or you can use your Existing project.

Go to the Google APIs Console.

& Create a Project using Name of your choice.

After Creating Project you need to Search & Enable Google Sheets Library in your Google API Console as you can see in the below Animation.

After Enabling Sheets API click on the Create Credentials Button.

Here’re Some Fields you need to fill.

which API are you using?Select Google Sheets API.

Where will you be calling the API from? Select Web Server.

What data will you be accessing? Select Application data.

& in the last option, Choose => No, I’m not using them.

Now click on What credentials do I need? Button.

In the next page, you need to create a service account. So, provide the Service account name & the Role as Project -> Editor

Select Json as key type & click on continue button.

A JSON file having your credentials will be download.

The downloaded JSON file has all the necessary credentials your application will need to work with spreadsheets. First of all, rename your credentials file as app_client_secret.json

Open your Credential’s File, copy your client_emailvalue & allow Eidt access to that Email in your Google Sheet.

Creating Dotnet core Project & Installing Google Sheets Library

Open your favourite Editor and create a new .NET Core Console Application from NuGet using the Package manager or Dotnet CLI.

I’m using Dotnet CLI. So, run this command

dotnet add package Google.Apis.Sheets.v4

Now, copy your app_client_secret.json File at the root of your Project.

edit the .csproj file of your Project & add a new ItemGroup as bellow.

<ItemGroup>
  <None Update="app_client_secret.json">
    <CopyToOutputDirectory>Always</CopyToOutputDirectory>
  </None>
</ItemGroup>

Let’s Code

First of all, declare these variables globally in your Program.cs File.

static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
static readonly string ApplicationName = "Dot Tutorials";
static readonly string sheet = "Sheet1";
static readonly string SpreadsheetId = "1KLGHGy-dUlREUjKQozMPhumtFXG2uFiTr7cWMr-RMK4";
static SheetsService service;

Replace the Sheet Name with your sheet name & Spreadsheet Id from your Spreadsheet URL.

Spread Sheet Id

We know that Google API uses OAuth 2.0 for authentication. We need to Read our credential’s JSON file & create a Google Sheets API service.

So, Add this Function in your Program.cs File

static void Init(){

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

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

Let’s Create a Reading Function for reading all records from this Spreadsheet.

Put the Below Code in your Program.cs File.

static void ReadSheet()
{
    // Specifying Column Range for reading...
    var range = $"{sheet}!A:E";
    SpreadsheetsResource.ValuesResource.GetRequest request =
            service.Spreadsheets.Values.Get(SpreadsheetId, range);

    // Ecexuting Read Operation...
    var response = request.Execute();
    // Getting all records from Column A to E...
    IList<IList<object>> values = response.Values;
    if (values != null && values.Count > 0)
    {
        foreach (var row in values)
        {
            // Writing Data on Console...
            Console.WriteLine("{0} | {1} | {2} | {3} | {4} ", row[0], row[1], row[2], row[3], row[4]);
        }
    }
    else
    {
        Console.WriteLine("No data found.");
    }
}

Now Call the ReadSheet() Method from your main function like this.

static void Main(string[] args)
{
    Init();
    ReadSheet();
}

Run your Application & you’ll get your Spreadsheet record in your Console.

We have successfully read data from our Spreadsheet. Now It’s time to write a new Record in your google Sheet.

So, use the below code to add another row.

static void AddRow() { 

    // Specifying Column Range for reading...
    var range = $"{sheet}!A:E";
    var valueRange = new ValueRange();

    // Data for another Student...
    var oblist = new List<object>() { "Harry", "80", "77", "62", "98" };
    valueRange.Values = new List<IList<object>> { oblist };

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

Call this AddRow() Method from your Main function & run your Application.

You’ll see that a new row will be added at the end of your records.

You can Update a specific Cell in your Spreadsheet as well.

Here’re the Code to Update a Cell Value.

static void UpdateCell()
{
    // Setting Cell Name...
    var range = $"{sheet}!C5";
    var valueRange = new ValueRange();

    // Setting Cell Value...
    var oblist = new List<object>() { "32" };
    valueRange.Values = new List<IList<object>> { oblist };

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

All Done!

You can also download the Complete Source Code from GitHub.

Download Source Code

Please Comment below If you find any difficulty, I’ll love to solve your problem.

Here’re some Articles you might be Interested:

– USING NOSQL DATABASE WITH DOTNET CORE EXAMPLE