Read Data From Google Live Sheet

In this blog, I explain how to read google sheet data into your MVC project.

Introduction 
 
In this blog, you will learn how to display Google sheet data in your MVC application by using the Google console account and key.
 
You can add the data into a Google sheet and run your application so your Google sheet data shows on your website. 
 
Step 1

Create a new sheet in your live account from your Google account. Go here and make the new sheet,
Google Live sheet 

Step 2

Create a new MVC application and make the code. Install the NuGet package for the integration of this sheet. Install the package from https://www.nuget.org/packages/Google.Apis/ 

Package Name - Install-Package Google.Apis

Step 3

Login into your Google developer account and make the new create service account link for the developer account: https://console.developers.google.com/.
 
Go to the service account and create a new service and download the Jason file and put into your project.

Google Live sheet 

After downloading the JSON file copy this file and paste it into your MVC project.

Step 4

Add this code into your index action result in the controller. 
  1. public ActionResult Index()  
  2.         {  
  3.             var credential = GoogleCredential.FromStream(new FileStream(Server.MapPath("~/client_secret.json"), FileMode.Open)).CreateScoped(Scopes);  
  4.             var service = new SheetsService(new BaseClientService.Initializer()  
  5.             {  
  6.                 HttpClientInitializer = credential,  
  7.                 ApplicationName = ApplicationName,  
  8.             });  
  9.   
  10.             // Define request parameters.  
  11.             String spreadsheetId = "enter here your spread sheet id";  eg.: spreadsheetId ="1JSGeY4mjJ557UAqMM";
  12.             String range = "Sheet1";  
  13.             SpreadsheetsResource.ValuesResource.GetRequest request =  
  14.                                     service.Spreadsheets.Values.Get(spreadsheetId, range);  
  15.             // Prints the names and majors of students in a sample spreadsheet:  
  16.             ValueRange response = request.Execute();  
  17.             IList<IList<Object>> values = response.Values;  
  18.             ViewBag.List = values;  
  19.   
  20.             return View();  
  21.         }  
Make sure your spreadsheet ID is the same you can get from your Google sheet URL, your sheet ID starts after this URL - https://docs.google.com/spreadsheets/d/

Your ID looks like this in the URL,

Google Live sheet 

So, now all the data will get into your ViewBag.List,

In your cshtml page code like this:
  1. <div class="col-md-6">  
  2.         <h3>Read Data From Google Live sheet</h3>  
  3.         <table class="table" id="customers">  
  4.             <thead>  
  5.                 <tr>  
  6.                     <th>  
  7.                         id  
  8.                     </th>  
  9.                     <th>  
  10.                         Name  
  11.                     </th>  
  12.                 </tr>  
  13.             </thead>  
  14.             <tbody>  
  15.                 @{  
  16.                     foreach (var item in ViewBag.List)  
  17.                     {  
  18.                         <tr>  
  19.                             <td>@item[0]</td>  
  20.                             <td>@item[1]</td>  
  21.                         </tr>  
  22.                     }  
  23.                 }  
  24.             </tbody>  
  25.   
  26.         </table>  
  27.     </div>  

Then the file result will be displayed like this:

Google Live sheet 
Summary 
 
First, create the Google sheet and open the Google console app and download the JSON file (credentials). Then you need to put this file into your MVC application and then make code and run it.