private static void pullSiteData(string siteName, String sheetKey, Queue <string> sheets) { SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(sheetKey); request.IncludeGridData = true; try { Google.Apis.Sheets.v4.Data.Spreadsheet response = request.Execute(); foreach (var sheetName in sheets) { try { Sheet sheet = response.Sheets.FirstOrDefault <Sheet>(s => s.Properties.Title == sheetName); if (sheet == null) { Console.WriteLine("Couldn't find sheet {0} in spreadsheet {1} {2}", sheetName, siteName, sheetKey); continue; } foreach (var row in sheet.Data.First <GridData>().RowData) { Console.Write("{0}\t{1}\t", siteName, sheetName); foreach (var cell in row.Values) { Console.Write("{0}\t", (cell.FormattedValue ?? "").Trim()); } Console.WriteLine(); } } catch (Exception e) { Console.WriteLine("Error processing sheet data for {0}:{1}:{2}={3}", siteName, sheetKey, sheetName, e.Message); }; } } catch (Exception e) { Console.WriteLine("Couldn't get sheet data for {0} on {1} = {2}", siteName, sheetKey, e.GetType()); }; }
//Creates a new sheet with sheetname static void CreateNewSheet(string SheetName) { SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = ApplicationName, }); // TODO: Assign values to desired properties of `requestBody`: Data.Spreadsheet requestBody = new Data.Spreadsheet(); requestBody.Properties = new SpreadsheetProperties(); requestBody.Properties.Title = SheetName; SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody); Data.Spreadsheet response = request.Execute(); string ok = response.SpreadsheetId; //1xt6CqlJgpxW2W8rNZvpT9nVRm--xzyo7dVSNY4qQE5w // TODO: Change code below to process the `response` object: //Console.WriteLine(JsonConvert.SerializeObject(response)); }
private static SheetsService AuthorizeGoogleAppForSheetsService() { UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Credential file saved to: " + credPath); } var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); //Creating the sheet now in Google Sheets after being authorised. Guid obj = Guid.NewGuid(); string sheetName = string.Format("{0} - {1}", "Google Sheets Testing", obj.ToString()); var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet(); myNewSheet.Properties = new SpreadsheetProperties(); myNewSheet.Properties.Title = sheetName; var newSheet = service.Spreadsheets.Create(myNewSheet).Execute(); sheetId = newSheet.SpreadsheetId; return(service); }
//Creates a new Sheet with tabs static void CreateNewSheet(string SheetTitle, IList <Sheet> Sheets) { SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = ApplicationName, }); // TODO: Assign values to desired properties of `requestBody`: Data.Spreadsheet requestBody = new Data.Spreadsheet(); requestBody.Properties = new SpreadsheetProperties(); requestBody.Properties.Title = SheetTitle; requestBody.Sheets = Sheets; SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody); Data.Spreadsheet response = request.Execute(); // TODO: Change code below to process the `response` object: //Console.WriteLine(JsonConvert.SerializeObject(response)); }
/// <summary> /// this will get the index of the sheet's location to be used for GridRange usages,returns -1 if not found /// </summary> /// <param name="SheetName"></param> /// <returns></returns> public static int?GetSheetIdBySheetName(string spreadsheetId, string SheetName) { SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = ApplicationName, }); SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId); // To execute asynchronously in an async method, replace `request.Execute()` as shown: Data.Spreadsheet response = request.Execute(); for (int i = 0; i < response.Sheets.Count; i++) { Sheet tempSheet = response.Sheets[i]; if (tempSheet.Properties.Title == SheetName) { return(tempSheet.Properties.SheetId); } } return(-1); }
static void CreateNewSheet() { SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer { HttpClientInitializer = GetCredential(), ApplicationName = ApplicationName, }); // TODO: Assign values to desired properties of `requestBody`: Data.Spreadsheet requestBody = new Data.Spreadsheet(); //IDs requestBody.SpreadsheetId = "thebakerid"; Sheet sheet = new Sheet(); Sheet sheet2 = new Sheet(); sheet.Properties = new SheetProperties(); sheet2.Properties = new SheetProperties(); sheet.Properties.Title = "test"; sheet.Properties.SheetId = 1; sheet2.Properties.SheetId = 2; sheet.Properties.Title = "test2"; requestBody.Sheets = new List <Sheet>(); requestBody.Sheets.Add(sheet); requestBody.Sheets.Add(sheet2); // SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody); // To execute asynchronously in an async method, replace `request.Execute()` as shown: Data.Spreadsheet response = request.Execute(); // Data.Spreadsheet response = await request.ExecuteAsync(); // TODO: Change code below to process the `response` object: Console.WriteLine(JsonConvert.SerializeObject(response)); }
// set servers data in list public void SetDataInTable(Data.Spreadsheet response, SheetsService service, List <Server> servers) { string spreadSheetId = response.SpreadsheetId; List <int?> sheetsId = new List <int?>(); string currentDate = DateTime.Now.ToString(); foreach (var item in response.Sheets) { sheetsId.Add(item.Properties.SheetId); } for (int i = 0; i < servers.Count; i++) { Server server = servers[i]; int? sheetId = sheetsId[i]; string[,] data = new string[server.dbData.Count, 4]; foreach (string key in server.dbData.Keys) { data.SetValue(server.name, 0, 0); data.SetValue(key, 0, 1); data.SetValue(server.dbData[key], 0, 2); data.SetValue(currentDate, 0, 3); server.dbData.Remove(key); break; } int count = 1; foreach (string key in server.dbData.Keys) { data.SetValue(" ", count, 0); data.SetValue(key, count, 1); data.SetValue(server.dbData[key], count, 2); data.SetValue(" ", count, 3); ++count; } List <Data.Request> requests = new List <Data.Request>(); for (int j = 0; j < data.GetLength(0); j++) { List <Data.CellData> values = new List <Data.CellData>(); for (int k = 0; k < data.GetLength(1); k++) { values.Add(new Data.CellData() { UserEnteredValue = new Data.ExtendedValue() { StringValue = data[j, k] } }); } requests.Add( new Data.Request { UpdateCells = new Data.UpdateCellsRequest() { Start = new Data.GridCoordinate() { SheetId = sheetId, RowIndex = j + 1, ColumnIndex = 0 }, Rows = new List <Data.RowData> { new Data.RowData { Values = values } }, Fields = "userEnteredValue" } }); Data.BatchUpdateSpreadsheetRequest bUpd = new Data.BatchUpdateSpreadsheetRequest { Requests = requests }; service.Spreadsheets.BatchUpdate(bUpd, spreadSheetId).Execute(); } string[] dataBasement = { server.name, "Volume", server.size, currentDate }; List <Data.CellData> valuesB = new List <Data.CellData>(); foreach (string dataB in dataBasement) { valuesB.Add(new Data.CellData() { UserEnteredValue = new Data.ExtendedValue() { StringValue = dataB } }); } requests.Add( new Data.Request { UpdateCells = new Data.UpdateCellsRequest() { Start = new Data.GridCoordinate() { SheetId = sheetId, RowIndex = server.dbData.Count + 1, ColumnIndex = 0 }, Rows = new List <Data.RowData> { new Data.RowData { Values = valuesB } }, Fields = "userEnteredValue" } }); Data.BatchUpdateSpreadsheetRequest bUpsRequest = new Data.BatchUpdateSpreadsheetRequest { Requests = requests }; service.Spreadsheets.BatchUpdate(bUpsRequest, spreadSheetId).Execute(); } }
void CreateTable(string tableName) { if (spreadSheetsService == null) { spreadSheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = applicationName, }); } try { // TODO: Assign values to desired properties of `requestBody`: Google.Apis.Sheets.v4.Data.Spreadsheet requestBody = new Google.Apis.Sheets.v4.Data.Spreadsheet(); requestBody.Properties = new SpreadsheetProperties(); requestBody.Properties.Title = tableName; List <Sheet> sheets = new List <Sheet>(); Sheet mainSheet = new Sheet(); mainSheet.Properties = new SheetProperties(); mainSheet.Properties.Title = "Notesieve"; sheets.Add(mainSheet); requestBody.Sheets = sheets; SpreadsheetsResource.CreateRequest request = spreadSheetsService.Spreadsheets.Create(requestBody); // To execute asynchronously in an async method, replace `request.Execute()` as shown: Google.Apis.Sheets.v4.Data.Spreadsheet response = request.Execute(); // Data.Spreadsheet response = await request.ExecuteAsync(); // TODO: Change code below to process the `response` object: // Console.WriteLine(); if (driveService == null) { driveService = new DriveService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = applicationName, }); } var fileMetadata = new Google.Apis.Drive.v3.Data.File() { Name = jsonFileName, Parents = new List <string>() { "appDataFolder" } }; using (StreamWriter sw = new StreamWriter(jsonFileLocalPath, false, System.Text.Encoding.Default)) { if (gSyncSettings == null) { gSyncSettings = new GSyncSettings(); } gSyncSettings.spreadshetrID = response.SpreadsheetId; gSyncSettings.dataVersion = 0; sw.WriteLine(JsonConvert.SerializeObject(gSyncSettings)); } FilesResource.CreateMediaUpload newRequest; using (var stream = new System.IO.FileStream(jsonFileLocalPath, System.IO.FileMode.Open)) { newRequest = driveService.Files.Create(fileMetadata, stream, "application/json"); newRequest.Fields = "id"; newRequest.Upload(); gSyncSettingFileId = newRequest.ResponseBody.Id; } File.Delete(jsonFileLocalPath); } catch (System.Net.Http.HttpRequestException e) { OnSyncStateChanged?.Invoke(gSyncState.eLostConnection); } }
/// <summary> /// Create spreadsheet Top-10 of populations county /// Title contains datetime of creation in format yyyy-MM-ddTHH.mm.ss.fff /// </summary> /// <param name="spsheedid"></param> /// <param name="spsheeturl"></param> public static void creategooglesheettop10(Icensusapi xco, ref string spsheedid, ref string spsheeturl) { //define exist spreadsheet spreadsheetId = spsheedid; spreadsheetUrl = spsheeturl; //get the top 10 most populous county in the united states List <population> top10 = null; string xfs = xco.getpopulations(); List <population> xpop = helper.getpoplistfromjson(xfs); if (xpop != null) { top10 = xpop.OrderByDescending(x => x.POP).Take(10).ToList(); //10 } if (top10 != null) { foreach (var item in top10) { Console.WriteLine("Get data for " + item.GEONAME); // Estimated Components of Resident Population Change string statbd = xco.getstats_birth_death(item.state, item.county); item.statsbd = helper.getstatbdfromjson(statbd); // Detailed Language Spoken (LANG7) string langs = xco.getstats_language(item.state, item.county); item.languages = helper.getlanglistfromjson(langs); // population estimate by agegroup per State // agegroup "18 years and over" string pop18 = xco.getpopulations18(item.state); population18 p18 = helper.getpop18fromjson(pop18); if (p18 != null) { item.POP18 = p18.POP; } // population estimate by agegroup per State // agegroup 0 - all population string pop00 = xco.getpopulations00(item.state); population18 p00 = helper.getpop18fromjson(pop00); if (p00 != null) { item.POP00 = p00.POP; } } // Columns for general information string[] colNames = new[] { "Name", "Population", "State's population", "State's population\nwith age older\nthan 18 years", "Last update" }; // Columns for Population Change string[] colStatsBD = new[] { "Births in period", "Deaths in period", "Natural increase\nin period", "Birth rate\nin period", "Death rate\nin period", "Period" }; // Columns for Detailed Language string[] colLangs = new[] { "Description", "Population" }; // Define range template string range1 = "{0}!A1:E1"; string range2 = "{0}!A2:E2"; string range3 = "{0}!A{1}"; string range2_2 = "{0}!A{1}:F{2}"; string range4 = "{0}!A{1}:B{2}"; // Google oauth credentials setcredentials(); var service = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, //HttpClientInitializer = sacredential, //ApiKey = apikey, ApplicationName = ApplicationName, }); // Create Spreadsgeet #region if (spreadsheetId == "") { Data.Spreadsheet requestBody = new Data.Spreadsheet(); requestBody.Properties = new SpreadsheetProperties(); requestBody.Properties.Title = "Top-10 of populations county " + DateTime.Now.ToString("yyyy-MM-ddTHH.mm.ss.fff"); requestBody.Sheets = new List <Sheet>(); // Create sheets foreach (var item in top10) { Console.WriteLine("Create sheet for " + item.GEONAME); Sheet x = new Sheet(); x.Properties = new SheetProperties(); x.Properties.Title = item.GEONAME; requestBody.Sheets.Add(x); } // Execute SpreadsheetsResource.CreateRequest request = service.Spreadsheets.Create(requestBody); for (int xy = 0; xy < 3; xy++) { try { Data.Spreadsheet response = request.Execute(); spreadsheetId = response.SpreadsheetId; spreadsheetUrl = response.SpreadsheetUrl; spsheedid = spreadsheetId; spsheeturl = spreadsheetUrl; break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { Console.WriteLine("An error occurred: " + ex.ToString()); break; } } } } #endregion // Insert data into spreadsheet if (spreadsheetId != "") { foreach (var item in top10) { Console.WriteLine("Add data row to sheet " + item.GEONAME); // Add general information #region if (range1 != "") { string range = String.Format(range1, item.GEONAME); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); for (int ix = 0; ix < colNames.Length; ix++) { var oblist = new List <object>() { colNames[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } } if (range2 != "") { string range = String.Format(range2, item.GEONAME); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); string[] po = item.ptoa; for (int ix = 0; ix < po.Length; ix++) { var oblist = new List <object>() { po[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } } #endregion // Add Population Change information #region int r = 4; if (range3 != "") { string range = String.Format(range3, item.GEONAME, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> > { new List <object>() { "Estimated Resident Population Change, and Rates of Resident Population Change" } }; SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } r++; } if (range2_2 != "") { string range = String.Format(range2_2, item.GEONAME, r, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); string[] po = colStatsBD; for (int ix = 0; ix < po.Length; ix++) { var oblist = new List <object>() { po[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } r++; } if (range2_2 != "") { string range = String.Format(range2_2, item.GEONAME, r, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); string[] po = item.statsbd.ptoa; for (int ix = 0; ix < po.Length; ix++) { var oblist = new List <object>() { po[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; r += 2; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } } #endregion // Add Language Spoken information #region if (range3 != "") { string range = String.Format(range3, item.GEONAME, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> > { new List <object>() { "Detailed Language Spoken" } }; SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } r++; } if (range4 != "") { string range = String.Format(range4, item.GEONAME, r, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); string[] po = colLangs; for (int ix = 0; ix < po.Length; ix++) { var oblist = new List <object>() { po[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } r++; } if (item.languages != null && item.languages.Count > 0) { for (int xi = 0; xi < item.languages.Count; xi++) { string range = String.Format(range4, item.GEONAME, r, r); ValueRange valueRange = new ValueRange(); valueRange.MajorDimension = "COLUMNS"; valueRange.Values = new List <IList <object> >(); string[] po = item.languages[xi].ptoa; for (int ix = 0; ix < po.Length; ix++) { var oblist = new List <object>() { po[ix] }; valueRange.Values.Add(oblist); } SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; for (int xy = 0; xy < 3; xy++) { try { UpdateValuesResponse result2 = update.Execute(); break; } catch (Exception ex) { if (ex.Message.IndexOf("USER-100s") >= 0) { Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s } else { break; } } } r++; } } #endregion } } } if (spreadsheetId != "") { var service = new DriveService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); InsertPermission(service, spreadsheetId, null, "anyone", "reader"); } }
static void Main(string[] args) { /* * Set up API services */ UserCredential credential; using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) { string credPath = System.Environment.GetFolderPath( System.Environment.SpecialFolder.Personal); credPath = Path.Combine(credPath, ".credentials/drive-dotnet-quickstart.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, new FileDataStore(credPath, true)).Result; Console.WriteLine("Google API credential file saved to: " + credPath); } // Create Google Sheets API service. var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); // Create Drive API service. var driveService = new DriveService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = ApplicationName, }); /* * USB Port Config */ var port = new SerialPort(); //Get user input Console.Write("Enter port number: "); String portNum = Console.ReadLine().Substring(0, 1); try { port.PortName = "COM" + portNum; port.BaudRate = 9600; port.ReadTimeout = 500; port.WriteTimeout = 500; port.Open(); port.DiscardInBuffer(); Console.WriteLine("Opened serial communication on COM" + portNum); } catch (Exception) { Console.WriteLine("ERROR: Failed to open COM" + portNum); } var propertiesFileLocation = @"C:\Users\danie\Desktop\VFproperties.txt"; String machineSN = ""; String spreadsheetID = ""; String folderID = "1YDdzdk8XuOHjplu_OcLPgwEl9Gnjk9LX"; try { System.IO.StreamReader propertiesFile = new System.IO.StreamReader(propertiesFileLocation); String propertiesLine1 = propertiesFile.ReadLine(); //Machine SN String propertiesLine2 = propertiesFile.ReadLine(); //Current Spreadsheet ID String propertiesLine3 = propertiesFile.ReadLine(); //Folder ID propertiesFile.Close(); machineSN = propertiesLine1.Substring(propertiesLine1.IndexOf(':') + 2); spreadsheetID = propertiesLine2.Substring(propertiesLine2.IndexOf(':') + 2); folderID = propertiesLine3.Substring(propertiesLine2.IndexOf(':') + 2); Console.WriteLine("Machine SN: " + machineSN); Console.WriteLine("Spreadsheet ID: " + spreadsheetID); Console.WriteLine("Folder ID: " + folderID); } catch (Exception) { Console.WriteLine("ERROR: Failed to locate or read VFproperties.txt, ensure this is the correct directory: " + propertiesFileLocation); } while (true) { if (port.IsOpen) { if (port.BytesToRead > 0) { String SN = ""; String tempA = ""; String tempW = ""; String humidity = ""; String RSC = ""; String LSC = ""; String watered = ""; String errorCode = ""; String dateTime = DateTime.Now.ToString(); String input = port.ReadLine(); if (input.Contains("Clear") || input.Contains("Start")) { Console.WriteLine(dateTime + " " + input); } else if (input.Contains("ERROR")) { errorCode = input.Substring(input.IndexOf(':') + 2); /* * Add data to spreadsheet */ String range = "Sheet1!A:I"; // How the input data should be interpreted. SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; // How the input data should be inserted. SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; Data.ValueRange requestBody = new Data.ValueRange(); requestBody.Range = range; requestBody.MajorDimension = "ROWS"; var oblist = new List <object>() { dateTime, SN, tempA, tempW, humidity, RSC, LSC, watered, errorCode }; requestBody.Values = new List <IList <object> > { oblist }; SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range); appendRequest.ValueInputOption = valueInputOption; appendRequest.InsertDataOption = insertDataOption; Data.AppendValuesResponse updateResponse = appendRequest.Execute(); String rangeReturn = JsonConvert.SerializeObject(updateResponse.Updates.UpdatedRange); rangeReturn = rangeReturn.Substring(rangeReturn.IndexOf("!A") + 2); rangeReturn = rangeReturn.Substring(0, rangeReturn.IndexOf(':')); int i = Convert.ToInt32(rangeReturn); //row it was put into /* * Print data to console */ Console.WriteLine(dateTime + ", SN: " + SN + ", Temp A (c): " + tempA + ", Temp W (c): " + tempW + ", Humidity (%): " + humidity + ", RSC: " + RSC + ", LSC: " + LSC + ", Watered: " + watered + ", Error: " + errorCode); if (i >= 10000) { /* * Create new spreadsheet */ Data.Spreadsheet requestBody1 = new Data.Spreadsheet(); requestBody1.Properties = new SpreadsheetProperties(); requestBody1.Properties.Title = dateTime; SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody1); Data.Spreadsheet response = request.Execute(); spreadsheetID = JsonConvert.SerializeObject(response.SpreadsheetId); spreadsheetID = spreadsheetID.Substring(1, spreadsheetID.Length - 2); Console.WriteLine(spreadsheetID); /* * Move spreadsheet to correct folder */ // Retrieve the existing parents to remove var getRequest = driveService.Files.Get(spreadsheetID); getRequest.Fields = "parents"; var file = getRequest.Execute(); var previousParents = String.Join(",", file.Parents); // Move the file to the new folder var updateRequest = driveService.Files.Update(new Google.Apis.Drive.v3.Data.File(), spreadsheetID); updateRequest.Fields = "id, parents"; updateRequest.AddParents = folderID; updateRequest.RemoveParents = previousParents; file = updateRequest.Execute(); /* * Add headers to spreadsheet */ oblist = new List <object>() { "Date/Time", "Serial Number", "Air Temp (c)", "Water Temp (c)", "Humidity (%)", "RSC", "LSC", "Watered", "Error" }; requestBody.Values = new List <IList <object> > { oblist }; appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range); updateResponse = appendRequest.Execute(); /* * Update VFproperties.txt with new ID's */ String[] lines = { "Machine Serial Number: " + machineSN, "Current Spreadsheet ID: " + spreadsheetID, "Google Drive Folder ID: " + folderID }; System.IO.File.WriteAllLines(propertiesFileLocation, lines); } } else { SN = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); tempA = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); tempW = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); humidity = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); RSC = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); LSC = input.Substring(0, input.IndexOf(',')); input = input.Substring(input.IndexOf(',') + 1); watered = input.Substring(0, 1); /* * Add data to spreadsheet */ String range = "Sheet1!A:I"; // How the input data should be interpreted. SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; // How the input data should be inserted. SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS; Data.ValueRange requestBody = new Data.ValueRange(); requestBody.Range = range; requestBody.MajorDimension = "ROWS"; var oblist = new List <object>() { dateTime, SN, tempA, tempW, humidity, RSC, LSC, watered, errorCode }; requestBody.Values = new List <IList <object> > { oblist }; SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range); appendRequest.ValueInputOption = valueInputOption; appendRequest.InsertDataOption = insertDataOption; Data.AppendValuesResponse updateResponse = appendRequest.Execute(); String rangeReturn = JsonConvert.SerializeObject(updateResponse.Updates.UpdatedRange); rangeReturn = rangeReturn.Substring(rangeReturn.IndexOf("!A") + 2); rangeReturn = rangeReturn.Substring(0, rangeReturn.IndexOf(':')); int i = Convert.ToInt32(rangeReturn); //row it was put into /* * Print data to console */ Console.WriteLine(dateTime + ", SN: " + SN + ", Temp A (c): " + tempA + ", Temp W (c): " + tempW + ", Humidity (%): " + humidity + ", RSC: " + RSC + ", LSC: " + LSC + ", Watered: " + watered + ", Error: " + errorCode); if (i >= 10000) { /* * Create new spreadsheet */ Data.Spreadsheet requestBody1 = new Data.Spreadsheet(); requestBody1.Properties = new SpreadsheetProperties(); requestBody1.Properties.Title = dateTime; SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody1); Data.Spreadsheet response = request.Execute(); spreadsheetID = JsonConvert.SerializeObject(response.SpreadsheetId); spreadsheetID = spreadsheetID.Substring(1, spreadsheetID.Length - 2); Console.WriteLine(spreadsheetID); /* * Move spreadsheet to correct folder */ // Retrieve the existing parents to remove var getRequest = driveService.Files.Get(spreadsheetID); getRequest.Fields = "parents"; var file = getRequest.Execute(); var previousParents = String.Join(",", file.Parents); // Move the file to the new folder var updateRequest = driveService.Files.Update(new Google.Apis.Drive.v3.Data.File(), spreadsheetID); updateRequest.Fields = "id, parents"; updateRequest.AddParents = folderID; updateRequest.RemoveParents = previousParents; file = updateRequest.Execute(); /* * Add headers to spreadsheet */ oblist = new List <object>() { "Date/Time", "Serial Number", "Air Temp (c)", "Water Temp (c)", "Humidity (%)", "RSC", "LSC", "Watered", "Error" }; requestBody.Values = new List <IList <object> > { oblist }; appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range); updateResponse = appendRequest.Execute(); /* * Update VFproperties.txt with new ID's */ String[] lines = { "Machine Serial Number: " + machineSN, "Current Spreadsheet ID: " + spreadsheetID, "Google Drive Folder ID: " + folderID }; System.IO.File.WriteAllLines(propertiesFileLocation, lines); } } } } } port.Close(); }