Consuming Google.Apis.Sheets.V4 Services From C#

A few days back, we got a requirement where we needed to push some data into Google Sheets, which the client was maintaining for the attendance of his employees.

We searched the internet and found a few things, but they were obsolete since Google has discontinued Service APIs such as Google.GData.Client and Google.GData.Spreadsheets and replaced it with Google.Apis.Sheets.v4 services.

Now Google has a very illustrative example on how to use and consume the Google.Apis.Sheets.v4 Services API, but we found it a bit unhelpful for beginners to understand. So, we thought of writing a blog on how to consume Google.Apis.Sheets.v4 services for data insert and fetch in C#, to help others who are trying their hand at the same thing.

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

Alright, let's get into the code. I will be explaining the process in 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 this link and create your project.

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

Step 2

Create a console, web, or Windows form application according to your needs.

Refer the below libraries in your project:

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;

Declare the below two lines 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 Google'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, for example, 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;

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, and if you have selected A8:A then it will give you the columns values starting from A8, i.e. the 8th row.

This 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 it's 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 a string. If you want to insert the data in proper format then change the ValueInputOptionEnum.RAW to ValueInputOptionEnum.USERENTERED.

If you want the full 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 can play around with the Google Sheets service and how to insert into Google Sheets from C#.