/// <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(); }