筆者目前研究資料寫入 Google Spreadsheet 有以下2種方法,後續還有其他再行分享:
- 更新寫入:指定位置修改,若指定欄位有資料,則覆蓋原位置內容。
- 添加寫入:指定位置修改,若指定欄位有資料,則往下一行列填寫。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Google.Apis.Auth.OAuth2; using Google.Apis.Sheets.v4; using Google.Apis.Sheets.v4.Data; using Google.Apis.Services; using Google.Apis.Util.Store; using System.IO; using System.Threading; using System.Threading.Tasks; namespace WriteGoogleSheet { class Program { static string[] Scopes = { SheetsService.Scope.Spreadsheets }; static string ApplicationName = "Update Google Sheet Data with Google Sheets API v4"; static String spreadsheetId = "1SyfODMfB1t7kpZ-CscOUIXdl6wHoHwYsxIjsbzMfzSk"; static string sheetName = "Test123"; static void Main(string[] args) { var service = OpenSheet(); //每10秒一次寫入時間到 Google Sheet while (true) { UpdateRow(service); System.Threading.Thread.Sleep(10000); } } static SheetsService OpenSheet() { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = Path.Combine (System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), ".credentials/sheets.googleapis.com-dotnet-quickstart.json"); //存儲憑證到credPath credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } //建立一個API服務,設定請求參數 var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); return service; } static void UpdateRow(SheetsService service) { ValueRange rVR; String sRange; int rowNumber = 1; //設定讀取A欄最後一行位置 sRange = String.Format("{0}!A:A", sheetName); SpreadsheetsResource.ValuesResource.GetRequest getRequest = service.Spreadsheets.Values.Get(spreadsheetId, sRange); rVR = getRequest.Execute(); //到Google sheet讀取內容 IList<IList<Object>> values = rVR.Values; //最後一行位置 //寫入新資料 if (values != null && values.Count > 0) rowNumber = values.Count + 1; //添加一行 sRange = String.Format("{0}!A{1}:B{1}", sheetName, rowNumber); //指定寫入位置 //設定寫入 ValueRange valueRange = new ValueRange(); valueRange.Range = sRange; valueRange.MajorDimension = "ROWS";//ROWS或COLUMNS //取得當前時間 DateTime dt = new DateTime(); dt = DateTime.Now; List<object> oblist = new List<object>() { String.Format("{0}", rowNumber), dt.ToString("HH:mm:ss") }; //寫入時間 valueRange.Values = new List<IList<object>> { oblist }; Console.WriteLine("{0}, {1}", oblist[0], oblist[1]); //執行寫入動作 SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, sRange); updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED; UpdateValuesResponse uUVR = updateRequest.Execute(); } } }執行結果:
相關文章:
參考資料:
- Google.Apis.Sheets.v4.SheetsBaseServiceRequest< TResponse > Class Template Reference
- Google.Apis.Sheets.v4.SpreadsheetsResource.ValuesResource Class Reference
- C#.NETでGoogle Sheets API 4を使ってSpreadSheetを読み書きしてみる
- Google Sheets API v4 C# update a cell
- update-google-sheets.cs
- Google Sheets API v4が登場しました