Insert and Update Google Sheet from ASP.NET(C#) using Google.Apis.Sheets.V4 API

Few days back I got a requirement where I need to push some data into Google Sheet, that the client was maintaining for attendance of his employees.

I searched a lot in the internet and found few things but those are obsolete ones as Google has discontinued those Service APIs such as Google.GData.Client, Google.GData.Spreadsheets and replaced those APIs with Google.Apis.Sheets.v4 services.

Though Google has a very illustrative example on how to use and consume Google.Apis.Sheets.v4 services api, I found it a bit not helpful for beginners to understand and consume it. So I thought of writing a blog on this “How to consume Google.Apis.Sheets.v4 services for data insert and fetch in C#” to help others who are trying their hands on the same.

Here is the Google Developer’s link you can read the article and try your hands.

https://developers.google.com/sheets/api/quickstart/dotnet

Alright, lets get into the code. I will be explaining the process in some steps to make it easy to understand and use.



Step: 1
First you need to create a project in Google Console to get your client id. Go to the below link and create your project

https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com

Once you are done creating the project download the client id, that will be in JSON format.

Step 2:
Create a console or web or windows form application according to your need.
Refer the below libraries in your project.
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
Declare the below two line globally.
static string[] Scopes = { SheetsService.Scope.Spreadsheets };
static string ApplicationName = "TimeSheetUpdation By Cybria Technology";
static string SheetId = "Your sheet id ";

Step:3
Here we will write a method to authorize our request to access Goolge’s API.
private static SheetsService AuthorizeGoogleApp()
 {
     UserCredential credential;

     using (var stream =
         new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
     {
         string credPath = System.Environment.GetFolderPath(
             System.Environment.SpecialFolder.Personal);
         credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

         credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
             GoogleClientSecrets.Load(stream).Secrets,
             Scopes,
             "user",
             CancellationToken.None,
             new FileDataStore(credPath, true)).Result;
         Console.WriteLine("Credential file saved to: " + credPath);
     }

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

     return service;
 }
This method will return a sheet service SheetsService object

Step: 4
Now we need to decide the range, like from which row we have to start inserting. There are few acceptable values, below are the details.
  • A:A – this means the entire column A
  • A8:A – this means A8 to all A
// Define request parameters.
String spreadsheetId = txtGoogleSheet.Text;
String range = "K:K";

SpreadsheetsResource.ValuesResource.GetRequest getRequest =
           service.Spreadsheets.Values.Get(spreadsheetId, range);

ValueRange getResponse = getRequest.Execute();
IList<IList<Object>> getValues = getResponse.Values;
Now this code block will get you the column values according to your range. If you have selected A:A then it will give you the values of the entire A column, if you have selected A8:A then it will give you the columns values starting from A8, i.e 8th row.



Below method will return the range
protected static string GetRange(SheetsService service)
 {
     // Define request parameters.
     String spreadsheetId = SheetId;
     String range = "A:A";

     SpreadsheetsResource.ValuesResource.GetRequest getRequest =
                service.Spreadsheets.Values.Get(spreadsheetId, range);

     ValueRange getResponse = getRequest.Execute();
     IList<IList<Object>> getValues = getResponse.Values;

     int currentCount = getValues.Count() + 2;

     String newRange = "A" + currentCount + ":A";

     return newRange;
 }

Step:5
Now its time to generate some dummy data
private static IList<IList<Object>> GenerateData()
 {
     List<IList<Object>> objNewRecords = new List<IList<Object>>();

     IList<Object> obj = new List<Object>();

     obj.Add("Column - 1");
     obj.Add("Column - 2");
     obj.Add("Column - 3");

     objNewRecords.Add(obj);

     return objNewRecords;
 }

Step:6
Here is the method that will update the google sheet accordingly.
private static void UpdatGoogleSheetinBatch(IList<IList<Object>> values, string spreadsheetId, string newRange, SheetsService service)
 {
     SpreadsheetsResource.ValuesResource.AppendRequest request =
        service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange);
     request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
     request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
     var response = request.Execute();
 }
The above code will insert all the things as string if you want to insert the data in proper format then change the ValueInputOptionEnum.RAW to ValueInputOptionEnum.USERENTERED.



If you want the whole code, here it is.
using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace testGoogleSheets
{
    public class Attendance
    {
        public string AttendanceId { get; set; }
    }

    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        static string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static string ApplicationName = "TimeSheetUpdation By Cybria Technology";
        static string SheetId = "Your sheet id";

       static void Main(string[] args)
        {
            var service = AuthorizeGoogleApp();

            string newRange = GetRange(service);

            IList<IList<Object>> objNeRecords = GenerateData();

            UpdatGoogleSheetinBatch(objNeRecords, SheetId, newRange, service);

            Console.WriteLine("Inserted");
            Console.Read();
        }

       private static SheetsService AuthorizeGoogleApp()
        {
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(
                    System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

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

            return service;
        }

       protected static string GetRange(SheetsService service)
        {
            // Define request parameters.
            String spreadsheetId = SheetId;
            String range = "A:A";

            SpreadsheetsResource.ValuesResource.GetRequest getRequest =
                       service.Spreadsheets.Values.Get(spreadsheetId, range);

            ValueRange getResponse = getRequest.Execute();
            IList<IList<Object>> getValues = getResponse.Values;

            int currentCount = getValues.Count() + 2;

            String newRange = "A" + currentCount + ":A";

            return newRange;
        }

       private static IList<IList<Object>> GenerateData()
        {
            List<IList<Object>> objNewRecords = new List<IList<Object>>();

            IList<Object> obj = new List<Object>();

            obj.Add("Column - 1");
            obj.Add("Column - 2");
            obj.Add("Column - 3");

            objNewRecords.Add(obj);

            return objNewRecords;
        }

       private static void UpdatGoogleSheetinBatch(IList<IList<Object>> values, string spreadsheetId, string newRange, SheetsService service)
        {
            SpreadsheetsResource.ValuesResource.AppendRequest request =
               service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange);
            request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
            request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var response = request.Execute();
        }

    }
}

I hope now you are on your own to play around with the Google sheet service on how to insert into google sheet from C#.

Happy Codding...

No comments:

Post a Comment