Home.NETASP.NETContact
.NET
Google Sheets Read Write Update Operations using DotNet | Tutorial
Shehryar Khan
Shehryar Khan
January 19, 2022
3 min

Table Of Contents

01
Introduction
02
Setting Up Google Sheet
03
Creating Project & Installing Google Sheets Library
04
Let’s Code

Introduction

Google Sheets provides easy access from 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!

Setting Up Google Sheet

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.

google sheet copy

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.

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.

google sheet create project

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.

google sheet email add

Creating 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.

Sheet Name
Sheet Name

Spread Sheet Id
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.

google sheet read data dotnet

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.

google sheet

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.

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

Related Articles:

Using NoSQL Database with .Net Example

Generate QR Code using CSharp


Tags

#googlesheet#dotnet
Shehryar Khan

Shehryar Khan

Full-Stack .NET Developer

I'm passionate about learning new technologies as well as mentoring and helping others get started with their programming career. This blog is my way of giving back to the Community.

Expertise

.NET
ASP.NET
React

Social Media

instagramtwitterwebsite

Related Posts

dropthedot
Top 15 Memes of Twitter DropTheDot Campaign Hashtag
August 19, 2022
1 min
© 2022, All Rights Reserved.

Quick Links

Advertise with usContact Us

Social Media