private SpreadsheetsService AuthenticateGoogleUser(string val)
{
SpreadsheetsService service = new SpreadsheetsService("NodeLookupProgram");
service.setUserCredentials(ConfigurationManager.AppSettings["username"].ToString(), ConfigurationManager.AppSettings["passcode"].ToString());
return service;
}
private static ListEntry InsertRow(SpreadsheetsService service, WorksheetEntry entry, NameValueCollection parameters)
{
logger.Debug("inserting row...");
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
ListEntry newRow = new ListEntry();
foreach(string key in parameters)
{
ListEntry.Custom curElement = new ListEntry.Custom();
curElement.Value = parameters[key];
curElement.LocalName = key;
newRow.Elements.Add(curElement);
}
// add datetime
ListEntry.Custom el = new ListEntry.Custom();
el.Value = parameters["data"];
el.LocalName = DateTime.Now.ToString() ;
newRow.Elements.Add(el);
ListEntry insertedRow = feed.Insert(newRow);
return insertedRow;
}
public GoogleNinjaService(ISpreadsheetConfiguration googleSpreadsheetConfiguration)
{
this.googleSpreadsheetConfiguration = googleSpreadsheetConfiguration;
service = new SpreadsheetsService("tretton37-NinjaBook");
service.setUserCredentials(googleSpreadsheetConfiguration.Username, googleSpreadsheetConfiguration.Password);
}
/// <summary>
/// Retrieves and prints a list feed of the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to retrieve</param>
/// <param name="reverseRows">true if the rows in the worksheet should
/// be reversed when returned from the server</param>
private static void RetrieveListFeed(SpreadsheetsService service, WorksheetEntry entry,
bool reverseRows)
{
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
Console.WriteLine();
Console.WriteLine("This worksheet's list feed URL is:");
Console.WriteLine(listFeedLink.HRef);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
if (reverseRows)
{
query.OrderByPosition = true;
query.Reverse = true;
}
ListFeed feed = service.Query(query);
Console.WriteLine();
Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements) {
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
public SpreadSheet()
{
service = new SpreadsheetsService("stock-market");
service.setUserCredentials("shurai", "$gva99void!");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry ssentry = (SpreadsheetEntry)feed.Entries[0];
AtomLink sslink = ssentry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wkquery = new WorksheetQuery(sslink.HRef.ToString());
WorksheetFeed wkfeed = service.Query(wkquery);
WorksheetEntry wkentry = (WorksheetEntry)wkfeed.Entries[0];
listFeedLink = wkentry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = service.Query(listQuery);
Console.WriteLine("Worksheet has {0} rows: ", listFeed.Entries.Count);
foreach (ListEntry worksheetRow in listFeed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements)
{
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
// grab your spreadsheet's ID / "key" from the URL to access your doc...
// e.g. everything after "key=" in the URL: https://docs.google.com/spreadsheet/ccc?key=0Ak-N8rbAmu7WdGRFdllybTBIaU1Ic0FxYklIbk1vYlE
// make sure stop as soon as you hit an ampersand, those are additional URL parameters we don't need
public static ListFeed GetSpreadsheet(string spreadsheetID)
{
// We need this fake certificate to trick Mono's security to use HTTPS... doesn't work in webplayer's security sandbox
InsecureSecurityCertificatePolicy.Instate();
SpreadsheetsService service = new SpreadsheetsService( "UnityConnect" );
ListQuery listQuery = new ListQuery( "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/default/public/values" );
const bool debugTest = false; // change to TRUE to enable debug output
if ( debugTest ) {
ListFeed listFeed = service.Query( listQuery );
Debug.Log( "loaded Google Doc Spreadsheet: " + listFeed.Title.Text );
// Iterate through each row, printing its cell values.
foreach ( ListEntry row in listFeed.Entries ) {
// Print the first column's cell value
Debug.Log( row.Title.Text );
// Iterate over the remaining columns, and print each cell value
foreach ( ListEntry.Custom element in row.Elements ) {
Debug.Log( element.Value );
}
}
}
return service.Query( listQuery );
}
public static Worksheet Read(WorksheetEntry entry, SpreadsheetsService service)
{
Worksheet sheet = new Worksheet(entry.Title.Text, entry.Rows, entry.Cols);
CellQuery cq = new CellQuery(entry.CellFeedLink);
CellFeed feed = service.Query(cq);
foreach (CellEntry cellentry in feed.Entries)
{
Cell cell = new Cell();
double output;
if (Double.TryParse(cellentry.Cell.Value, out output))
{
cell.Type = DataType.Number;
cell.Value = output;
}
else
{
cell.Type = DataType.String;
cell.Value = cellentry.Cell.Value;
}
sheet[cellentry.Cell.Row - 1, cellentry.Cell.Column - 1] = cell;
}
return sheet;
}
public ActionResult AllCells()
{
SpreadsheetsService service;
service = new SpreadsheetsService("DevFestEvent");
service.setUserCredentials(
ConfigurationManager.AppSettings["GoogleUser"],
ConfigurationManager.AppSettings["GoogleUserPassword"]);
SpreadsheetQuery q = new SpreadsheetQuery(App.SheetFeedData);
var feed = service.Query(q);
AtomLink l = feed.Entries.First().Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(l.HRef.ToString());
WorksheetFeed f = service.Query(query);
foreach (var item in f.Entries)
{
AtomLink cellFeedLink = item.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cellfeedlink = cellFeedLink.HRef.ToString();
CellQuery cquery = new CellQuery(cellfeedlink);
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
uint rownum = 2;
foreach (CellEntry curCell in cfeed.Entries)
{
rownum = curCell.Cell.Row;
}
}
return View(f);
}
public ExcelTable(int day,ExcelDoc doc, WorksheetEntry entry,SpreadsheetsService service)
{
_entry = entry;
_doc = doc;
DayOfWeek = day;
_service = service;
}
private static Dictionary<string, CellEntry> CreateEntryCellsMap(SpreadsheetsService service, CellFeed cellFeed,
List<ExcelCell> cells)
{
Dictionary<string, CellEntry> res = new Dictionary<string, CellEntry>();
CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
foreach (ExcelCell cell in cells) {
if (cell.GetEntry() == null) {
CellEntry batchEntry = new CellEntry(cell.Row, cell.Column, cell.GetBatchID());
batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cell.GetBatchID()));
batchEntry.BatchData = new GDataBatchEntryData(cell.GetBatchID(), GDataBatchOperationType.query);
batchRequest.Entries.Add(batchEntry);
}
else {
if (!res.ContainsKey(cell.GetBatchID())) {
res.Add(cell.GetBatchID(), cell.GetEntry());
}
}
}
if (batchRequest.Entries.Count > 0) {
CellFeed queryBatchResponse = (CellFeed) service.Batch(batchRequest, new Uri(cellFeed.Batch));
foreach (CellEntry entry in queryBatchResponse.Entries) {
res.Add(entry.BatchData.Id, entry);
}
}
return res;
}
public GDataAPI(NameValueCollection parameters)
{
try
{
SpreadsheetsService service = new SpreadsheetsService("post2spreadsheet");
service.setUserCredentials(cardeira.Properties.Settings.Default.gUserName, cardeira.Properties.Settings.Default.gPassword);
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry entry = null;
foreach (SpreadsheetEntry e in feed.Entries)
{
entry = e;
logger.Debug("Spreadsheet: {0}", entry.Title.Text);
if (entry.Title.Text == cardeira.Properties.Settings.Default.spreadsheetkey)
break;
}
if (entry != null)
InsertRow(service, (WorksheetEntry)entry.Worksheets.Entries[0], parameters);
}
catch (Exception e)
{
logger.ErrorException("error writing to spreadsheet", e);
}
}
public string GetSpreadsheets(Hashtable State, RadComboBox Spreadsheets)
{
try
{
SpreadsheetsService service = new SpreadsheetsService(State["SelectedApp"].ToString());
GOAuthRequestFactory requestFactory = new GOAuthRequestFactory("wise", "MobiFlex");
requestFactory.ConsumerKey = ConfigurationManager.AppSettings["GoogleAppsKey"];
requestFactory.ConsumerSecret = ConfigurationManager.AppSettings["GoogleAppsSecret"];
service.RequestFactory = requestFactory;
//get all spreadsheets
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
query.OAuthRequestorId = State["CustomerEmail"].ToString();
query.Uri = new Uri("https://spreadsheets.google.com/feeds/spreadsheets/private/full?xoauth_requestor_id=" + State["CustomerEmail"].ToString());
SpreadsheetFeed feed = service.Query(query);
Spreadsheets.Items.Clear();
Spreadsheets.Items.Add(new RadComboBoxItem("Select Spreadsheet ->", "->"));
foreach (SpreadsheetEntry entry in feed.Entries)
{
string spreadsheet_name = entry.Title.Text;
Spreadsheets.Items.Add(new RadComboBoxItem(spreadsheet_name, spreadsheet_name));
}
return "OK";
}
catch (Exception ex)
{
Util util = new Util();
util.LogError(State, ex);
return ex.Message;
}
}
public WorksheetEntry GetWorkSheet(SpreadsheetsService service, string spreadSheetName, string workSheetName)
{
if (service == null) {
return null;
}
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = spreadSheetName;
query.Exact = true;
//Iterate over the results
var feed = service.Query(query);
if (feed.Entries.Count == 0) {
Debug.LogError ("can't find spreadsheet : " + spreadSheetName);
return null;
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = null;
for (int i=0; i<wsFeed.Entries.Count; i++) {
if(wsFeed.Entries[i].Title.Text == workSheetName) {
worksheet = wsFeed.Entries[i] as WorksheetEntry;
break;
}
}
if (worksheet == null) {
Debug.LogError("can't find worksheet : " + workSheetName);
}
return worksheet;
}
private static void DoWork()
{
service = new SpreadsheetsService("Comwell");
service.RequestFactory = GApi.RequestFactory;
// Make the request to Google
// See other portions of this guide for code to put here...
Console.Write("Retrieving data... ");
ListQuery query = new ListQuery(GApi.SpreadsheetSubmissions, "1", "private", "values");
ListFeed feed = service.Query(query);
Console.WriteLine("complete.");
Console.Write("Processing data... ");
Submission.ProcessSubmissions(feed);
Console.WriteLine("complete.");
Console.ReadLine();
Console.WriteLine("There are " + Submission.Submissions.Count + " submissions data retrieved.");
foreach (Submission sub in Submission.Submissions)
{
Console.WriteLine(sub.ToString());
}
Console.ReadLine();
}
private void button1_Click(object sender, EventArgs e)
{
//////////////////////////////////////////////////////////////////////////////
//// STEP 5: Make an OAuth authorized request to Google
//////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "TourTracking", parameters);
SpreadsheetsService service = new SpreadsheetsService("TourTracking");
service.RequestFactory = requestFactory;
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry entry in feed.Entries)
{
// Print the title of this spreadsheet to the screen
MessageBox.Show(entry.Title.Text);
}
}
public static AtomEntryCollection GetSheetNames(SpreadsheetsService zSpreadsheetService, AtomEntry zSheetEntry)
{
var link = zSheetEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString());
var wsfeed = zSpreadsheetService.Query(wsquery);
return wsfeed.Entries;
}
public GoogleClient(TrelloStatsConfiguration configuration)
{
_configuration = configuration;
_service = new SpreadsheetsService("trelloStats");
_service.setUserCredentials(_configuration.GmailEmailAddress, _configuration.GmailPassword);
_configuration = configuration;
}
public SpreadsheetProxy(Credentials credentials)
{
spreadsheetService = new SpreadsheetsService("groundfloor-svc1");
spreadsheetService.setUserCredentials(credentials.username, credentials.password);
documentService = new DocumentsService("groundfloor-svc2");
documentService.setUserCredentials(credentials.username, credentials.password);
}
public SpreadsheetsService GetService()
{
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", GetParameters());
SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
return service;
}
public GoogleReferenceReader(ProjectLayoutReference zReference)
{
ReferencePath = (File.Exists(zReference.RelativePath)
? Path.GetFullPath(zReference.RelativePath)
: CardMakerMDI.ProjectPath + zReference.RelativePath);
m_zSpreadsheetsService = GoogleSpreadsheet.GetSpreadsheetsService(APP_NAME, CLIENT_ID,
CardMakerMDI.GoogleAccessToken);
}
/// <summary>
/// New Method To get Worksheet
/// </summary>
/// <returns></returns>
public WorksheetEntry GetWorksheet(OAuth2Parameters parameters, string IntegrationName, string SpreadSheetURI, SpreadsheetsService service)
{
SpreadsheetQuery query = new SpreadsheetQuery(SpreadSheetURI);
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
return worksheet;
}
/// <summary>
/// Creates a new instance of the SpreadsheetHelper class with an already existing <typeparamref name="Google.GData.Spreadsheets.SpreadsheetsService" /> object.
/// </summary>
/// <param name="service">The <typeparamref name="Google.GData.Spreadsheets.SpreadsheetsService" /> object with which the instance should be created.</param>
/// <exception cref="System.ArgumentException">The service doesn't provide any authentication</exception>
public SpreadsheetHelper(SpreadsheetsService service)
{
// Unfortunately we cannot check if password is not null.
if (service.Credentials == null || service.Credentials.Username == null)
{
throw new ArgumentException(LocalizationManager.GetLocalizedString("ServiceNoAuthentication"), "service");
}
this.service = service;
}
/// <summary>
/// constructor for webapplications. Obtain the token using the authsub
/// helper methods
/// </summary>
/// <param name="token">Your authentication token</param>
/// <returns></returns>
public Application(string token)
{
GAuthSubRequestFactory authFactory =
new GAuthSubRequestFactory("wise", Application.Name);
authFactory.Token = token;
Application.service = new SpreadsheetsService(authFactory.ApplicationName);
Application.service.RequestFactory = authFactory;
}
public DatabaseClient(string username, string password) {
var docService = new DocumentsService("database");
docService.setUserCredentials(username, password);
documentService = docService;
var ssService = new SpreadsheetsService("database");
ssService.setUserCredentials(username, password);
spreadsheetService = ssService;
}
private IEnumerable<WorksheetEntry> GetWorksheetEntrees(SpreadsheetsService service = null)
{
if (service == null)
service = new SpreadsheetsService(applicationName);
service.setUserCredentials(userName, password);
var worksheetQuery = new WorksheetQuery(spreadsheetKey, "private", "full");
var wsFeed = service.Query(worksheetQuery);
return wsFeed.Entries.OfType<WorksheetEntry>();
}
//Fetch all worksheets for given Spreadsheet
public static ArrayList getWorksheetList(string userName, string passWord, string spreadSheetName)
{
ArrayList worksheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService(spreadSheetName + "Service");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == spreadSheetName)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = service.Query(wquery);
foreach (WorksheetEntry worksheet in wfeed.Entries)
{
worksheetList.Add(worksheet.Title.Text);
}
}
}
return worksheetList;
}
/// <summary>
/// Creates a new instance of the RestoreData class.
/// </summary>
/// <param name="settings">The current Settings object.</param>
/// <param name="values">The current float-array with the values.</param>
/// <param name="sheetEntries">The current array of SpreadsheetEntry objects.</param>
/// <param name="sheetService">The current SpreadsheetService object.</param>
public RestoreData(Settings settings, float[] values, SpreadsheetEntry[] sheetEntries, SpreadsheetsService sheetService)
{
this.Settings = settings;
this.Values = values;
this.SheetEntries = sheetEntries;
this.SheetService = SheetService;
}
public SpreadSheetManager(OAuth2Parameters parameters)
{
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
}
/// <summary>
/// Initilize sheets API with saved token
/// </summary>
public void Initilize()
{
PermissiveCert.Instate();
RefreshToken = EditorPrefs.GetString("Datablocks_RefreshToken");
AccessToken = EditorPrefs.GetString("Datablocks_AccessToken");
Service = new SpreadsheetsService("Datablocks for Unity");
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
oAuthParameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
oAuthParameters.ClientId = CLIENT_ID;
// Set your OAuth 2.0 Client Secret, which can be obtained at
oAuthParameters.ClientSecret = CLIENT_SECRET;
// Set your Redirect URI, which can be registered at
oAuthParameters.RedirectUri = REDIRECT_URI;
// Set the scope for this particular service.
oAuthParameters.Scope = SCOPE;
if (!string.IsNullOrEmpty(RefreshToken))
{
oAuthParameters.RefreshToken = RefreshToken;
oAuthParameters.AccessToken = AccessToken;
var requestFactory = new GOAuth2RequestFactory(null, "Datablocks for Unity", oAuthParameters);
Service.RequestFactory = requestFactory;
}
}
public ActionResult MySpreadsheets()
{
SpreadsheetsService service = new SpreadsheetsService("chavp-mybook-1");
service.setUserCredentials("iuityuj@rtrtey5e6yrty", "sdfdsfsdfsdf");
WorksheetQuery query = new WorksheetQuery("0AsqxIqqTdYVidExTN0R0THh6XzlBa01pOURWVGRSMGc", "private", "full");
WorksheetFeed feed = service.Query(query);
List<Row> listData = new List<Row>();
foreach (WorksheetEntry worksheet in feed.Entries)
{
AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
CellQuery cellQuery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
foreach (CellEntry curCell in cellFeed.Entries)
{
Cell cell = new Cell { Row = curCell.Cell.Row, Index = curCell.Cell.Column, Data = curCell.Cell.Value };
var row = listData.Find(r => r.Index == curCell.Cell.Row);
if (row == null)
{
row = new Row { Index = curCell.Cell.Row };
listData.Add(row);
}
row.CellList.Add(cell);
}
}
return View();
}
