// Create new spreadsheet from data containing spreadsheet blueprint internal static async Task <Spreadsheet> AddSpreadsheetAsync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int, IList <IList <string> > > > spreadsheetBlueprint) { // Wait for google apis request quota availability. await SessionRequestsLimiter.Instance.WaitAsync(); // Obtain create spreadsheet request. SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetBlueprint); // Execute createSpreadsheetRequest in safe asynchronous manner, and store the result in a local variable. Spreadsheet spreadsheet = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(createSpreadsheetRequest); // Obtain get spreadsheet request. SpreadsheetsResource.GetRequest getSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetRequest(sheetsService, spreadsheet.SpreadsheetId); // Execute getSpreadsheetRequest in safe synchronous manner. return(await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetRequest)); }
// Create new spreadsheet from data containing spreadsheet blueprint internal static Spreadsheet AddSpreadsheetSync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int, IList <IList <string> > > > spreadsheetBlueprint) { // Wait for google apis request quota availability. SessionRequestsLimiter.Instance.Wait(); // Obtain create spreadsheet request. SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetBlueprint); // Execute createSpreadsheetRequest in safe synchronous manner, and store revived dateless spreadsheet instance Spreadsheet spreadsheet = RequestsExecutor.SafeExecuteSync <Spreadsheet>(createSpreadsheetRequest); // Obtain get spreadsheet request. SpreadsheetsResource.GetRequest getSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetRequest(sheetsService, spreadsheet.SpreadsheetId); // Execute getSpreadsheetRequest in safe synchronous manner. return(RequestsExecutor.SafeExecuteSync <Spreadsheet>(getSpreadsheetRequest)); }
//the main procedure public void CreateSheet(int N) { //get credentials service = cred.GetCredential(); //create a spreadsheet Spreadsheet requestBody = new Spreadsheet(); SpreadsheetsResource.CreateRequest request = service.Spreadsheets.Create(requestBody); // To execute asynchronously in an async method, replace `request.Execute()` as shown: Spreadsheet response = request.Execute(); // Data.Spreadsheet response = await request.ExecuteAsync(); // TODO: Change code below to process the `response` object: JsonConvert.SerializeObject(response); //get spreadsheet id sheetID = response.SpreadsheetId; //get a hyperlink for user to use url = response.SpreadsheetUrl; }
/// <summary> /// creates a new Google Sheet and returns the ID /// </summary> /// <param name="SheetName"></param> /// <returns></returns> public static String 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(); return(response.SpreadsheetId); }
public SpreadsheetClass CreateSheet(SpreadsheetClass sheetEntities) { Spreadsheet spreadsheet = new Spreadsheet(); spreadsheet.Properties = new SpreadsheetProperties(); spreadsheet.Properties.Title = "Google Sheets Exm"; List <Sheet> sheets = new List <Sheet>(); foreach (GoogleSheet item in sheetEntities.Servers) { Sheet sheet = new Sheet(); sheet.Properties = new SheetProperties(); sheet.Properties.Title = item.serverName; sheets.Add(sheet); } spreadsheet.Sheets = sheets; SpreadsheetsResource.CreateRequest request = this.service.Spreadsheets.Create(spreadsheet); var x = request.Execute(); sheetEntities.SpreadsheetId = x.SpreadsheetId; return(sheetEntities); }
//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)); }
//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> /// /// </summary> /// <param name="title"></param> /// <param name="sheetTitle"></param> /// <returns></returns> public async Task <Spreadsheet> CreateAsync(string title, string sheetTitle) { var requestBody = new Spreadsheet { Properties = new SpreadsheetProperties { Title = title }, Sheets = new List <Sheet> { new Sheet { Properties = new SheetProperties { Title = sheetTitle, TabColor = new Color { Red = 0.9F, Green = 0.1F, Blue = 0.1F, Alpha = 0.7F }, GridProperties = new GridProperties { ColumnCount = 50 } } } } }; SpreadsheetsResource.CreateRequest createRequest = _sheetsService.Spreadsheets.Create(requestBody); Spreadsheet createResponse = await createRequest.ExecuteAsync(); return(createResponse); }
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)); }
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(); }