public GoogleSpreadsheetBrowser(string sAppName, string sClientId, string sGoogleAccessToken, bool bRequireSheetSelect)
{
m_bRequireSheetSelect = bRequireSheetSelect;
InitializeComponent();
m_zSpreadsheetsService = GoogleSpreadsheet.GetSpreadsheetsService(sAppName, sClientId, sGoogleAccessToken);
}
/// <summary>
/// Creates a new instance of the PriceList 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 PriceList(SpreadsheetsService service)
{
this.helper = new SpreadsheetHelper(service);
}
public static void SpreadSheetWork()
{
var certificate = new X509Certificate2(@"GoogleDocAccessProject-b63329148447.p12", "notasecret", X509KeyStorageFlags.Exportable);
const string user = "******";
var serviceAccountCredentialInitializer = new ServiceAccountCredential.Initializer(user)
{
Scopes = new[] { "https://spreadsheets.google.com/feeds" }
}.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("GoogleDocAccessApp")
{
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);
if (feed.Entries.Count == 0)
{
Console.WriteLine("There are no sheets");
}
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry sheet in feed.Entries)
{
// Print the title of this spreadsheet to the screen
Console.WriteLine(sheet.Title.Text);
// Make a request to the API to fetch information about all
// worksheets in the spreadsheet.
WorksheetFeed wsFeed = sheet.Worksheets;
// Iterate through each worksheet in the spreadsheet.
foreach (WorksheetEntry entry in wsFeed.Entries)
{
// Get the worksheet's title, row count, and column count.
string title = entry.Title.Text;
var rowCount = entry.Rows;
var colCount = entry.Cols;
#region "перевод выбранного"
if (title.Contains("перевод выбранного"))
{
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//And get a cell based feed:
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
string[,] cells = new string[rowCount, colCount];
foreach (CellEntry curCell in cfeed.Entries)
//for (int ri = 0; ri < rowCount; ri++)
// for (int ci = 0; ci < colCount; ci++)
{
//CellEntry curCell = cfeed.Entries.FirstOrDefault(x => x.);
//Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
cells[curCell.Cell.Row - 1, curCell.Cell.Column - 1] = curCell.Cell.Value;
}
using (var db = new ProductContext())
{
for (int ri = 0; ri < rowCount; ri++)
{
//for (int ci = 0; ci < colCount; ci++)
if (!string.IsNullOrEmpty(cells[ri, 0]) &&
!string.IsNullOrEmpty(cells[ri, 1]) &&
!string.IsNullOrEmpty(cells[ri, 2]) &&
!string.IsNullOrEmpty(cells[ri, 3]))
{
string titleEng = cells[ri, 1];
if (db.Translations.Any(t => t.titleEng == titleEng))
{
db.Translations.RemoveRange(db.Translations.Where(t => t.titleEng == titleEng));
}
db.Translations.Add(new Translation
{
titleEng = cells[ri, 1],
title = cells[ri, 2],
desc = cells[ri, 3]
});
Console.WriteLine("added {0}", cells[ri, 1]);
}
}
db.SaveChanges();
}
// Create a local representation of the new worksheet.
/*
* WorksheetEntry worksheet = new WorksheetEntry();
* worksheet.Title.Text = "New Worksheet";
* worksheet.Cols = 10;
* worksheet.Rows = 20;
*
* // Send the local representation of the worksheet to the API for
* // creation. The URL to use here is the worksheet feed URL of our
* // spreadsheet.
* WorksheetFeed NewwsFeed = sheet.Worksheets;
* service.Insert(NewwsFeed, worksheet);
*/
}
#endregion
#region категории
if (title.Contains("Категории"))
{
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//And get a cell based feed:
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
string[,] cells = new string[rowCount, colCount];
foreach (CellEntry curCell in cfeed.Entries)
//for (int ri = 0; ri < rowCount; ri++)
// for (int ci = 0; ci < colCount; ci++)
{
//CellEntry curCell = cfeed.Entries.FirstOrDefault(x => x.);
Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
cells[curCell.Cell.Row - 1, curCell.Cell.Column - 1] = curCell.Cell.Value;
}
using (var db = new ProductContext())
{
for (int ri = 0; ri < rowCount; ri++)
{
//for (int ci = 0; ci < colCount; ci++)
if (!string.IsNullOrEmpty(cells[ri, 0]) &&
!string.IsNullOrEmpty(cells[ri, 1]) &&
!string.IsNullOrEmpty(cells[ri, 2])
)
{
string titleEng = cells[ri, 0];
if (db.Translations.Any(t => t.titleEng == titleEng))
{
db.Translations.RemoveRange(db.Translations.Where(t => t.titleEng == titleEng));
}
db.Translations.Add(new Translation
{
titleEng = cells[ri, 0],
title = cells[ri, 1],
isOurCategory = (cells[ri, 2] == "Наша категория" || cells[ri, 2] == "Наша категория 2"),
keyWords = cells[ri, 3],
antiKeyWords = cells[ri, 4]
});
Console.WriteLine("added {0}", cells[ri, 1]);
}
}
db.SaveChanges();
}
}
#endregion
}
}
}
private CacheManager()
{
service = new SpreadsheetsService();
CheckUpdateFitler();
GetUpdatedFiles();
}
protected void ddlexcellst_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (ddlexcellst.SelectedIndex == 0)
{
Label2.Visible = true;
}
else
{
Label2.Visible = false;
OAuth2Parameters parameters_lst = oauthcredentials();
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "Fusion-SpreadSheet", parameters_lst);
SpreadsheetsService service = new SpreadsheetsService("Fusion-SpreadSheet");
service.RequestFactory = requestFactory;
service.RequestFactory = requestFactory;
// TODO: Authorize the service object for a specific user (see other sections)
// 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);
if (feed.Entries.Count == 0)
{
// TODO: There were no spreadsheets, act accordingly.
}
// TODO: Choose a spreadsheet more intelligently based on your
// app's needs.
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[Convert.ToInt32(ddlexcellst.SelectedIndex) - 1];
//Response.Write(spreadsheet.Title.Text + "\n");
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
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());
listQuery.StartIndex = 0;
ListFeed listFeed = service.Query(listQuery);
// Iterate through each row, printing its cell values.
List <category> chart_categories = new List <category>();
ChartModels result = new ChartModels();
categories final_categories = new categories();
List <categories> list_categories = new List <categories>();
List <dataset> collect_dataset = new List <dataset>();
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
TableRow tr = new TableRow();
// Response.Write(row.Title.Text + "\n");
// Iterate over the remaining columns, and print each cell value
dataset final_dataset = new dataset();
List <data> collect_data = new List <data>();
foreach (ListEntry.Custom element in row.Elements)
{
if (row.Title.Text == "Row: 2")
{
category chart_category = new category();
chart_category.label = element.Value.ToString();
chart_categories.Add(chart_category);
}
else
{
data data_value = new data();
final_dataset.seriesname = row.Title.Text;
int n;
bool isNumeric = int.TryParse(element.Value, out n);
if (isNumeric)
{
data_value.value = element.Value.ToString();
collect_data.Add(data_value);
}
}
// Response.Write(element.Value + "\n");
TableCell tc = new TableCell();
if (row.Title.Text == "Row: 2")
{
tc.Text = "";
}
else
{
tc.Text = element.Value;
}
tr.Cells.Add(tc);
}
Table1.Rows.Add(tr);
if (collect_data.Count != 0)
{
final_dataset.data = collect_data;
collect_dataset.Add(final_dataset);
}
}
final_categories.category = chart_categories;
result.dataset = collect_dataset;
list_categories.Add(final_categories);
result.categories = list_categories;
JavaScriptSerializer js = new JavaScriptSerializer();
string res = js.Serialize(result);
string chartjson = JsonConvert.SerializeObject(result.categories);
StringBuilder strJson = new StringBuilder();
strJson.Append("{" +
"'chart': {" +
"'caption': 'Quarterly revenue'," +
"'subCaption':'Last year'," +
"'xAxisName':'Quarter (Click to drill down)'," +
"'subcaptionFontColor':'#0075c2'," +
"'numberPrefix': '$'," +
"'formatNumberScale': '1'," +
"'placeValuesInside': '1'," +
"'decimals': '0'" +
"},");
strJson.Append("'categories':");
strJson.Append(chartjson);
strJson.Append(",");
strJson.Append("'dataset':");
string chartdatajson = JsonConvert.SerializeObject(result.dataset);
strJson.Append(chartdatajson);
strJson.Append("}");
// Initialize the chart.
Chart sales = new Chart("mscolumn3d", "myChart", "600", "350", "json", strJson.ToString());
// Render the chart.
Label1.Text = sales.Render();
}
}
catch (Exception)
{
Response.Redirect("Default.aspx");
}
}
protected void Button2_Click(object sender, EventArgs e)
{
OAuth2Parameters parameters = new OAuth2Parameters();
parameters.ClientId = clientID.Text.Trim();
parameters.ClientSecret = clientSecret.Text.Trim();
parameters.RedirectUri = REDIRECT_URI;
parameters.Scope = SCOPE;
parameters.AccessCode = aToken.Text.Trim();
try
{
//dealing with slack;
string urlWithAccessToken = sToken.Text.Trim();
SlackClient client = new SlackClient(urlWithAccessToken);
client.PostMessage(username: owner.Text.Trim(), text: uStory.Text.Trim(), channel: "#" + cID.Text.Trim());
//Now Deal with spreadsheet.
OAuthUtil.GetAccessToken(parameters);
string accessToken = parameters.AccessToken;
GOAuth2RequestFactory requestFactory = new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = sID.Text.Trim();
// Make a request to the API and get the spreadsheet;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count != 1)
{
msg.Text = "The Mentioned Spreadsheet does not exist";
return;
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "userstory", Value = uStory.Text.Trim()
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "owner", Value = owner.Text.Trim()
});
try
{
// Send the new row to the API for insertion.
service.Insert(listFeed, row);
//Disable button 1 and enable button 2.
Button1.Visible = true;
Button2.Visible = false;
//disable all text boxes except for the access token.
uStory.Enabled = true;
owner.Enabled = true;
clientID.Enabled = true;
clientSecret.Enabled = true;
sID.Enabled = true;
sToken.Enabled = true;
cID.Enabled = true;
aToken.Enabled = true;
//clear all textboxes
uStory.Text = owner.Text = sToken.Text = "";
msg.Text = "Submitted Successfully";
}
catch (Exception)
{
msg.Text = "Error while sending data to spreadsheet.";
}
}
catch (Exception)
{
msg.Text = "Some error occured.";
}
}
static void Main()
{
// spreadsheet service and auth
SpreadsheetsService _service = new SpreadsheetsService(sheetService);
_service.setUserCredentials(userName, password);
// new spreadsheet query
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = sheetTitle; // will find the spreadsheet using title
// make the request
SpreadsheetFeed feed = _service.Query(query);
// get spreadsheet
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
// request the spreadsheets worksheet feeds
WorksheetFeed wsFeeds = spreadsheet.Worksheets;
// target worksheet
WorksheetEntry worksheet = (WorksheetEntry)wsFeeds.Entries[worksheetNumber];
// need request url for the list feed
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// fetch a list
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed parcelList = _service.Query(listQuery);
// fetch list of members
WorksheetEntry memberWorksheet = (WorksheetEntry)wsFeeds.Entries[0];
AtomLink memberListFeedLink = memberWorksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery memberListQuery = new ListQuery(memberListFeedLink.HRef.ToString());
ListFeed memberList = _service.Query(memberListQuery);
// loop through each entry and update the legal cell for each row
int i = 0;
foreach (ListEntry entry in parcelList.Entries)
{
i++;
PropertyInfo property = new PropertyInformation().GetPropertyInfo("", entry.Elements[parcelColumnNumber].Value.ToString());
Parcel parcel = new Parcel(property, memberList);
Console.WriteLine("{0} => {1}, {2}", i, parcel.Number, parcel.IsMember);
// look at each element in entry
foreach (ListEntry.Custom element in entry.Elements)
{
if (element.LocalName == "legal")
{
element.Value = parcel.Legal;
}
if (element.LocalName == "subdivision")
{
element.Value = parcel.Subdivision;
}
if (element.LocalName == "filing")
{
element.Value = parcel.Filing;
}
if (element.LocalName == "ismember")
{
element.Value = parcel.IsMember == true ? "1" : "0";
}
if (element.LocalName == "grantsigned")
{
element.Value = parcel.GrantSigned;
}
if (element.LocalName == "grantfiled")
{
element.Value = parcel.GrantFiled;
}
if (element.LocalName == "memberid")
{
element.Value = parcel.MemberId;
}
if (element.LocalName == "annexmarkercolor")
{
var color = "";
if (parcel.IsMember)
{
color = "small_blue";
}
else
{
color = parcel.IsAnnexed ? "small_red" : "small_yellow";
}
element.Value = color;
}
// mailing address
if (element.LocalName == "mailaddress1")
{
element.Value = parcel.MailAddress1;
}
if (element.LocalName == "mailaddress2")
{
element.Value = parcel.MailAddress2;
}
if (element.LocalName == "mailaddresscity")
{
element.Value = parcel.MailAddressCity;
}
if (element.LocalName == "mailaddressstate")
{
element.Value = parcel.MailAddressState;
}
if (element.LocalName == "mailaddresszip")
{
element.Value = parcel.MailAddressZip;
}
// 1st owner
if (element.LocalName == "owner1")
{
element.Value = parcel.Owner1;
}
// 2nd owner
if (element.LocalName == "owner2")
{
element.Value = parcel.Owner2;
}
}
entry.Update();
}
Console.WriteLine("Done, press any key");
Console.ReadKey();
}
/// <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 override void Load()
{
// inside bindings
Bind <IServerTime>().To <ServerTime>().InCallScope();
Bind <IRepository <InsideModel.Models.Task> >().ToMethod(c => new Repository <InsideModel.Models.Task>(context => context.Task)).InCallScope();
Bind <IRepository <Client> >().ToMethod(c => new Repository <Client>(context => context.Clients)).InCallScope();
Bind <IRepository <InsideUser> >().ToMethod(c => new Repository <InsideUser>(context => context.InsideUser)).InCallScope();
Bind <IRepository <SerpRanking> >().ToMethod(c => new Repository <SerpRanking>(context => context.SerpRanking)).InCallScope();
Bind <IRepository <Contact> >().ToMethod(c => new Repository <Contact>(context => context.Lead)).InCallScope();
Bind <IRepository <Token> >().ToMethod(c => new Repository <Token>(context => context.Token)).InCallScope();
Bind <IRepository <ExternalToken> >().ToMethod(c => new Repository <ExternalToken>(context => context.ExternalToken)).InCallScope();
Bind <IDownloadManager>().To <DownloadManager>().InCallScope();
Bind <IJsonConverter>().To <JsonUtcConverter>().InCallScope();
Bind <IContactService>().To <ContactService>().InCallScope();
Bind <IProductService>().To <ProductService>().InCallScope();
Bind <IRestClient>().To <RestClient>().InCallScope();
Bind <IGoogleAnalyticsApi>().To <GoogleAnalyticsApi>().InCallScope();
Bind <IGoogleAuthentication>().To <Inside.GoogleService.GoogleAuthentication>().InCallScope();
// Task bindings
Bind <IEmailSender>().To <MandrilEmailSender>().InCallScope();
Bind <ITaskDelegate>().To <TaskDelegate>().InTransientScope();
Bind <ITaskQueueStorage>().To <AzureQueueTaskQueue>().InCallScope();
Bind <INotificationEmailDefenitionBuilder>().To <NotificationEmailDefenitionBuilder>().InCallScope();
Bind <INotificationEmailSender>().To <NotificationEmailSender>().InCallScope();
Bind <IAccessTokenProvider>().To <AccessTokenProvider>().InCallScope();
Bind <IPhoneNotificationTaskPerformer>().To <PhoneNotificationTaskPerformer>().InCallScope();
Bind <IPhoneNotificationTextBuilder>().To <PhoneNotificationTextBuilder>().InCallScope();
Bind <INotificationSender>().To <NotificationSender>().InCallScope();
Bind <IAddAnalyticProductDataForClient>().To <AddAnalyticProductDataForClient>().InCallScope();
Bind <IAnalyticDataPropertyExtractor>().To <AnalyticDataPropertyExtractor>().InCallScope();
Bind <ICreateAnalyticDataTasksForClients>().To <CreateAnalyticDataTasksForClients>().InCallScope();
Bind <ICustomEventsImporter>().To <CustomEventsImporter>().InCallScope();
Bind <ICustomEventsExtractor>().To <CustomEventsExtractor>().InCallScope();
Bind <ISeoDataImporter>().To <SeoDataImporter>().InCallScope();
Bind <ISeoDataImportMapper>().To <SeoDataImportMapper>().InCallScope();
Bind <IUserNotificationEmailSender>().To <UserNotificationEmailSender>().InCallScope();
Bind <ITaskManager>().To <TaskManager>().InCallScope();
Bind <IImportCustomEventsTaskCreator>().To <ImportCustomEventsTaskCreator>().InCallScope();
Bind <IRemoveExpiredTokens>().To <RemoveExpiredTokens>().InCallScope();
Bind <IUpdatePhonecalls>().To <UpdatePhonecalls>().InCallScope();
Bind <ICreateUpdatePhonecallsTasksForClients>().To <CreateUpdatePhonecallsTasksForClients>().InCallScope();
Bind <IExternalDataProvider>().To <ExternalDataProvider>().InCallScope();
Bind <ISmsNotificationTextBuilder>().To <SmsNotificationTextBuilder>().InCallScope();
Bind <ISmsSender>().To <SmsSender>().InCallScope();
Bind <IUserSmsNotificationTaskPerformer>().To <UserSmsNotificationTaskPerformer>().InCallScope();
Bind <ISmsNotificationTaskPerformer>().To <SmsNotificationTaskPerformer>().InCallScope();
Bind <IContactAutoRating>().To <ContactAutoRating>().InCallScope();
Bind <IContactIndexCalculator>().To <ContactIndexCalculator>().InCallScope();
// External services
Bind <IService>().ToMethod(c =>
{
var ss = new SpreadsheetsService("Helloy inside");
ss.Credentials = new GDataCredentials("*****@*****.**", "GuT9ThUn");
return(ss);
}).InCallScope();
Bind <IGoogleUrlShortnerService>().To <GoogleUrlShortnerService>().InCallScope();
}
public GDataSpreadSheet()
{
this.Service = Google.Spreadsheets.GetService();
}
public static string strAccessCode = "4/n-puZ4O2y3rRKmE_lhQfgaz12LQnuRdpe_nWLg_8bBc"; //i got this access code 6/21/2016 - not being used right now
// this method authorizes the google api with client id and secret, etc..
public static void AuthorizeRequestGoogleSheetsAPI()
{
try
{
//also check for expiration time parameters.TokenExpiry {6/22/2016 12:12:08 PM} seems to be about one hour before it expires - but, it also has a value called parameters.RefreshToken maybe I can use that to refresh it (?)
if (parameters == null)
{
clsGlobals.boolGoogleHasAccessCode = false;
////////////////////////////////////////////////////////////////////////////
// STEP 1: Configure how to perform OAuth 2.0
////////////////////////////////////////////////////////////////////////////
// TODO: Update the following information with that obtained from
// https://code.google.com/apis/console. After registering
// your application, these will be provided for you.
string CLIENT_ID = "378108272379-90bdt65runbo7im4hial72f8otk3t4d2.apps.googleusercontent.com";
// This is the OAuth 2.0 Client Secret retrieved
// above. Be sure to store this value securely. Leaking this
// value would enable others to act on behalf of your application!
string CLIENT_SECRET = "WIbwPxkAYwrg-4SVhhmGZNIf";
// Space separated list of scopes for which to request access.
//string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";
string SCOPE = "https://spreadsheets.google.com/feeds";
// This is the Redirect URI for installed applications.
// If you are building a web application, you have to set your
// Redirect URI at https://code.google.com/apis/console.
string REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";
////////////////////////////////////////////////////////////////////////////
// STEP 2: Set up the OAuth 2.0 object
////////////////////////////////////////////////////////////////////////////
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = CLIENT_ID;
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = CLIENT_SECRET;
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = REDIRECT_URI;
////////////////////////////////////////////////////////////////////////////
// STEP 3: Get the Authorization URL
////////////////////////////////////////////////////////////////////////////
// Set the scope for this particular service.
parameters.Scope = SCOPE;
// Get the authorization url. The user of your application must visit
// this url in order to authorize with Google. If you are building a
// browser-based application, you can redirect the user to the authorization
// url.
string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
//MessageBox.Show(authorizationUrl);
System.Diagnostics.Process.Start(authorizationUrl);
//Console.WriteLine(authorizationUrl);
//Console.WriteLine("Please visit the URL above to authorize your OAuth "
// + "request token. Once that is complete, type in your access code to "
// + "continue...");
//parameters.AccessCode = Console.ReadLine();
//parameters.AccessCode = Microsoft.VisualBasic.Interaction.InputBox("Title", "Prompt", "Default", 0, 0);
//open up the user input from - use ShowDialog b/c is waits for user to click ok button before continue - aka: async
clsGlobals.UserInputNotes = new frmUserInputNotes();
clsGlobals.UserInputNotes.ShowDialog();
parameters.AccessCode = clsGlobals.strUserInputGoogleAccessCode.ToString().Trim();
////////////////////////////////////////////////////////////////////////////
// STEP 4: Get the Access Token
////////////////////////////////////////////////////////////////////////////
// Once the user authorizes with Google, the request token can be exchanged
// for a long-lived access token. If you are building a browser-based
// application, you should parse the incoming request token from the url and
// set it in OAuthParameters before calling GetAccessToken().
OAuthUtil.GetAccessToken(parameters);
accessToken = parameters.AccessToken;
//Console.WriteLine("OAuth Access Token: " + accessToken);
}
else
{
//set the bool to true so the dialog doesn't show the label and textbox for the access code
clsGlobals.boolGoogleHasAccessCode = true;
//open up the user input from - use ShowDialog b/c is waits for user to click ok button before continue - aka: async
clsGlobals.UserInputNotes = new frmUserInputNotes();
clsGlobals.UserInputNotes.ShowDialog();
}
////////////////////////////////////////////////////////////////////////////
// STEP 5: Make an OAuth authorized request to Google
////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
// Make the request to Google
// See other portions of this guide for code to put here...
}
catch (Exception ex)
{
clsGlobals.logger.Error(Environment.NewLine + "Error Message: " + ex.Message + Environment.NewLine + "Error Source: " + ex.Source + Environment.NewLine + "Error Location:" + ex.StackTrace + Environment.NewLine + "Target Site: " + ex.TargetSite);
MessageBox.Show("Error Message: " + Environment.NewLine + ex.Message + Environment.NewLine + Environment.NewLine +
"Error Source: " + Environment.NewLine + ex.Source + Environment.NewLine + Environment.NewLine +
"Error Location:" + Environment.NewLine + ex.StackTrace,
"UTRANS Editor tool error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public SheetReader(SpreadsheetsService authorisedSpreadsheetsheetsService)
{
_authorisedSpreadsheetsheetsService = authorisedSpreadsheetsheetsService;
}
/// <summary>
/// スプレッドシートを作成する。同じ名前の場合でも気にせず作成される。
/// </summary>
/// <param name="spreadSheetName">スプレッドシート名</param>
/// <returns>失敗したらfalse</returns>
public bool CreateSpreadsheet(string spreadSheetName)
{
// https://developers.google.com/drive/web/mime-types
string req_json = string.Format("{{\"title\": \"{0}\",\"mimeType\": \"application/vnd.google-apps.spreadsheet\"}}", spreadSheetName);
Debug.WriteLine("Trying to create spreadsheet:" + spreadSheetName);
// AccessTokenが切れてるかもしれないので更新
Refresh();
/*
* https://developers.google.com/drive/v2/reference/files/insert
* metadataを送るだけなのでエンドポイントは https://www.googleapis.com/drive/v2/files
*/
HttpWebRequest req = WebRequest.Create("https://www.googleapis.com/drive/v2/files")
as HttpWebRequest;
if (req == null)
{
return(false);
}
req.ContentType = "application/json; charset=UTF-8";
req.Method = "POST";
req.Proxy = _requestFactory.Proxy;
req.Headers.Add("Authorization", string.Format("Bearer {0}", _parameters.AccessToken));
var req_by = Encoding.UTF8.GetBytes(req_json);
req.ContentLength = req_by.Length;
var rs = req.GetRequestStream();
rs.Write(req_by, 0, req_by.Length);
rs.Close();
try
{
HttpWebResponse res = req.GetResponse() as HttpWebResponse;
/*
* 追加したらSpreadsheetsService を作りなおさないとだめ。
* SpreadsheetFeedの再QueryではWorksheetFeedが取得できず例外が飛ぶ
*/
_service = null;
}
catch (WebException wex)
{
System.Diagnostics.Debug.WriteLine("CreateSpreadsheet Ex:" + wex.ToString());
if (wex != null)
{
using (var sr = new System.IO.StreamReader(wex.Response.GetResponseStream()))
{
Debug.WriteLine("wex:" + sr.ReadToEnd());
}
}
return(false);
}
Debug.WriteLine("Spreadsheet created");
return(true);
}
protected void Button1_Click(object sender, EventArgs e)
{
// STEP 1: Configure how to perform OAuth 2.0
////////////////////////////////////////////////////////////////////////////
// TODO: Update the following information with that obtained from
// https://code.google.com/apis/console. After registering
// your application, these will be provided for you.
string CLIENT_ID = "641157605077-rr2l9ma85ps6n5grp031js3pccehfv5u.apps.googleusercontent.com";
// This is the OAuth 2.0 Client Secret retrieved
// above. Be sure to store this value securely. Leaking this
// value would enable others to act on behalf of your application!
string CLIENT_SECRET = "Gq-ekOvi9grFi9K3rznIpoT9";
// Space separated list of scopes for which to request access.
string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";
// This is the Redirect URI for installed applications.
// If you are building a web application, you have to set your
// Redirect URI at https://code.google.com/apis/console.
string REDIRECT_URI = "http://localhost:1918/oauth2callback";
string TOKEN_TYPE = "refresh";
////////////////////////////////////////////////////////////////////////////
// STEP 2: Set up the OAuth 2.0 object
////////////////////////////////////////////////////////////////////////////
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
OAuth2Parameters parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = CLIENT_ID;
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = CLIENT_SECRET;
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = REDIRECT_URI;
////////////////////////////////////////////////////////////////////////////
// STEP 3: Get the Authorization URL
////////////////////////////////////////////////////////////////////////////
// Set the scope for this particular service.
parameters.Scope = SCOPE;
parameters.AccessType = "offline"; // IMPORTANT and was missing in the original
parameters.TokenType = TOKEN_TYPE; // IMPORTANT and was missing in the original
string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
Response.Redirect(authorizationUrl);
Console.WriteLine("Please visit the URL above to authorize your OAuth "
+ "request token. Once that is complete, type in your access code to "
+ "continue...");
parameters.AccessCode = Console.ReadLine();
////////////////////////////////////////////////////////////////////////////
// STEP 4: Get the Access Token
////////////////////////////////////////////////////////////////////////////
// Once the user authorizes with Google, the request token can be exchanged
// for a long-lived access token. If you are building a browser-based
// application, you should parse the incoming request token from the url and
// set it in OAuthParameters before calling GetAccessToken().
OAuthUtil.GetAccessToken(parameters);
string accessToken = parameters.AccessToken;
Console.WriteLine("OAuth Access Token: " + accessToken);
////////////////////////////////////////////////////////////////////////////
// STEP 5: Make an OAuth authorized request to Google
////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
// Make the request to Google
}
public static void Main(string[] args)
{
Console.WriteLine("Working...");
long bukow = 50217665; // bukowskaii's player id
Region bRegion = new Region(Region.Regions.NA);
Console.WriteLine("Getting List of all champions...");
List <ChampionDto> allChampions = ChampionAPI.RetrieveAllChampions(bRegion.ChampionRegion);
Console.WriteLine("Getting masteries for Bukowskaii...");
List <ChampionMasteryDto> myMasteries = ChampionMasteryAPI.GetAllChampionMastery(bRegion.ChampionMasteryRegion, bukow);
Console.WriteLine("Getting Dictionary of champion Ids and champion metadata...");
Dictionary <long, ChampionMetaData> championLookup = StaticDataAPI.GetChampionMetaData(bRegion.ChampionRegion);
Console.WriteLine("Writing info to file...");
List <long> loggedIds = new List <long>();
StringBuilder sb = new StringBuilder();
foreach (ChampionMasteryDto mastery in myMasteries)
{
string champName = championLookup[mastery.ChampionId].Name;
string champTitle = championLookup[mastery.ChampionId].Title;
sb.AppendLine(champName + " -- " + champTitle + "," +
mastery.ChampionId + "," +
mastery.ChestGranted + "," +
mastery.TokensEarned + "," +
UnixTimeStampToDateTime(mastery.LastPlayTime) + "," +
mastery.ChampionLevel + "," +
mastery.ChampionPoints);
loggedIds.Add(mastery.ChampionId);
}
foreach (ChampionDto champion in allChampions)
{
if (!loggedIds.Contains(champion.Id))
{
string champName = championLookup[champion.Id].Name;
string champTitle = championLookup[champion.Id].Title;
sb.AppendLine(champName + " - " + champTitle + "," +
champion.Id + "," +
"False," +
"0," +
"0," +
"0," +
"0");
}
}
//File.WriteAllText(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "LeagueData.csv"), sb.ToString());
//Console.WriteLine("Done! -- Press any key to exit.");
//Thread.Sleep(100);
//Console.ReadKey(false);
//Thread.Sleep(100);
const string ClientId = "376250057781-pud8thtu2qrku59p4jkusf5o97d0nc1e.apps.googleusercontent.com";
string ClientSecret = "WAIKheO-M3YQ6RVlDrK0DC8i";
string scope = "https://spreadsheets.google.com/feeds";
string redirect = "urn:ietf:wg:oauth:2.0:oob";
OAuth2Parameters parameters = new OAuth2Parameters();
parameters.ClientId = ClientId;
parameters.ClientSecret = ClientSecret;
parameters.RedirectUri = redirect;
parameters.Scope = scope;
string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
Process.Start(authorizationUrl);
Console.WriteLine("Paste your authorization code:");
parameters.AccessCode = Console.ReadLine();
//parameters.AccessCode = "4/6op1327wcvTW8eMwFm4D4ZETIS2JCDSas8oifd1OD9M";
OAuthUtil.GetAccessToken(parameters);
string accessToken = parameters.AccessToken;
Console.WriteLine("OAuthAccessToken: " + accessToken);
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "LoL Crate Parse", parameters);
SpreadsheetsService service = new SpreadsheetsService("LoL Crate Parse");
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count > 0)
{
SpreadsheetEntry selectedEntry = new SpreadsheetEntry();
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == "LoL Crates")
{
selectedEntry = entry;
}
}
WorksheetFeed wFeed = selectedEntry.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wFeed.Entries[0];
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
int entries = listFeed.Entries.Count;
int remaining = 0;
ListEntry existingRow;
do
{
existingRow = (ListEntry)listFeed.Entries[0];
existingRow.Delete();
listFeed = service.Query(listQuery);
remaining++;
Console.WriteLine(string.Format("Deleting {0} of {1} complete", remaining, entries));
} while (listFeed.Entries.Count > 0);
string[] rows = sb.ToString().Trim('\n').Split('\n');
entries = rows.Length;
remaining = 0;
foreach (string row in rows)
{
Console.WriteLine(string.Format("Inserting {0} of {1} complete", remaining, entries));
string[] elements = row.Split(',');
ListEntry toAdd = new ListEntry();
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "champion", Value = elements[0]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "id", Value = elements[1]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "chest", Value = elements[2]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "tokensearned", Value = elements[3]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "lastplayed", Value = elements[4]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "champlevel", Value = elements[5]
});
toAdd.Elements.Add(new ListEntry.Custom()
{
LocalName = "masterypoints", Value = elements[6]
});
service.Insert(listFeed, toAdd);
remaining++;
}
}
Console.WriteLine("Done! -- Press any key to exit.");
Thread.Sleep(100);
Console.ReadKey(false);
}
public override void Load()
{
Bind <InsideContext>().ToMethod(c => new InsideContext()).InRequestScope();
Bind <IRestClient>().ToMethod(c => new RestClient()).InRequestScope();
// security
Bind <IAnonymizedDataHelper>().To <AnonymizedDataHelper>().InRequestScope();
// nassa model access
Bind <IRepository <InsideUser> >().ToMethod(c => new Repository <InsideUser>(context => context.InsideUser))
.InRequestScope();
// Anonymised repositories
Bind <IRepository <Client> >().To <AnonymisedClientRepository>().InRequestScope();
Bind <ISerpRankingRepository>().To <AnonymisedSerpRepository>().InRequestScope();
Bind <IRepository <SerpRanking> >().ToMethod(c => new Repository <SerpRanking>(context => context.SerpRanking)).InRequestScope();
/* Bind<IRepository<Admin>>().ToMethod(c => new Repository<Admin>(context => context.Admin)).InRequestScope();*/
Bind <IRepository <InsideModel.Models.Task> >().ToMethod(c => new Repository <InsideModel.Models.Task>(context => context.Task)).InRequestScope();
Bind <IRepository <Contact> >().To <AnonymisedContactRepositorySirius>().InRequestScope();
Bind <IRepository <ContactInteraction> >().ToMethod(c => new Repository <ContactInteraction>(context => context.LeadInteraction)).InRequestScope();
Bind <IRepository <ContactProperty> >().ToMethod(c => new Repository <ContactProperty>(context => context.LeadProperty)).InRequestScope();
Bind <IRepository <Budget> >().ToMethod(c => new Repository <Budget>(context => context.Budget)).InRequestScope();
Bind <IRepository <Label> >().ToMethod(c => new Repository <Label>(context => context.Label)).InRequestScope();
Bind <IRepository <Token> >().ToMethod(c => new Repository <Token>(context => context.Token)).InRequestScope();
Bind <IRepository <Tag> >().ToMethod(c => new Repository <Tag>(context => context.Tag)).InRequestScope();
Bind <IRepository <ExternalToken> >().ToMethod(c => new Repository <ExternalToken>(context => context.ExternalToken)).InRequestScope();
Bind <ITaskManager>().To <TaskManager>().InRequestScope();
Bind <IRepository <InsideRole> >().ToMethod(c => new Repository <InsideRole>(context => context.Role)).InRequestScope();
Bind <IRepository <UserLogins> >().ToMethod(c => new Repository <UserLogins>(context => context.UserLogins)).InRequestScope();
Bind <IUserStore <InsideUser> >().To <InsideModel.Models.Identity.UserStore <InsideUser> >().InRequestScope();
Bind <IRoleStore <InsideRole> >().To <InsideModel.Models.Identity.RoleStore <InsideRole> >().InRequestScope();
Bind <InsideUserManager>().To <InsideUserManager>().InRequestScope();
Bind <IIdentityMembershipProvider>().To <AspIdentityMembership>().InRequestScope();
// External data
Bind <IExternalDataProvider>().To <ExternalDataProvider>().InRequestScope();
// Data Services
Bind <IDownloadManager>().To <DownloadManager>().InRequestScope();
Bind <IServerTime>().To <ServerTime>().InRequestScope();
Bind <IGoogleAuthentication>().To <GoogleAuthentication>().InRequestScope();
Bind <IGoogleAnalyticsApi>().To <GoogleAnalyticsApi>().InRequestScope();
Bind <IContactService>().To <ContactService>().InRequestScope();
Bind <IJsonConverter>().To <JsonUtcConverter>().InRequestScope();
Bind <IAccessTokenProvider>().To <AccessTokenProvider>().InRequestScope();
Bind <IProductService>().To <ProductService>().InRequestScope();
Bind <IEmailSender>().To <MandrilEmailSender>();
Bind <IContactAutoRating>().To <ContactAutoRating>().InRequestScope();
Bind <IContactIndexCalculator>().To <ContactIndexCalculator>().InRequestScope();
// task binding
Bind <ITaskQueueStorage>().To <AzureQueueTaskQueue>().InRequestScope();
Bind <IService>().ToMethod(c =>
{
var ss = new SpreadsheetsService("Helloy inside");
ss.Credentials = new GDataCredentials("*****@*****.**", "GuT9ThUn");
return(ss);
}).InRequestScope();
}
static void Main(string[] args)
{
//Get credentials
Console.Write("Enter your username:"******"Enter your password:"******"my-service");
service.setUserCredentials(user, password);
ListFeed listFeed = null;
SpreadsheetFeed feed = null;
IProgress <string> p = new Progress <string>(Console.WriteLine);
//Get list of spreadsheets
var getSheets = Task.Run(() =>
{
p.Report("Getting all your Google spreadsheets...");
var query = new SpreadsheetQuery();
feed = service.Query(query);
});
getSheets.Wait();
//Show list of spreadsheets...
foreach (SpreadsheetEntry entry in feed.Entries.OrderBy(x => x.Title.Text))
{
Console.WriteLine(entry.Title.Text);
}
Console.WriteLine("Which spreadsheet would you like to see the contents of?");
var title = Console.ReadLine();
//Get list of spreadsheets
var getInfo = Task.Run(() =>
{
p.Report("Reading rows from spreadsheet");
var query = new SpreadsheetQuery();
query.Title = title;
feed = service.Query(query);
var spreadsheet = feed.Entries.FirstOrDefault() as SpreadsheetEntry;
var wsFeed = spreadsheet.Worksheets;
var worksheet = wsFeed.Entries.FirstOrDefault() as WorksheetEntry;
//Define the URL to request the list feed of the worksheet
var listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
p.Report(string.Format("Spreadsheet table link: {0}", listFeedLink.HRef));
//Rows in spreadsheet
var listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = service.Query(listQuery);
});
getInfo.Wait();
//Iterate through the rows...
foreach (ListEntry row in listFeed.Entries)
{
foreach (ListEntry.Custom element in row.Elements)
{
Console.WriteLine(element.Value);
}
}
//NOTE: google docs always treats the first row as the header row...
Console.WriteLine("Type a new value for the first cell in the first column:");
var newValue = Console.ReadLine();
Console.WriteLine("Updating row data...");
var updateRow = (ListEntry)listFeed.Entries[0];
ListEntry.Custom updateMe = updateRow.Elements[0];
updateMe.Value = newValue;
updateRow.Update();
Console.Read();
}
public void method1()
{
OAuth2Parameters parameters = (OAuth2Parameters)Session["para"];
if (!string.IsNullOrEmpty(Request.QueryString["Code"]))
{
parameters.AccessCode = Request.QueryString["Code"].ToString();// = Console.ReadLine();
OAuthUtil.GetAccessToken(parameters);
//
//parameters.AccessToken= credential.Token.AccessToken;
//
string accessToken = parameters.AccessToken;
Console.WriteLine("OAuth Access Token: " + accessToken);
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
//foreach (SpreadsheetEntry entry in feed.Entries)
//{
// Console.WriteLine(entry.Title.Text);
//}
//SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
if (feed.Entries.Where(x => x.Title.Text == "testSS").First() != null)
{
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries.Where(x => x.Title.Text == "testSS").First();
//SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.CreateFeedEntry();
//Console.WriteLine(spreadsheet.Title.Text);
//AtomTextConstruct at = new AtomTextConstruct(AtomTextConstructElementType.Title);
//at.Text = "test";
//spreadsheet.Title = at;
// spreadsheet.Worksheets.AddExtension(requestFactory);
var t = (Home.JSONClass)Session["UserDataGet"];
List <object> lists = new List <object>()
{
t.email, t.name, t.given_name, t.family_name, t.picture, t.Gender, t.locale
};
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
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);
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = service.Query(cellQuery);
CellEntry cellEntry = new CellEntry(1, 1, "email");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 2, "name");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 3, "givenname");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 4, "familyname");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 5, "picture");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 6, "gender");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 7, "locale");
cellFeed.Insert(cellEntry);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "email", Value = t.email
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "name", Value = t.name
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "givenname", Value = t.given_name
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "familyname", Value = t.family_name
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "picture", Value = t.picture
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "gender", Value = t.Gender
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = "locale", Value = t.locale
});
// Send the new row to the API for insertion.
service.Insert(listFeed, row);
}
Response.Redirect("Home.aspx?access_token=" + Session["access_token"].ToString());
}
}
public GoogleDocGenerator(SpreadsheetsService service)
{
Service = service;
}
public void CreateScript()
{
GSheetSettings setting = GSheetUtility.GetSettings();
if (setting == null)
{
return;
}
SpreadsheetsService service = setting.GetService();
WorksheetEntry worksheet = setting.GetWorkSheet(service, manager.SpreadSheetName, manager.WorkSheetName);
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = service.Query(cellQuery);
string fieldFormat = setting.FieldTemplate.text;
StringBuilder sb = new StringBuilder();
// Iterate through each cell, printing its value.
foreach (CellEntry cell in cellFeed.Entries)
{
if (cell.Row > 1)
{
break;
}
string fieldType = "string";
if (cell.Value[0] == 'n')
{
fieldType = "int";
}
else if (cell.Value[0] == 'f')
{
fieldType = "float";
}
else if (cell.Value[0] == '_')
{
continue;
}
string fieldScript = fieldFormat.Replace("{FieldName}", cell.Value).Replace("{LowerCaseFieldName}", cell.Value.ToLower()).Replace("{FieldType}", fieldType);
sb.Append(fieldScript);
}
string dataFormat = setting.DataTemplate.text;
string dataScript = dataFormat.Replace("{WorkSheetName}", manager.WorkSheetName).Replace("{FieldList}", sb.ToString());
StringBuilder dataPathSB = new StringBuilder(setting.ScriptPath);
if (setting.ScriptPath[setting.ScriptPath.Length - 1] != '/')
{
dataPathSB.Append("/");
}
dataPathSB.Append(manager.WorkSheetName);
dataPathSB.Append("Data.cs");
System.IO.File.WriteAllText(dataPathSB.ToString(), dataScript);
string editorFormat = setting.DataEditorTemplate.text;
string editorScript = editorFormat.Replace("{WorkSheetName}", manager.WorkSheetName);
StringBuilder editorPathSB = new StringBuilder(setting.EditorScriptPath);
if (setting.EditorScriptPath [setting.EditorScriptPath.Length - 1] != '/')
{
editorPathSB.Append("/");
}
editorPathSB.Append(manager.WorkSheetName);
editorPathSB.Append("DataEditor.cs");
System.IO.File.WriteAllText(editorPathSB.ToString(), editorScript);
AssetDatabase.Refresh();
}
// Methods
public void Start(string[] args)
{
// Get credential
var credential = AuthorizationManager <ServiceCredential> .GetCredential(
new ServiceAccountProvider(),
ApiKeyPath,
ApplicationScopes);
// Create request
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + ((ServiceAccountCredential)credential).Token.AccessToken);
// Create service using the request
var service = new SpreadsheetsService(null)
{
RequestFactory = requestFactory
};
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
string docKey = "1dAa5tHdqkq2brQ5pfoFjiv1GkVxge52bn99Oz2AgrQw";
string gDocsURL = "https://spreadsheets.google.com/feeds/spreadsheets/private/full/{0}";
string docURL = String.Format(gDocsURL, docKey);
SpreadsheetQuery query = new SpreadsheetQuery()
{
Uri = new Uri(docURL)
};
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
Console.WriteLine("There are no sheets");
}
SpreadsheetEntry spreadsheet = feed.Entries.SingleOrDefault() as SpreadsheetEntry;
var worksheet = spreadsheet.Worksheets.Entries.SingleOrDefault(e => (e as WorksheetEntry).Title.Text == "Data") as WorksheetEntry;
// 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());
listQuery.SpreadsheetQuery = "type=Sales";
ListFeed listFeed = service.Query(listQuery);
Console.WriteLine(listFeed.Entries.Count);
return;
// Iterate through each row, printing its cell values.
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
Console.WriteLine(row.Title.Text);
// Iterate over the remaining columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements)
{
Console.WriteLine(element.Value);
}
}
return;
// Iterate through all of the spreadsheets returned
foreach (SpreadsheetEntry sheet in feed.Entries)
{
// Print the title of this spreadsheet to the screen
Console.WriteLine(sheet.Title.Text);
// Make a request to the API to fetch information about all
// worksheets in the spreadsheet.
var wsFeed = sheet.Worksheets;
// Iterate through each worksheet in the spreadsheet.
foreach (WorksheetEntry entry in wsFeed.Entries)
{
// Get the worksheet's title, row count, and column count.
string title = entry.Title.Text;
var rowCount = entry.Rows;
var colCount = entry.Cols;
// Print the fetched information to the screen for this worksheet.
Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
//// Create a local representation of the new worksheet.
//WorksheetEntry worksheet = new WorksheetEntry();
//worksheet.Title.Text = "New Worksheet";
//worksheet.Cols = 10;
//worksheet.Rows = 20;
//// Send the local representation of the worksheet to the API for
//// creation. The URL to use here is the worksheet feed URL of our
//// spreadsheet.
//WorksheetFeed NewwsFeed = sheet.Worksheets;
//service.Insert(NewwsFeed, worksheet);
}
}
}
/// <summary>
/// Submit button handler. Creates file contents and adds to Google Drive.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void SubmitGoogle(object sender, EventArgs e)
{
try
{
string newDocumentName = NewDocument.Text;
string selectedUri = "";
bool isSheet = false;
// check for valid user selection first
if (SheetTree.SelectedNode == null)
{
selectedUri = "root";
}
else
{
selectedUri = SheetTree.SelectedNode.Value;
if (String.IsNullOrWhiteSpace(selectedUri))
{
throw new Exception("Server exception: code 4");
}
isSheet = (selectedUri.Contains("http://") || selectedUri.Contains("https://"));
}
string submitData = SubmitDataStr;
string meetingName = MeetingNameDate;
if (String.IsNullOrWhiteSpace(submitData) ||
(String.IsNullOrWhiteSpace(newDocumentName) && String.IsNullOrWhiteSpace(selectedUri)) ||
(String.IsNullOrWhiteSpace(newDocumentName) && !isSheet))
{
throw new Exception("Missing data, document name or selected document/folder.");
}
// Make the Auth request to Google
SpreadsheetsService sheetService = GoogleOAuth2.GoogleAuthSheets(Page.Request);
if (sheetService == null)
{
throw new Exception("Google authentication not acquired. Try logging off and in again.");
}
// Make the Auth request to Google
DriveService driveService = GoogleOAuth2.GoogleAuthDrive(Page.Request);
if (driveService == null)
{
throw new Exception("Google authentication not acquired. Try logging off and in again.");
}
// create file contents
List <CheckinEntry> entries = GoogleDriveHelpers.CreateFileContent(submitData);
if (entries == null)
{
throw new Exception("Server exception: code 5");
}
// create new spreadsheet if need be
if (!String.IsNullOrWhiteSpace(newDocumentName) && !isSheet)
{
GDrive.File newFile = GoogleDriveHelpers.CreateFile(driveService, newDocumentName, selectedUri);
if (newFile == null)
{
throw new Exception("Server exception: code 6");
}
// get spreadsheet feed
List <GoogleSheet> sheetList = GoogleDriveHelpers.GoogleRetrieveAllSheets(sheetService);
selectedUri = "";
if (sheetList != null && sheetList.Count > 0)
{
selectedUri = (from feed in sheetList where feed.Id.Equals(newFile.Id) select feed.FeedUri).First();
}
}
// create new worksheet in spreadsheet
WorksheetEntry worksheet = GoogleDriveHelpers.CreateWorksheet(sheetService, selectedUri, meetingName);
if (worksheet == null)
{
throw new Exception("Server exception: code 7");
}
// Add content to worksheet
if (!GoogleDriveHelpers.AddContentToWorksheet(sheetService, worksheet, entries))
{
throw new Exception("Server exception: code 8");
}
// output page redirect
string script = string.Format("alert('Data added successfully.');");
Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", script, true);
}
catch (Exception ex)
{
string script = string.Format("alert('Request not logged: {0}');", ex.Message);
Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", script, true);
}
}
private void GetPlayers(SpreadsheetsService service, SpreadsheetEntry entry, WorksheetFeed wsFeed)
{
int cellID = 0;
Players = new List <Player>();
List <CellFeed> cellFeeds = DoCellQuery(service, wsFeed, 2, 2, 2);
for (int i = 0; i < cellFeeds.Count; i++)
{
CellFeed currentCellFeed = cellFeeds[i];
if (currentCellFeed != null)
{
foreach (CellEntry cell in currentCellFeed.Entries)
{
string playerList = cell.InputValue;
string[] playerNames = null;
if (!String.IsNullOrWhiteSpace(playerList))
{
playerNames = playerList.Split(',').Select(sValue => sValue.Trim()).ToArray();
}
else
{
break;
}
foreach (string playerName in playerNames)
{
if (!Players.Exists(player => player.Name == playerName))
{
Players.Add(new Player()
{
Name = playerName
});
}
Player myPlayer = new Player();
myPlayer = Players.Find(player => player.Name == playerName);
myPlayer.GamesPlayed += 1;
foreach (GameEvent gameEvent in GamesPlayed)
{
if (gameEvent.Name == wsFeed.Entries[i].Title.Text && gameEvent.ID == cellID)
{
if (gameEvent.Participants == null)
{
gameEvent.Participants = new List <Player>();
}
gameEvent.Participants.Add(myPlayer);
}
}
}
cellID++;
}
}
}
GetWins(service, entry, wsFeed, Players);
}
internal Spreadsheet(SpreadsheetsService service, SpreadsheetEntry entry)
{
_entry = entry;
_service = service;
}
private void GetLosses(SpreadsheetsService service, SpreadsheetEntry entry, WorksheetFeed wsFeed, List <Player> Players)
{
int cellID = 0;
List <CellFeed> cellFeeds = DoCellQuery(service, wsFeed, 2, 4, 4);
for (int i = 0; i < cellFeeds.Count; i++)
{
CellFeed currentCellFeed = cellFeeds[i];
if (currentCellFeed != null)
{
foreach (CellEntry cell in currentCellFeed.Entries)
{
string playerList = cell.InputValue;
string[] playerNames = null;
if (!String.IsNullOrWhiteSpace(playerList))
{
playerNames = playerList.Split(',').Select(sValue => sValue.Trim()).ToArray();
}
else
{
break;
}
foreach (string playerName in playerNames)
{
string placement = null;
string playerNameTrimmed = null;
if (playerName.Contains("("))
{
placement = Regex.Match(playerName, @"\(([^)]*)\)").Groups[1].Value;
playerNameTrimmed = playerName.Remove(playerName.Length - 4);
}
else
{
playerNameTrimmed = playerName;
}
if (Players.Exists(player => player.Name == playerNameTrimmed))
{
Player myPlayer = new Player();
myPlayer = Players.Find(player => player.Name == playerNameTrimmed);
myPlayer.Losses += 1;
if (placement != null)
{
if (myPlayer.Placement == null)
{
myPlayer.Placement = new List <int>();
}
myPlayer.Placement.Add(Int32.Parse(placement));
}
foreach (GameEvent gameEvent in GamesPlayed)
{
if (gameEvent.Name == wsFeed.Entries[i].Title.Text && gameEvent.ID == cellID)
{
if (gameEvent.Losers == null)
{
gameEvent.Losers = new List <Player>();
}
gameEvent.Losers.Add(myPlayer);
}
}
}
}
cellID++;
}
}
}
foreach (Player player in Players)
{
player.AveragePlacement = player.GetAveragePlacement();
}
}
// TODO: all callers of this method should handle exceptions (InvalidCredentialsException etc.)
public static List <List <string> > GetSpreadsheet(SpreadsheetsService zSpreadsheetService, string sSpreadsheetName, string sSheetName)
{
var listLines = new List <List <string> >();
// get all spreadsheets
var query = new SpreadsheetQuery
{
// only ask for the spreadsheet by the given name
Title = sSpreadsheetName
};
var feed = zSpreadsheetService.Query(query);
var bFoundSpreadsheet = false;
foreach (var entry in feed.Entries)
{
if (entry.Title.Text != sSpreadsheetName)
{
continue;
}
bFoundSpreadsheet = true;
Logger.AddLogLine("Google: Found spreadsheet: " + sSpreadsheetName);
var link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString())
{
Title = sSheetName
};
var wsfeed = zSpreadsheetService.Query(wsquery);
var bFoundSheet = false;
foreach (var worksheet in wsfeed.Entries)
{
//System.Diagnostics.Trace.WriteLine(worksheet.Title.Text);
if (worksheet.Title.Text != sSheetName)
{
continue;
}
bFoundSheet = true;
Logger.AddLogLine("Google: Found sheet: " + sSheetName);
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cquery = new CellQuery(cellFeedLink.HRef.ToString());
var cfeed = zSpreadsheetService.Query(cquery);
//System.Diagnostics.Trace.WriteLine("Cells in this worksheet:");
uint uRow = 1;
uint uCol = 1;
var listRow = new List <string>();
foreach (var curCell in cfeed.Entries.OfType <CellEntry>())
{
// NOTE: This completely ignores blank lines in the spreadsheet
if (uRow != curCell.Cell.Row)
{
// new row, flush the previous
listLines.Add(listRow);
listRow = new List <string>();
uRow = curCell.Cell.Row;
uCol = 1;
}
// fill in any missing columns with empty strings
if (uCol != curCell.Cell.Column)
{
while (uCol < curCell.Cell.Column)
{
listRow.Add(string.Empty);
uCol++;
}
}
listRow.Add(curCell.Cell.Value ?? string.Empty);
uCol++;
}
// always flush the last line
listLines.Add(listRow);
}
if (bFoundSheet)
{
break;
}
Logger.AddLogLine("Google: Failed to find sheet: " + sSheetName);
}
if (!bFoundSpreadsheet)
{
Logger.AddLogLine("Google: Failed to find spreadsheet: " + sSpreadsheetName);
}
processNewLines(listLines);
return(listLines);
}
public static void Main(string[] args)
{
// Check if the argument is to update config files
if (args.Length == 4 && args[0] == "-u")
{
Console.WriteLine("Starting modime config updater");
ModimeConfigurationGenerator.UpdateWithSpreadsheet(args[1], args[2],
args[3]);
Console.WriteLine("Done");
return;
}
// Get and create the output directory.
string outDir;
if (args.Length > 1)
{
outDir = args[0];
}
else
{
outDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
outDir = Path.Combine(outDir, "output");
}
if (!Directory.Exists(outDir))
{
Directory.CreateDirectory(outDir);
}
// Get the services and start working.
var service = new SpreadsheetsService();
// Ask for file names.
while (true)
{
// Get the requested SpreadhSheet title.
Console.WriteLine();
Console.Write("Type the file title (\"exit\" to quit)> ");
string title = Console.ReadLine();
if (title == "exit")
{
break;
}
// Search for it
var spreadsheets = service.SearchSpreadsheets(title, 0).ToArray();
var targetSheet = SelectSpreadsheet(spreadsheets);
if (targetSheet == null)
{
continue;
}
// Check that it's a valid spreadsheet by comparing the name
// All the converted XML are written into the first worksheet.
var worksheet = targetSheet[0];
var name = worksheet[0, 0]; // Written into the first cell.
if (string.IsNullOrEmpty(name) || !name.StartsWith("Filename "))
{
Console.WriteLine("Invalid spreadsheet name. Please verify it.");
continue;
}
name = name.Substring("Filename ".Length);
// Convert.
Console.WriteLine("Converting {0}...", name);
var xml = new WorksheetToXml(worksheet).Convert();
// If it's a subtitle script, the root tag has an attribute with name
if (xml.Root.Name == "Subtitle")
{
xml.Root.SetAttributeValue("Name", name);
}
xml.Save(Path.Combine(outDir, name + ".xml"));
}
}
public formatSheet(string Username, string Password, string Sheet, string Worksheet)
{
dt = new DataTable();
try
{
SpreadsheetsService service = new
SpreadsheetsService("Anachrophobe");
service.setUserCredentials(Username, Password);
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = Sheet;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count != 1)
{
return;
}
AtomLink link =
feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel,
null);
WorksheetQuery worksheetQuery = new
WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = Worksheet;
WorksheetFeed worksheetFeed =
service.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1)
{
return;
}
WorksheetEntry worksheet =
(WorksheetEntry)worksheetFeed.Entries[0];
AtomLink listFeedLink =
worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new
ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
// define the table's schema
dt.Columns.Add(new DataColumn("A", typeof(string)));
dt.Columns.Add(new DataColumn("B", typeof(string)));
dt.Columns.Add(new DataColumn("C", typeof(string)));
dt.Columns.Add(new DataColumn("D", typeof(string)));
foreach (ListEntry worksheetRow in listFeed.Entries)
{
string station = string.Empty;
string scanData =
worksheetRow.Elements[0].Value.ToString();
string scanData1 =
worksheetRow.Elements[1].Value.ToString();
string[] arSD = new string[100];
DataRow dr = dt.NewRow();
dr["A"] = scanData;
dr["B"] = scanData1;
dt.Rows.Add(dr);
}
//dgwTabla.DataSource = dt;
}
catch (Exception ex)
{
}
}
//private static string currentSpreadsheet; //not used //TODO determine if this is a bug
/// <summary>
/// default constructor for client login.
/// </summary>
/// <param name="user">the username to user</param>
/// <param name="password">the users password</param>
/// <returns></returns>
public Application(string user, string password)
{
Application.service = new SpreadsheetsService(Application.Name);
Application.service.setUserCredentials(user, password);
}
