Friday, November 18, 2016

How to read Google SpreadSheet using Sheets API v4, .Net and a Service Account

In this post, I want to show how to read google spreadsheet data using google sheets v4 apis in .net via a service account. First of all you need an active google account and then next head to google developer console and create a project. I created a project called My Project. Next on the dashboard screen click on Enable API.

On the following screen click on Google Sheets API link and Sheets API will be Enabled. Next go to Credentials page and then Click on Create Credentials.

Click on the third option Service account key to create a service account.

On the Create service account key click on New Service Account. Provide a service account name, select a role for your project. I am choosing owner here. Select Key type to be JSON. Finally click on create and the file will be downloaded on your machine. Know where this file is downloaded we will need this in a later step.

On the credentials page, under service account keys you will be able to see the account you created in earlier step.

Create a spreadsheet called Employees with one row and two columns and keep a note of the spreadsheet ID.

The spreadsheetId can be found from the url of the google spreadsheet as shown below. In the url below the id in the {} bracket. Keep a note of this spreadsheet id we will need this in a later step.

Finally all setup is done. Lets head over to Visual Studio and create a new Console Project. Install nuget package Google.Sheets.Api.v4 from the nuget package manager. Next create three .cs classes and paste the following code as shown below. Fix all the references.

1. GoogleService.cs. This class is responsible for creating a sheetsservice using googlecredential.

2. SpreadSheet.cs. I created a spreadsheet class to store values that we read from the spreadsheet. There is a headerrow and there is rows. Each spreadsheetrow can have multiple rows. For demo purposes I created 2 columns. We need only two

3. GoogleSpreadSheetReader.cs. This class relies on GoogleService class from step1. The GetSpreadSheet method accepts parameter spreadSheetId and a range parameter. I have a spreadsheet in which the first row was header row. If you wanted to read all the rows then you will have to modify this method.

4. Remember the JSON file we downloaded from Google put that file inside a folder called GoogleSecret inside your solution as shown below. Right click on the the file and hit F4 to view properties and change Copy to Output Directory to Copy Always.

5. Putting it all together inside Program.cs together to finally read data from Google SpreadSheet.

Explanation of the above code. First we get hold of the JSON file path from the Bin directory then provide the name of your application, I called mine “My Project”. Using scopes, we mention what level of access we have eg. SpreadsheetsReadonly. We create a new instance of GoogleService then pass googleservice into GoogleSpreadSheetReader. In the Employees spreadsheet we are using only two columns so we provide a range as A : B. The range option is very interesting and you can get crazy with these ranges. Finally you get a spreadsheet by calling reader.GetSpreadSheet(spreadSheetId, range); Run the application by pressing F5 and you will see the output as JSON string.

If you have any questions then please let me know in the comments below.