Google Spreadsheet To CSV Conversion

• Download source code (RAR) - 21.9 KB

Introduction

I got a request from my marketing department that they are collecting Google Analytics data in Google Docs (Spreadsheet) and they want it to convert this data to CSV format. So I created a configurable console application to accomplish the same.

This utility connects to Google Docs based on configured settings, fetches data from spreadsheet cells and converts it to CSV file.

Background

The first thing is authentication to Google Docs. There are several ways to achieve that, I have used service account(s) for authentication. Below is a link to configure service account:

• https://developers.google.com/identity/protocols/OAuth2ServiceAccount

You need to create a service account and get a JSON key file/Certificate to allow authentication. Each Google service account has a unique email id and make sure you allow this email to access the spreadsheet you want to access. In short, just share the spreadsheet with this email id.

Using the Code

To make the application fully configurable, a section handler is written. It collects information about spreadsheet(s), sheet(s) and cell(s) to be read from Google docs.

<googleSpreadsheetSection>
    <googleSpreadsheets>
      <googleSpreadSheet name="Your Analytics" 
worksheetName="First" useAbsoluteRange="false" 
	range="A1:B11" startRow="0" endRow="0" 
	startColumn="0" endColumn="0" 
	outputFileName="file_one.csv"/>
      <googleSpreadSheet name="My Analytics" 
worksheetName="First" useAbsoluteRange="false" 
	range="A1:B11" startRow="0" endRow="0" 
	startColumn="0" endColumn="0" 
	outputFileName="file_two.csv"/>
    </googleSpreadsheets>
  </googleSpreadsheetSection>

There are few app settings that control the behavior of the application. Below are the application settings you need to configure.

 <appSettings>
    <add key="ApplicationName" value="AccessGoogleDriveDocs" />
    <add key="AppClientName" value=" AcessSpreadsheets" />
    <add key="JsonKeyFilePath" value="D:\Google\Key\AccessGoogleDocs.json" />
    <add key="ApplicationLogFilePath" value="D:\Google\Logs\ApplicationLog.txt" />
    <add key="CSVFolderPath" value="D:\Google\CSV" />
    <add key="NewLineCharacter" value="\n" />
    <add key="ColumnSeparator" value="," />
    <add key="EnableConsoleLogging" value="true" />
    <add key="UseFixedFileNames" value="false" />
  </appSettings>

A short description of important settings is as follows:

• ApplicationName/AppClientName - Anything you want
• JsonKeyFilePath - Path of file you downloaded when creating a key under your Google service account.
• NewLineCharacter - New line character to be used in CSV file
• ColumnSeparator: Column separator to be used in CSV file
• UseFixedFileName: When set true, app will use name defined in section handler otherwise auto-naming will be used
• CSVFolderPath: Path where CSV files will be generated

The connection management to Google Docs is done by GoogleSpreadsheetConnection class.

public GoogleSpreadsheetConnection(string applicationName, string filePath, string clientName)
{
  SpreadsheetConnection = new SpreadsheetsService(applicationName);
  SpreadsheetConnection.RequestFactory = AuthorizationHelper.GetRequestFactoryFromJson
	(filePath, clientName);
}

This class creates a connection to Google Spreadsheet Service using the Google service account and authenticates itself using the JSON key file credentials. A RequestFactory instance is created using AuthorizationHelper class and assigned to SpreadsheetService.

public static GDataRequestFactory GetRequestFactoryFromJson(string filePath, string clientName)
{
   var jsonconfig = Newtonsoft.Json.JsonConvert.DeserializeObject
   				<authorizationinfo>(File.ReadAllText(filePath));
  var credential = new ServiceAccountCredential
  (new ServiceAccountCredential.Initializer(jsonconfig.ClientEmail)
            {
                Scopes = new[] 
                {"https://spreadsheets.google.com/feeds", 
                "https://docs.google.com/feeds" }
            }.FromPrivateKey(jsonconfig.PrivateKey));

            credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Wait();
            var requestFactory = new GDataRequestFactory(clientName);
            requestFactory.CustomHeaders.Add(string.Format
            	("Authorization: Bearer {0}", credential.Token.AccessToken));
            return requestFactory;
        }

Points of Interest

I made a silly mistake while working on this. I configured Google service account and configured the settings but forgot to share my spreadsheet with Google service account. So remember when you create a service account, you will get an email id for service account, MAKE SURE this email/Account has rights to access the Google spreadsheet.

History

• Jan 23, 2016 - First release