Example #1
0
        private void CleanRange(SheetsService service, string range)
        {
            Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();

            SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);

            // To execute asynchronously in an async method, replace `request.Execute()` as shown:
            Data.ClearValuesResponse response = request.Execute();
        }
        public static void Main(string[] args)
        {
            string x = "0";

            while (x != "6")
            {
                Console.WriteLine("Press: \n1. Reading\n2. Appending\n3. Updating\n4. Clearing\n5. Add/Update Headers\n6. Exit");
                x = Console.ReadLine();
                switch (x)
                {
                case "1":
                    Data.ValueRange readResponse = ReadSpreadsheet(spreadsheetId, range, service);
                    if (readResponse.Values == null)
                    {
                        Console.WriteLine("Sorry! No data read from this range.");
                        break;
                    }
                    Console.WriteLine("Read " + readResponse.Values.Count + " rows.");
                    break;

                case "2":
                    Data.AppendValuesResponse appendResponse = AppendSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
                    Console.WriteLine("Appended! Updated " + appendResponse.Updates.UpdatedCells + " cells.");
                    break;

                case "3":
                    Data.UpdateValuesResponse updateResponse = UpdateSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
                    Console.WriteLine("Updated " + updateResponse.UpdatedRows + " rows and " + updateResponse.UpdatedColumns + " columns.");
                    break;

                case "4":
                    Data.ClearValuesResponse clearResponse = Clearspreadsheet(spreadsheetId, range, service);
                    Console.WriteLine("Cleared range: " + clearResponse.ClearedRange);
                    break;

                case "5":
                    UpdateHeadersInSpreadsheet(spreadsheetId, service, GetHeaders());
                    Console.WriteLine("Updated the headers!");
                    break;

                default:
                    Console.WriteLine("Please enter a valid key.");
                    break;
                }
                ;
            }
        }
        public static Data.ClearValuesResponse Clearspreadsheet(string spreadsheetId, string range, SheetsService service)
        {
            Console.WriteLine("Do you want to give your own range? 1. Yes\t Any other key. No");
            string rangeChoice = Console.ReadLine();

            if (rangeChoice == "1")
            {
                range = GetRangeFromUser();
            }

            //Create a clear request
            SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(null, spreadsheetId, range);

            Console.WriteLine("Clearing range " + range + " from spreadsheet...");

            //Execute the request
            Data.ClearValuesResponse response = request.Execute();
            Headers.Clear();
            return(response);
        }
Example #4
0
        public Form1()
        {
            InitializeComponent();
            GoogleCredential credential;

            using (var stream = new FileStream("My First Project-2dfed0050064.json", FileMode.Open, FileAccess.Read))
            {
                credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
            }

            service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName       = ApplicationName,
            });

            Google.Apis.Sheets.v4.Data.ClearValuesRequest    requestBody = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
            SpreadsheetsResource.ValuesResource.ClearRequest request     = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Main Table!A1:Z100");
            Google.Apis.Sheets.v4.Data.ClearValuesResponse   response    = request.Execute();

            Google.Apis.Sheets.v4.Data.ClearValuesRequest    requestBody2 = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
            SpreadsheetsResource.ValuesResource.ClearRequest request2     = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Data Table!A1:Z100");
            Google.Apis.Sheets.v4.Data.ClearValuesResponse   response2    = request2.Execute();
        }
Example #5
0
        public object deleteData(string spreadsheetId, string tenBang, string phamViCell)
        {
            /*Xóa dữ liệu trong bảng
             * Đầu vào cần có ID bảng, Tên sheet vd: sheet 1, phạm vi cần xóa*/
            UserCredential credential    = GetCredential(ScopesWrite);
            var            sheetsService = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName       = "VolunteerDTU"
            });

            try
            {
                string range = "'" + tenBang + "'!" + phamViCell;;
                Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
                SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
                Data.ClearValuesResponse response = request.Execute();
                return(request);
            }
            catch (Exception e)
            {
                return(e);
            }
        }
Example #6
0
 public async Task ClearArea(string range, string spreadsheetId)
 {
     Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
     SpreadsheetsResource.ValuesResource.ClearRequest request = _service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
     Data.ClearValuesResponse response = await request.ExecuteAsync();
 }
