// get table and list in google sheets
        public Data.Spreadsheet CreateSpreadsheet(SheetsService service, string tableName, List <Server> servers)
        {
            Data.Spreadsheet requestBody = new Data.Spreadsheet();

            List <Data.Sheet> list = new List <Data.Sheet>();

            for (int i = 0; i < servers.Count; i++)
            {
                list.Add(new Data.Sheet
                {
                    Properties = new Data.SheetProperties
                    {
                        Title = servers[0].name
                    }
                });
            }

            requestBody.Sheets     = list;
            requestBody.Properties = new Data.SpreadsheetProperties
            {
                Title = tableName
            };

            SpreadsheetsResource.CreateRequest request = service.Spreadsheets.Create(requestBody);

            Data.Spreadsheet response = request.Execute();

            return(response);
        }
        static void AddSheetsToSheetTitle(string SheetTitle, IList <Sheet> Sheets, string CellRange)
        {
            SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
            {
                HttpClientInitializer = GetCredential(),
                ApplicationName       = ApplicationName,
            });
            string SheetId = GetSheetIdByTitle(SheetTitle);

            //Copys the current sheet data and adds to it
            SpreadsheetsResource.GetRequest getRequest = sheetsService.Spreadsheets.Get(SheetId);
            Spreadsheet requestBody = getRequest.Execute();

            requestBody.SpreadsheetId = null;

            //Update values in requestBody cells and sheets

            List <SpreadsheetsResource.ValuesResource.GetRequest> valueRequests = new List <SpreadsheetsResource.ValuesResource.GetRequest>();

            for (int i = 0; i < requestBody.Sheets.Count; i++)
            {
                valueRequests.Add(sheetsService.Spreadsheets.Values.Get(SheetId, requestBody.Sheets[i].Properties.Title + "!" + CellRange));
            }

            //execute all requests
            List <ValueRange> responses = new List <ValueRange>();

            for (int i = 0; i < valueRequests.Count; i++)
            {
                responses.Add(valueRequests[i].Execute());
            }

            //Add sheets to requestBody
            for (int i = 0; i < Sheets.Count; i++)
            {
                requestBody.Sheets.Add(Sheets[i]);
            }

            //Overwrite current sheet, delete first sheet
            SpreadsheetsResource.CreateRequest createRequest = sheetsService.Spreadsheets.Create(requestBody);
            //delete sheet
            DeleteFileByTitle(SheetTitle);
            createRequest.Execute();

            //Update sheets and cells
            for (int i = 0; i < responses.Count; i++)
            {
                SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest =
                    sheetsService.Spreadsheets.Values.Update(responses[i], GetSheetIdByTitle(SheetTitle), requestBody.Sheets[i].Properties.Title + "!" + CellRange);
                updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
                updateRequest.Execute();
            }
        }
Exemple #3
0
        public void CreateSheet(string sheetName)
        {
            Spreadsheet spreadsheet = new Spreadsheet();

            spreadsheet.Properties = new SpreadsheetProperties()
            {
                Title = sheetName
            };
            SpreadsheetsResource.CreateRequest createRequest = Service.Spreadsheets.Create(spreadsheet);
            var response = createRequest.Execute();

            if (response == null || response.SpreadsheetId == null)
            {
                throw new Exception("SpreadSheet creation failed");
            }
        }
Exemple #4
0
        //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;
        }
Exemple #5
0
        /// <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));
        }
        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));
        }
Exemple #10
0
        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);
            }
        }
Exemple #11
0
        /// <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");
            }
        }
Exemple #12
0
        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();
        }