// access spreadsheet data
static void AccessSpreadsheet(SpreadsheetFeed feed)
{
string name = _SpreadsheetName;
SpreadsheetEntry spreadsheet = null;
foreach (AtomEntry sf in feed.Entries)
{
if (sf.Title.Text == name)
{
spreadsheet = (SpreadsheetEntry)sf;
}
}
if (spreadsheet == null)
{
Debug.Log("There is no such spreadsheet with such title in your docs.");
return;
}
// Get the first worksheet of the first spreadsheet.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
foreach (ListEntry row in listFeed.Entries)
{
//access spreadsheet data here
}
}
public WorksheetEntry GetWorksheet(SpreadsheetFeed feed, string worksheetName)
{
var link = feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var worksheetQuery = new WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = worksheetName;
var worksheetFeed = _service.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1) throw new Exception(String.Format("Did not find exactly 1 {0} worksheet.", worksheetName));
return (WorksheetEntry)worksheetFeed.Entries[0];
}
public StatusReporter()
{
this.service = new SpreadsheetsService("Seeker");
this.service.setUserCredentials(username, password);
XmlTextReader reader = new XmlTextReader(@"\\cob-hds-1\compression\QC\QCing\otherFiles\SpreadSheetDictionary.xml");
this.spreadsheets = new Dictionary<string, string>();
string key = "";
string value = "";
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Text:// Display text
Console.Write(reader.Name);
key = reader.Value;
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Text)
{
value = reader.Value;
break;
}
}
if (value != "")
{
if (!this.spreadsheets.ContainsKey(key))
{
this.spreadsheets.Add(key, value);
}
}
break;
}
}
SpreadsheetQuery query = new SpreadsheetQuery();
this.allSheets = this.service.Query(query);
for (int i = 0; i < allSheets.Entries.Count; i++)
{
if (this.allSheets.Entries[i].AlternateUri.Content.Contains("0AnSESHA7JsTFdFFBNjZ1U3JzdEFXdlZOa0dhQXJNMEE"))
{
this.ReportSpreadSheet = (SpreadsheetEntry)allSheets.Entries[i];
}
if (this.allSheets.Entries[i].AlternateUri.Content.Contains("0AnSESHA7JsTFdDBGUWtqRVJVbEFFYVlaUmZnRTBaVWc"))
{
this.failSheet = (SpreadsheetEntry)allSheets.Entries[i];
}
}
}
/// <summary>
/// this will reload the list of spreadsheets from the server and reset the
/// currently active spreadsheet/worksheet
/// </summary>
/// <returns></returns>
public void Refresh()
{
Application.sf = null;
Application.se = null;
Application.we = null;
}
private void EnsureSpreadsheetFeed()
{
if (Application.sf == null)
{
SpreadsheetQuery query = new SpreadsheetQuery();
Application.sf = service.Query(query);
}
}
public void logItConference(string fileName, string result)
{
try
{
this.canClose = false;
WorksheetEntry worksheet = null;
CellFeed cells = null;
// FIND THE SPREADSHEET AND WORKSHEET
SpreadsheetQuery query = new SpreadsheetQuery();
this.allSheets = this.service.Query(query);
String lastCode = "";
this.currentSpreadSheet = new SpreadsheetEntry();
string s = fileName;
string code = this.getCode(s);
if (code == "NO FILE CODE FOUND")
{
return;
}
string spreadsheetCode = spreadsheets[code];
if (lastCode != this.getFullCode(s))
{
lastCode = this.getFullCode(s);
for (int i = 0; i < this.allSheets.Entries.Count; i++)
{
if (this.allSheets.Entries[i].AlternateUri.Content.Contains(spreadsheetCode))
{
this.currentSpreadSheet = (SpreadsheetEntry)this.allSheets.Entries[i];
WorksheetFeed wsFeed = this.currentSpreadSheet.Worksheets;
for (int x = 0; x < wsFeed.Entries.Count; x++)
{
WorksheetEntry wksht = (WorksheetEntry)wsFeed.Entries[x];
worksheet = wksht;
CellQuery Querycells = new CellQuery(wksht.CellFeedLink);
Querycells.MaximumColumn = 1;
Querycells.MaximumRow = 2;
Querycells.MinimumColumn = 1;
Querycells.MinimumRow = 2;
this.cells = this.service.Query(Querycells);
string fix = System.IO.Path.GetFileName(s);
if (fix.Contains(this.cells.Entries[0].Content.Content))
{
this.currentWorkSheet = wksht;
break;
}
}
}
}
// NOW, if all went well, we are in the right sheet. Create the list of the needed things--
//List of languages
CellQuery languageQuery = new CellQuery(this.currentWorkSheet.CellFeedLink);
languageQuery.MinimumRow = 3;
languageQuery.MaximumColumn = 2;
languageQuery.MinimumColumn = 2;
languageQuery.ReturnEmpty = ReturnEmptyCells.no;
CellFeed languages = this.service.Query(languageQuery);
//OVP tabs
CellQuery OVPTabs = new CellQuery(this.currentWorkSheet.CellFeedLink);
OVPTabs.MinimumRow = OVPTabs.MaximumRow = 1;
CellFeed columnHeadings = this.service.Query(OVPTabs);
//USE THIS TO FIND EACH INDIVIDUAL VIDEO'S COLUMN
uint row = this.getLanguageRow(s, languages);
uint col = this.getEditColumn(s, columnHeadings, "QC", this.currentWorkSheet);
//OVP tabs
//USE THIS TO FIND EACH INDIVIDUAL VIDEO'S COLUMN
if (col == 0 || row == 0)
{
// something failed--write to the google doc to say that it failed
return;
}
//Get Individual Cell
CellQuery edit = new CellQuery(this.currentWorkSheet.CellFeedLink);
edit.MaximumRow = edit.MinimumRow = row;
edit.MaximumColumn = edit.MinimumColumn = col;
edit.ReturnEmpty = ReturnEmptyCells.yes;
CellFeed editCell = this.service.Query(edit);
CellEntry temp = (CellEntry)editCell.Entries[0];
if (this.initials == "")
{
temp.InputValue = "";
}
else
{
temp.InputValue = result;
}
temp.Update();
this.canClose = true;
}
}
catch (Exception exeption)
{
logger.writeErrorLog("Failed to log " + fileName + " in conference google docs " + DateTime.Now);
this.canClose = true;
}
this.canClose = true;
}
/// <summary>
/// Sets the list view on the Spreadsheets page with the titles of the
/// spreadsheets in the feed
/// </summary>
/// <param name="feed">The spreadsheets feed</param>
void setSpreadsheetListView(SpreadsheetFeed feed)
{
// Clear out the old list
this.spreadsheetListView.Items.Clear();
AtomEntryCollection entries = feed.Entries;
for (int i = 0; i < entries.Count; i++)
{
// Get the worksheets feed URI
AtomLink worksheetsLink = entries[i].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel,
AtomLink.ATOM_TYPE);
// Create an item that will display the title and hide the worksheets feed URI
ListViewItem item = new ListViewItem(new string[2]{entries[i].Title.Text, worksheetsLink.HRef.Content});
this.spreadsheetListView.Items.Add(item);
}
}
public static void initialize()
{
try {
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
logonParameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
logonParameters.ClientId = Constants.CLIENT_ID;
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
logonParameters.ClientSecret = Constants.CLIENT_SECRET;
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
logonParameters.RedirectUri = Constants.REDIRECT_URI;
// Set the scope for this particular service.
logonParameters.Scope = Constants.SCOPE;
// Get the authorization code
logonParameters.AccessCode = frmAuthCode.getAuthCode(logonParameters);
OAuthUtil.GetAccessToken(logonParameters);
string accessToken = logonParameters.AccessToken;
logger.Debug("OAuth Access Token: " + accessToken);
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, Constants.APPLICATION_NAME, logonParameters);
service = new SpreadsheetsService(Constants.APPLICATION_NAME);
service.RequestFactory = requestFactory;
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
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
logger.Debug(String.Format("Title: {0}, URI: {1}", entry.Title.Text, entry.Id.Uri.ToString()));
}
isInitialized = true;
} catch (Exception e) {
MessageBox.Show("Could not Authenticate - shutting down.");
logger.Error("Could not Authenticate", e);
System.Environment.Exit(-1);
}
}
private SpreadsheetEntry GetSpreadsheetEntry(SpreadsheetFeed feed)
{
SpreadsheetEntry res = new SpreadsheetEntry();
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text.Equals(ApiUtils.c_sExcelFileName))
{
res = entry;
break;
}
}
return res;
}
private static WorksheetEntry CreateWorksheet(string worksheetTitle, uint rows, uint columns, SpreadsheetsService service, SpreadsheetFeed feed)
{
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = worksheetTitle;
worksheet.Rows = rows;
worksheet.Cols = columns;
WorksheetFeed wsFeed = spreadsheet.Worksheets;
service.Insert(wsFeed, worksheet);
// retrieve the newly created worksheet from google
AtomLink worksheetLink = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery worksheetQuery = new WorksheetQuery(worksheetLink.HRef.ToString())
{
Title = worksheetTitle,
};
wsFeed = service.Query(worksheetQuery);
return (WorksheetEntry)wsFeed.Entries[0];
}