Example #7
0
        static void Main(string[] args)
        {
            Console.WriteLine("Welcome to Sheets2CSV");
            if (File.Exists("Settings.txt"))
            {
                Console.WriteLine("There Are Settings!!");
                StreamReader infile = new StreamReader("Settings.txt");
                configured    = infile.ReadLine();
                outputdir     = infile.ReadLine();
                spreadsheetID = infile.ReadLine();
                headernum     = infile.ReadLine();
                entity        = infile.ReadLine();
                columns       = infile.ReadLine();
                infile.Close();
            }
            else
            {
                Console.WriteLine("Program has not been configured yet! Please Run SettingsUI first to start this program.");
                Environment.Exit(0);
            }

            Console.WriteLine("Moving On...");

            #region Configuration Level
            string path = outputdir;
            //string value = outputdir;

            path = path + "\\";
            #endregion

            #region Preparation Level
            #region Sheets API
            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-Sheetstocsv.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                //Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName       = ApplicationName,
            });

            // Define request parameters.
            String spreadsheetId = spreadsheetID;
            char   let           = (char)(64 + Convert.ToInt32(headernum));
            string range         = "A2:" + let;

            SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);

            Data.ValueRange         response = request.Execute();
            IList <IList <Object> > values   = response.Values;
            #endregion

            Boolean fileExist;

            //Check for File
            if (File.Exists(path + "Lead_Sheet.csv"))
            {
                fileExist = true;
            }
            else
            {
                fileExist = false;
            }

            //Setup File IO
            StreamWriter file          = new StreamWriter(path + entity + "_Sheet.csv", true);
            StreamWriter injectionfile = new StreamWriter(path + entity + "_inject.csv");

            //Set up Headers
            string[] headers = new string[]
            {
                /*"+new_createdon",
                 * "firstname",
                 * "lastname",
                 * "address1_postalcode",
                 * "emailaddress1",
                 * "telephone2",
                 * "new_isprimaryphonecellphone",
                 * "*new_besttimetocall",
                 * "subject"*/
                "fname",
                "lname",
                "org_name",
                "member_role"
            };

            //Check for exisitng records
            if (!fileExist)
            {
                file.Write(entity + ", " + columns + "\n");
            }
            injectionfile.Write(entity + ", " + columns + "\n");
            #endregion

            #region Execution Level
            //Handle Retrieved Data
            if (values != null && values.Count > 0)
            {
                foreach (var row in values)
                {
                    file.Write(entity + ", ");
                    injectionfile.Write(entity + ", ");
                    for (int i = 0; i < row.Count - 1; i++)
                    {
                        if (i == row.Count - 1)
                        {
                            file.Write(row[i]);
                            injectionfile.Write(row[i]);
                        }
                        if (headers[i] == "address1_postalcode")
                        {
                            string r = row[i].ToString();
                            if (r.Length > 5)
                            {
                                file.Write(r.Substring(0, 5) + ", ");
                                injectionfile.Write(r.Substring(0, 5) + ", ");
                            }
                            else
                            {
                                file.Write(row[i] + ", ");
                                injectionfile.Write(row[i] + ", ");
                            }
                        }
                        else if (headers[i] == "telephone2")
                        {
                            string val = row[i].ToString();
                            val = val.Replace("-", string.Empty);
                            val = val.Replace(" ", string.Empty);
                            val = val.Replace("(", string.Empty);
                            val = val.Replace(")", string.Empty);
                            val = val.Replace(".", string.Empty);
                            if (val.Length > 10)
                            {
                                val = val.Substring(0, 10);
                            }
                            file.Write(val + ", ");
                            injectionfile.Write(val + ", ");
                        }
                        else if (headers[i] == "new_isprimaryphonecellphone")
                        {
                            if (row[i].ToString() == "Yes")
                            {
                                file.Write("TRUE" + ", ");
                                injectionfile.Write("TRUE" + ", ");
                            }
                            else
                            {
                                file.Write("FALSE" + ", ");
                                injectionfile.Write("FALSE" + ", ");
                            }
                        }
                        else if (headers[i] == "*new_besttimetocall")
                        {
                            file.Write(callTime[row[i].ToString()] + ", ");
                            injectionfile.Write(callTime[row[i].ToString()] + ", ");
                        }
                        else
                        {
                            file.Write(row[i] + ", ");
                            injectionfile.Write(row[i] + ", ");
                        }
                    }
                    file.Write("\n");
                    injectionfile.Write("\n");
                }
            }


            string ClearRange = "A2:Z";
            Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
            SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, ClearRange);
            Data.ClearValuesResponse clearResponse = clearRequest.Execute();

            file.Close();
            injectionfile.Close();
            #endregion
        }