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.
https://docs.google.com/spreadsheets/d/{your-spreadsheet-id}/edit#gid=0
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.
public class GoogleService
{
private readonly string _googleSecretJsonFilePath;
private readonly string _applicationName;
private readonly string[] _scopes;
public GoogleService(string googleSecretJsonFilePath, string applicationName, string[] scopes)
{
_googleSecretJsonFilePath = googleSecretJsonFilePath;
_applicationName = applicationName;
_scopes = scopes;
}
public GoogleCredential GetGoogleCredential()
{
GoogleCredential credential;
using (var stream =
new FileStream(_googleSecretJsonFilePath, FileMode.Open, FileAccess.Read))
{
credential = GoogleCredential.FromStream(stream).CreateScoped(_scopes);
}
return credential;
}
public SheetsService GetSheetsService()
{
var credential = GetGoogleCredential();
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = _applicationName,
});
return sheetsService;
}
}
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
public class SpreadSheet
{
public SpreadSheetRow HeaderRow { get; set; }
public List<SpreadSheetRow> Rows { get; set; }
}
public class SpreadSheetRow
{
private readonly IList<Object> _values;
public SpreadSheetRow(IList<Object> values)
{
_values = values;
}
public string Value0 => _getValue(0);
public string Value1 => _getValue(1);
private string _getValue(int columnIndex)
{
try
{
var s = _values[columnIndex].ToString();
return s;
}
catch (Exception ex)
{
return String.Empty;
}
}
}
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.
public class GoogleSpreadSheetReader
{
private readonly SheetsService _sheetService;
public GoogleSpreadSheetReader(GoogleService googleService)
{
_sheetService = googleService.GetSheetsService();
}
public SpreadSheet GetSpreadSheet(string spreadSheetId,string range)
{
SpreadsheetsResource.ValuesResource.GetRequest request = _sheetService.Spreadsheets.Values.Get(spreadSheetId, range);
ValueRange response = request.Execute();
IList<IList<Object>> values = response.Values;
var rows = new List<SpreadSheetRow>();
for (int i = 1; i < values.Count; i++)
{
var row = new SpreadSheetRow(values[i]);
rows.Add(row);
}
var headerRow = new SpreadSheetRow(values[0]);
var spreadSheet = new SpreadSheet();
spreadSheet.HeaderRow = headerRow;
spreadSheet.Rows = new List<SpreadSheetRow>();
spreadSheet.Rows.AddRange(rows);
return spreadSheet;
}
}
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.
class Program
{
static void Main(string[] args)
{
Console.WriteLine(System.AppDomain.CurrentDomain.BaseDirectory.ToString());
var googleSecretJsonFilePath = $"{System.AppDomain.CurrentDomain.BaseDirectory}\\GoogleSecret\\GoogleSecret.json";
var applicationName = "My Project";
string[] scopes = { SheetsService.Scope.SpreadsheetsReadonly };
var googleService = new GoogleService(googleSecretJsonFilePath, applicationName, scopes);
var spreadSheetId = "your-spreadsheet-id";
var range = "A:B";
var reader = new GoogleSpreadSheetReader(googleService);
var spreadSheet = reader.GetSpreadSheet(spreadSheetId, range);
Console.WriteLine(JsonConvert.SerializeObject(spreadSheet.Rows));
Console.Read();
}
}
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.
