Tuesday, January 12, 2016

Retrieve data from Google Sheets in .NET- the easiest way!

So, I was creating an app for the Windows Phone platform the other day, and wanted to store some data. But I did not want to shell out money for a full fledged database server. So a question struck my mind. Google sheets allows me to create spreadsheets for FREE! So can I use them to store my tabular data? Since my requirements were much simpler, I did not require to perform any of the database operations such as 'Join' or 'Group By'. I simply needed all the data stored in the table at a particular instant of time.

Note that although Google provides several APIs for Google Sheets but none of them work for the Windows RT platform(at least, this is what I concluded after a couple of days of frantically searching their documentation).

So I finally figured out a way to accomplish what I wanted. I'm demonstrating it from scratch, by creating a spreadsheet. Follow along.

STEP 1: Create a new Google Sheet
  • Navigate to drive.google.com and create a new Google Sheet.
  • I am naming it 'Demo', but you can assign it any name, or even leave it untitled; it does not matter.
  • I'll create three columns- NAME, AGE, and Email. 
    • Leave the first column empty. Let column B, C and D of first row have the data- "NAME", "AGE" and "Email" respectively.We'll use these as headings. 
    • To get a better look-n-feel, I am 'Freezing' 1 row, though this step is not necessary.

      So this is how my spreadsheet looks like:
STEP 2: Publish the spreadsheet
To make the data in this sheet accessible to public, the spreadsheet has to be published to web. This is DIFFERENT from sharing a document. 
  • To publish this sheet, click choose 'Publish to the web' in the 'File' menu.
  • Click on the 'Publish' button.
  • An alert pops-up warning that you are about top publish this document. Click OK.

STEP 3: Read the data in the spreadsheet in XML/JSON format
Now that the spreadsheet has been published to web, we'll just check if the data in the sheet is retrievable in XML/JSON format or not. But before we do so, carefully observe the URL of your spreadsheet. 

The URL of the spreadsheet looks something like 
https://docs.google.com/spreadsheets/d/<Some_Secret_Key_Here>/edit

  • The most important part here is the document key. It is present in the URL in between docs.google.com/spreadsheet/d/ and /edit#gid=0. Note down this key somewhere, or simply copy paste it into a text editor for sometime. We'll need this key in later on.
  • Now, open a new tab in your browser and copy-paste this in the address bar:

    https://spreadsheets.google.com/feeds/list/<Key_Here>/1/public/values?alt=json
  • Replace the <Key_Here> with the document key that you obtained in the previous step and press enter. If you have been following right, you'll get the spreadsheet data in JSON format.
  • Copy paste all of this text in a text editor, as we'll need it soon.

STEP 4: Creating classes from JSON
Having obtained JSON in the previous step, we'll now build C# classes from JSON.
  • Navigate to http://json2csharp.com/ 
  • Copy-paste the JSON obtained in the previous step and click 'Generate'.
  • C# classes are generated. Keep this tab open; we'll need it in the next step.

STEP 4: Building the application
  • Fire up Visual Studio and create a new Console Application and name it 'SpreadsheetDemo'. Note that the following process is exactly the same for Windows Store, Windows Phone, and universal apps built in C#/XAML.
  • Right click on the project 'SpreadsheetDemo' in the Solution Explorer and add a new class. Name it 'GoogleSheetData.cs'.
  • Remove everything inside the namespace block.
  • Paste the C# classes from json2sharp.com here.
  • Note that there are several errors. The error is at the __invalid_name__$t. This is because there are several '$t' present in the name, which are not allowed in C#. They exist here because the JSON we had contains them. 
  • In order to eliminate these errors we'll have to change these attribute names that comply with the C# naming convention, and manually map the JSON variables to these names. 
Before that,install the 'Newtonsoft-Json.net' package from the NuGet Package Manager.

Installing Newtonsoft JSON.Net package


Coming back to the GoogleSpreadsheet.cs file in the Solution Explorer, make the following changes:
  • Add the following using statement
  •  using Newtonsoft.Json;  
  • Add [JsonProperty("$t")] above every line of code that contains __Invalid_name__$t.
  • Change '__invalid_name__$t' everywhere on this page to simply 't', meaning simply remove the '$'.
  • 1:  namespace SpreadsheetDemo  
    2:  {  
    3:    public class Id  
    4:    {  
    5:      [JsonProperty("$t")]  
    6:      public string t { get; set; }  
    7:    }  
    8:    public class Updated  
    9:    {  
    10:      [JsonProperty("$t")]  
    11:      public string t { get; set; }  
    12:   }  
    13:  }  
    
  • Do this everywhere until all the errors are resolved. Ensure that the mapping string provided in the [JsonProperty("<mapping_string_here>")] exactly matches with the JSON keys.
Now we are short of only one step before we have the spreadsheet data in our application.

To be able to send an HTTP request to Google, we have to add a reference to the 'System.Net.Http' library in this project.

Adding reference to 'System.Net.Http

\

  • In the menubar click on PROJECT > Add Reference
  • Search for 'System.Net.Http' (without quotes).
  • Open 'Program.cs' file from the Solution Explorer and replace all the code available in that file with the following code:

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Threading.Tasks;  
 // Add this namespace here  
 using System.Net.Http;  
 using Newtonsoft.Json;  
 namespace SpreadsheetDemo  
 {  
   class Program  
   {  
     static async void DataFromSpreadsheet()  
     {  
       string url = "https://spreadsheets.google.com/feeds/list/<Spreadsheet_Key_Here>/1/public/values?alt=json";  
       string response = String.Empty;  
       // Getting JSON string from spreadsheet in 'response'  
       try  
       {  
         HttpClient request = new HttpClient();  
         response = await request.GetStringAsync(url);  
         /* Creating an object of RootObject class   
           * (defined in GoogleSheetData.cs file)    
           * and deserializing JSON into the object. */  
         RootObject obj = JsonConvert.DeserializeObject<RootObject>(response);  
         // Printing the data to console  
         Console.WriteLine("\t\t****DATA FROM SPREADSHEET****");  
         foreach (var entry in obj.feed.entry)  
         {  
           Console.WriteLine("Name \t : \t " + entry.gsxName.t);  
           Console.WriteLine("Age \t : \t " + entry.gsxAge.t);  
           Console.WriteLine("Email \t : \t " + entry.gsxEmail.t);  
         }  
         Console.ReadKey();  
       }  
       catch (HttpRequestException)  
       {  
         Console.WriteLine("\n***Cannot connect to the server.***");  
       }  
       Console.ReadKey();  
     }  
     static void Main(string[] args)  
     {  
       DataFromSpreadsheet();  
     }  
   }  
 }  

  • Replace the document key of your spreadsheet in the 'string url = "https://....?alt=json"; ' line where <spreadsheet_Key_Here> is written in the code above.


Run the program!

No comments:

Post a Comment