/// <summary>
/// カラム数が変わっていたら表を拡張してカラム名を更新
/// </summary>
/// <param name="wsEntry"></param>
/// <param name="columns"></param>
/// <returns></returns>
protected WorksheetEntry CheckColumns(WorksheetEntry wsEntry, string[] columns)
{
if (columns.Length > wsEntry.Cols)
{
wsEntry.Cols = (uint)columns.Length;
WorksheetEntry newEntry = (WorksheetEntry)wsEntry.Update();
/*
* http://stackoverflow.com/questions/22719170/updating-cell-in-google-spreadsheets-returns-error-missing-resource-version-id
* http://stackoverflow.com/questions/20841411/google-spreadsheets-api-c-sharp-missing-resource-version-id-on-batch-update/23438381
*
* ETag:*をするとか、そもそもBatchよりPublishの方が分かりやすい、とか。
*/
CellFeed cellFeed = wsEntry.QueryCellFeed();
uint col = 1;
foreach (var it in columns)
{
CellEntry batchEntry = cellFeed[1, col++];
batchEntry.InputValue = it;
batchEntry.Etag = "*";
}
cellFeed.Publish();
return(newEntry);
}
return(wsEntry);
}
/// <summary>
/// 指定したシートにヘッダを追加
/// </summary>
/// <param name="sheet">ヘッダを追加するワークシート</param>
/// <param name="headers">ヘッダ</param>
/// <returns></returns>
bool AddNewHeader(WorksheetEntry sheet, params string[] headers)
{
Debug.WriteLine("AddHeader num=" + headers.Count().ToString());
//現状、どっちでも動く
// CellQuery cellQuery = new CellQuery(sheet.CellFeedLink);
// CellFeed cellFeed = _wrapper.Query(cellQuery);
CellFeed cellFeed = sheet.QueryCellFeed();
uint column = 1;
//InsertはBatch処理できないらしい。
foreach (var it in headers)
{
Debug.WriteLine(string.Format("AddHeader name:{0} column:{1}", it, column));
var entry = new CellEntry(1, column, it);
column++;
cellFeed.Insert(entry);
}
return(true);
}
public override void Log(LoggingEntery LE)
{
IniFile Ini = new IniFile(Path.Combine(Environment.CurrentDirectory, "GoogleDocs.ini"));
// nir start
////////////////////////////////////////////////////////////////////////////
// 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 = "339569043085-6k0io9kdubi7a3g3jes4m76t614fkccr.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 = "wWC4Wcb12RbQg4YuGWJtkh4j";
// 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.
//tring 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.
OAuth2Parameters parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = Ini.IniReadValue(ConnectSection, "ClientID");
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = Ini.IniReadValue(ConnectSection, "ClientSecret");
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = Ini.IniReadValue(ConnectSection, "RedirectURI");
// Set your refresh token
parameters.RefreshToken = Ini.IniReadValue(ConnectSection, "RefreshToken");
parameters.AccessToken = Ini.IniReadValue(ConnectSection, "LastAccessToken");
// Set the scope for this particular service.
parameters.Scope = Ini.IniReadValue(ConnectSection, "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);
//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...");
////////////////////////////////////////////////////////////////////////////
// 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.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection, "LastAccessToken", 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, "OctoTipPlus", parameters);
SpreadsheetsService myService = new SpreadsheetsService("OctoTipPlus");
myService.RequestFactory = requestFactory;
// nir end
string User = Ini.IniReadValue("UserLogin", "User");
string Password = Ini.IniReadValue("UserLogin", "Password");
// SpreadsheetsService myService = new SpreadsheetsService("MySpreadsheetIntegration-v1");
//myService.setUserCredentials(User,Password);
SpreadsheetQuery Squery = new SpreadsheetQuery();
string Sender = LE.Sender;
Squery.Title = Sender;
Squery.Exact = true;
SpreadsheetFeed Sfeed;
try
{
Sfeed = myService.Query(Squery);
}
catch (Google.GData.Client.InvalidCredentialsException e)
{
throw(new Exception(string.Format("Credentials error in google acount for user:{0}", User), e));
}
if (Sfeed.Entries.Count == 0)
{
//DriveService service1 = new DriveService();
//service.SetAuthenticationToken(parameters.AccessToken);//.setUserCredentials(User,Password);
//Google.GData.Client.GOAuth2RequestFactory requestf = new Google.GData.Client.GOAuth2RequestFactory(null, "OctoTipPlus",parameters);
OAuthUtil.RefreshAccessToken(parameters); //parameters.AccessToken;
Ini.IniWriteValue(ConnectSection, "LastAccessToken", parameters.AccessToken);
Google.GData.Documents.DocumentsService service = new Google.GData.Documents.DocumentsService("OctoTipPlus");
GOAuth2RequestFactory requestFactory2 =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
service.RequestFactory = requestFactory2;
//service.RequestFactory=requestf;
// Instantiate a DocumentEntry object to be inserted.
Google.GData.Documents.DocumentEntry entry = new Google.GData.Documents.DocumentEntry();
// Set the document title
entry.Title.Text = LE.Sender;
// Add the document category
entry.Categories.Add(Google.GData.Documents.DocumentEntry.SPREADSHEET_CATEGORY);
// Make a request to the API and create the document.
Google.GData.Documents.DocumentEntry newEntry = service.Insert(
Google.GData.Documents.DocumentsListQuery.documentsBaseUri, entry);
Squery = new SpreadsheetQuery();
Squery.Title = Sender;
Squery.Exact = true;
Sfeed = myService.Query(Squery);
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)Sfeed.Entries[0];
WorksheetEntry ProtocolWorksheetEntry = null;
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery Wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed Wfeed = myService.Query(Wquery);
foreach (WorksheetEntry worksheet in Wfeed.Entries)
{
if (worksheet.Title.Text == LE.SubSender)
{
ProtocolWorksheetEntry = worksheet;
}
}
if (ProtocolWorksheetEntry == null)
{
// cteate new worksheet
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = LE.SubSender;
worksheet.Cols = 3;
worksheet.Rows = 5;
// 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 wsFeed = spreadsheet.Worksheets;
ProtocolWorksheetEntry = myService.Insert(wsFeed, worksheet);
CellFeed cellFeed = ProtocolWorksheetEntry.QueryCellFeed();
CellEntry cellEntry = new CellEntry(1, 1, DateHeader);
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 2, MessageHeader);
cellFeed.Insert(cellEntry);
}
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = ProtocolWorksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = myService.Query(listQuery);
string Message = string.Format("{0}\n{1}", LE.Title, LE.Message);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom()
{
LocalName = DateHeader, Value = DateTime.Now.ToString()
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = MessageHeader, Value = Message
});
// Send the new row to the API for insertion.
myService.Insert(listFeed, row);
}
public void InsertWorksheetTest()
{
Tracing.TraceMsg("Entering InsertWorksheetTest");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetsService service = new SpreadsheetsService(this.ApplicationName);
if (this.userName != null)
{
service.Credentials = new GDataCredentials(this.userName, this.passWord);
}
service.RequestFactory = this.factory;
SpreadsheetFeed feed = service.Query(query);
Assert.IsTrue(feed != null, "Need to have a spreadsheet feed");
Assert.IsTrue(feed.Entries != null && feed.Entries.Count > 0, "Need to have one spreadsheet in there");
SpreadsheetEntry entry = feed.Entries[0] as SpreadsheetEntry;
string wUri = entry.WorksheetsLink;
Assert.IsTrue(wUri != null, "Need to have a worksheet feed in the spreadsheet entry");
WorksheetFeed sheets = entry.Worksheets;
// kill all but the default before we start
foreach (WorksheetEntry w in sheets.Entries)
{
if (w.Title.Text != "DefaultSheet")
{
w.Delete();
}
}
WorksheetEntry defEntry = sheets.Entries[0] as WorksheetEntry;
Assert.IsTrue(defEntry != null, "There should be one default entry in this account/sheet");
Assert.IsTrue(defEntry.Title.Text == "DefaultSheet", "it should be the default sheet");
CellFeed defCells = defEntry.QueryCellFeed();
Assert.IsTrue(defCells != null, "There should be a cell feed for the worksheet");
CellEntry header = null;
foreach (CellEntry cell in defCells.Entries)
{
if (cell.Title.Text == "A1")
{
cell.Cell.InputValue = string.Empty;
cell.Update();
header = cell;
}
}
if (header != null)
{
header.InputValue = "HeaderA";
header.Update();
}
WorksheetEntry newEntry = sheets.Insert(new WorksheetEntry(10, 20, "New Worksheet"));
Assert.IsTrue(newEntry.ColCount.Count == 20, "Column count should be equal 20");
Assert.IsTrue(newEntry.RowCount.Count == 10, "Row count should be equal 10");
Assert.IsTrue(newEntry.Title.Text == "New Worksheet", "Titles should be identical");
CellFeed cells = newEntry.QueryCellFeed(ReturnEmptyCells.yes);
Assert.IsTrue(cells != null, "There should be a cell feed for the new worksheet");
Assert.IsTrue(cells.Entries.Count == 200, "There should be 200 cells");
for (uint row = 1; row <= 10; row++)
{
for (uint col = 1; col <= 20; col++)
{
cells[row, col].InputValue = "R" + row + "C" + col;
}
}
cells.Publish();
// try to update just one cell
cells[1, 1].InputValue = string.Empty;
cells[1, 1].Update();
for (uint row = 1; row <= 10; row++)
{
for (uint col = 1; col <= 20; col++)
{
cells[row, col].InputValue = string.Empty;
}
}
cells.Publish();
// cleanup the new worksheet at the end
newEntry.Delete();
}