Skip to content

Instantly share code, notes, and snippets.

@markembling
Created December 9, 2012 13:16
Show Gist options
  • Save markembling/4244834 to your computer and use it in GitHub Desktop.
Save markembling/4244834 to your computer and use it in GitHub Desktop.
Google Drive & Spreadsheets Using OAuth 2.0 Service Account Example. See http://markembling.info/2012/12/google-spreadsheet-dotnet-oauth2-service-account
Copyright (c) 2012, Mark Embling
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
- Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
- Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
- Neither the name of Mark Embling nor the names of any contributors may be used to endorse or promote products derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
using Google.Apis.Authentication.OAuth2;
using Google.Apis.Authentication.OAuth2.DotNetOpenAuth;
using Google.Apis.Drive.v2;
using Google.Apis.Drive.v2.Data;
using Google.GData.Spreadsheets;
using Google.Apis.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using Google.GData.Client;
namespace GoogleSpreadsheetsTest1 {
class Program {
const string ServiceAccount = "xxxxx@developer.gserviceaccount.com";
const string KeyFile = "xxxxxx-privatekey.p12";
const string KeyPass = "notasecret";
const string ApplicationName = "GoogleSpreadsheetsExample";
const string User = "example@example.com"; // this is the user you wish to act for
static void Main(string[] args) {
var certificate = new X509Certificate2(KeyFile, KeyPass, X509KeyStorageFlags.Exportable);
var afc = new AssertionFlowClient(GoogleAuthenticationServer.Description, certificate) {
ServiceAccountId = ServiceAccount,
Scope = DriveService.Scopes.Drive.GetStringValue() + " https://spreadsheets.google.com/feeds",
ServiceAccountUser = User
};
var auth = new OAuth2Authenticator<AssertionFlowClient>(afc, AssertionFlowClient.GetState);
// DRIVE - make the spreadsheet "file"
var driveService = new DriveService(auth);
var file = new File();
file.Title = "Test spreadsheet";
file.Description = string.Format("Created via {0} at {1}", ApplicationName, DateTime.Now.ToString());
file.MimeType = "application/vnd.google-apps.spreadsheet";
var request = driveService.Files.Insert(file);
var result = request.Fetch();
var spreadsheetLink = "https://spreadsheets.google.com/feeds/spreadsheets/" + result.Id;//result.SelfLink;
Console.WriteLine("Created at " + spreadsheetLink);
Console.WriteLine("------------");
// SPREADSHEETS - edit the newly created spreadsheet
var spreadsheetsService = new SpreadsheetsService(ApplicationName);
spreadsheetsService.RequestFactory = new ServiceAccountGDataRequestFactory(ApplicationName, auth);
// query for the one we just made
Console.WriteLine("List the one we just made up above here:");
var query = new SpreadsheetQuery(spreadsheetLink);
var feed = spreadsheetsService.Query(query);
var sheet = (SpreadsheetEntry)feed.Entries[0];
Console.WriteLine(sheet.Title.Text);
// Do some stuff with it
var worksheetsFeed = sheet.Worksheets;
var worksheet = (WorksheetEntry)worksheetsFeed.Entries[0];
worksheet.Title.Text = "Created by " + ApplicationName;
// worksheet.Cols = 10;
// worksheet.Rows = 8;
worksheet.Update();
// Data via cell API - works but one update request per cell
var cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.ReturnEmpty = ReturnEmptyCells.yes;
var cellFeed = spreadsheetsService.Query(cellQuery);
foreach (CellEntry cell in cellFeed.Entries) {
Console.WriteLine("cell");
Console.WriteLine(cell.Title.Text);
switch (cell.Title.Text) {
case "A1":
cell.InputValue = "Joe";
cell.Update();
break;
case "A2":
cell.InputValue = "Bloggs";
cell.Update();
break;
default:
break;
}
}
// Via cell API, bulk update - more efficient
DoBulkUpdate(spreadsheetsService, worksheet);
Console.WriteLine("Done");
Console.ReadLine();
}
private class CellIdentifier {
public uint Row { get; private set; }
public uint Col { get; private set; }
public CellIdentifier(uint row, uint col) {
Row = row;
Col = col;
}
public string Id { get { return string.Format("R{0}C{1}", Row, Col); } }
}
private static void DoBulkUpdate(SpreadsheetsService service, WorksheetEntry worksheet) {
Console.WriteLine("Attempting batch update...");
var cellQuery = new CellQuery(worksheet.CellFeedLink); //new CellQuery(result.Id, "od6", "private", "full");
var cellFeed = service.Query(cellQuery);
// Work out those we wish to update
var cellIdentifiers = new List<CellIdentifier>();
for (uint row = 3; row <= 5; row++) {
for (uint col = 1; col <= 3; col++) {
cellIdentifiers.Add(new CellIdentifier(row, col));
}
}
// Query Google's API for these cells...
CellFeed cellsToUpdate = BatchQuery(service, cellFeed, cellIdentifiers);
// Update query...
CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
foreach (CellEntry cell in cellsToUpdate.Entries) {
CellEntry batchEntry = cell;
batchEntry.InputValue = string.Format("hello {0}", cell.BatchData.Id);
batchEntry.BatchData = new GDataBatchEntryData(cell.BatchData.Id, GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
var batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
// Check all is well
foreach (CellEntry entry in batchResponse.Entries) {
var currentCellId = entry.BatchData.Id;
if (entry.BatchData.Status.Code == 200) {
Console.WriteLine(string.Format("Cell {0} succeeded", currentCellId));
} else {
Console.WriteLine(string.Format("Cell {0} failed: (status {1}) {2}", currentCellId, entry.BatchData.Status.Code, entry.BatchData.Status.Reason));
}
}
}
private static CellFeed BatchQuery(SpreadsheetsService service, CellFeed cellFeed, IEnumerable<CellIdentifier> cellIdentifiers) {
CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
foreach (var cellId in cellIdentifiers) {
CellEntry batchEntry = new CellEntry(cellId.Row, cellId.Col, cellId.Id);
batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.Id));
batchEntry.BatchData = new GDataBatchEntryData(cellId.Id, GDataBatchOperationType.query);
batchRequest.Entries.Add(batchEntry);
}
CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
return queryBatchResponse;
}
}
}
using Google.Apis.Authentication;
using Google.GData.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace GoogleSpreadsheetsTest1 {
public class ServiceAccountGDataRequestFactory : GDataRequestFactory {
private IAuthenticator _authenticator;
public ServiceAccountGDataRequestFactory(string userAgent, IAuthenticator auth) : base(userAgent) {
_authenticator = auth;
}
public override IGDataRequest CreateRequest(GDataRequestType type, Uri uriTarget) {
return new ServiceAccountGDataRequest(type, uriTarget, this);
}
public IAuthenticator Authenticator {
get { return _authenticator; }
}
}
public class ServiceAccountGDataRequest : IGDataRequest, IDisposable, ISupportsEtag {
private GDataRequest _underlying;
private ServiceAccountGDataRequestFactory _factory;
public ServiceAccountGDataRequest(GDataRequestType type, Uri uriTarget, ServiceAccountGDataRequestFactory factory) {
_factory = factory;
var ctor = typeof(GDataRequest).GetConstructors(BindingFlags.Instance | BindingFlags.NonPublic)[0];
_underlying = (GDataRequest)ctor.Invoke(new object[] { type, uriTarget, factory });
}
public GDataCredentials Credentials {
get {
return _underlying.Credentials;
}
set {
_underlying.Credentials = value;
}
}
public void Execute() {
var request = _underlying.GetFinalizedRequest();
// do auth
_factory.Authenticator.ApplyAuthenticationToRequest(request);
_underlying.Execute();
}
public System.IO.Stream GetRequestStream() {
return _underlying.GetRequestStream();
}
public System.IO.Stream GetResponseStream() {
return _underlying.GetResponseStream();
}
public DateTime IfModifiedSince {
get {
return _underlying.IfModifiedSince;
}
set {
_underlying.IfModifiedSince = value;
}
}
public bool UseGZip {
get {
return _underlying.UseGZip;
}
set {
_underlying.UseGZip = value;
}
}
public void Dispose() {
_underlying.Dispose();
}
public string Etag {
get {
return _underlying.Etag;
}
set {
_underlying.Etag = value;
}
}
}
}
@preguntoncojonero
Copy link

developers.google.com/google-apps/spreadsheets/authorize

Important: OAuth 1.0 is no longer supported and will be disabled on May 5, 2015. If your application uses OAuth 1.0, you must migrate to OAuth 2.0 or your application will cease functioning.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment