예제 #1
0
        public void DeleteAllRowsAndColumns(string sheetName)
        {
            BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();

            DeleteDimensionRequest deleteRowsRequest = new DeleteDimensionRequest
            {
                Range = new DimensionRange {
                    Dimension = "ROWS", StartIndex = 0, EndIndex = 500000
                }
            };

            DeleteDimensionRequest deleteColumnsRequest = new DeleteDimensionRequest
            {
                Range = new DimensionRange {
                    Dimension = "COLUMNS", StartIndex = 0, EndIndex = 500000
                }
            };

            requestBody.Requests = new List <Request>
            {
                new Request {
                    DeleteDimension = deleteRowsRequest
                },
                new Request {
                    DeleteDimension = deleteColumnsRequest
                }
            };

            SpreadsheetsResource.BatchUpdateRequest batchRequest =
                Service.Spreadsheets.BatchUpdate(requestBody, SheetId);

            var response = batchRequest.Execute();
        }
예제 #2
0
        // Removes specified rows.
        internal static SpreadsheetsResource.BatchUpdateRequest GetRemoveRowsRequest(SheetsService sheetsService, string spreadsheetId, int?sheetId, int toBeRemovedTopRowIndex, int toBeRemovedRowCount)
        {
            // Build delete rows partial request...
            DeleteDimensionRequest request = new DeleteDimensionRequest();

            request.Range            = new DimensionRange();
            request.Range.SheetId    = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
            request.Range.Dimension  = "ROWS";
            request.Range.StartIndex = toBeRemovedTopRowIndex;
            request.Range.EndIndex   = toBeRemovedTopRowIndex + toBeRemovedRowCount;

            // Construct new batch update request body..
            BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();

            // .. initialize new list of partial requests ..
            batchUpdateSpreadsheetRequest.Requests = new List <Request>();
            // .. and add appropriate partial request.
            batchUpdateSpreadsheetRequest.Requests.Add(new Request {
                DeleteDimension = request
            });

            // Construct and return appropriate batch update request based on the provided request body and spreadsheet id.
            return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId));
        }
예제 #3
0
        //static volatile bool exit = false;

        static void Main(string[] args)
        {
            UserCredential credential;

            using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

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

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

            // Prints the data from a sample spreadsheet:
            // https://docs.google.com/spreadsheets/d/1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So/edit#gid=0

            // Define request parameters.
            String spreadsheetId = "1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So";

            // here is the actual data to be sent to sheet
            List <object> headerList = new List <object>()
            {
                "ID", "DTStamp", "DTShiftStart", "ModelNbr", "SerialNbr", "PassFail", "LineNbr", "ShiftNbr", "Computer", "Word40", "Word41", "Word42"
                , "Word43", "Word44", "Word45", "Word46", "Word47", "Word48", "Word49", "Word50", "Word51", "Word52", "Word53", "Word54", "Word55", "Word56"
                , "Word57", "Word58", "Word59", "Word60", "Word61", "Word62", "Word63", "Word64", "Word65", "Word66", "Word67", "Word68", "Word69", "Word70"
                , "Word71", "Word72", "Word73", "Word74", "Word75", "Word76", "Word77", "Word78", "Word79", "Word80"
            };

            //var dataList = new List<object>();

            //Write some data
            String     writeRange = "WriteData!A1:ZZ";
            ValueRange valueRange = new ValueRange {
                MajorDimension = "ROWS"
            };

            Console.WriteLine("Clear the Sheet");

            //API method to clear the sheet
            ClearValuesRequest clearValuesRequest = new ClearValuesRequest();

            SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, writeRange);
            ClearValuesResponse clearResponse = cr.Execute();

            Console.WriteLine("Delete all rows in Sheet");

            //API method to batch update
            DimensionRange dr = new DimensionRange
            {
                Dimension  = "ROWS",
                StartIndex = 1,
                SheetId    = 1809337217
            };

            DeleteDimensionRequest ddr = new DeleteDimensionRequest()
            {
                Range = dr
            };

            Request r = new Request {
                DeleteDimension = ddr
            };

            // { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} }  ]};
            List <Request> batchRequests = new List <Request>()
            {
                r
            };

            BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest()
            {
                Requests = batchRequests
            };

            SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
            BatchUpdateSpreadsheetResponse          busr     = bRequest.Execute();

            Console.WriteLine("Write the Headers to the Sheet");

            //API method to update the sheet
            valueRange.Values = new List <IList <object> > {
                headerList
            };
            SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange);
            update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
            UpdateValuesResponse result;

            result = update.Execute();

            SqlConnection sqlConnection = new SqlConnection("Data Source=tul-mssql;Initial Catalog=Division;User ID=tqisadmin;Password=admin2k");
            SqlCommand    cmd           = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "SELECT TOP 1000 [ID],[DTStamp],[DTShiftStart],[ModelNbr],[SerialNbr],[PassFail],[LineNbr],[ShiftNbr],[Computer],[Word40],[Word41],[Word42]" +
                              ",[Word43],[Word44],[Word45],[Word46],[Word47],[Word48],[Word49],[Word50],[Word51],[Word52],[Word53],[Word54],[Word55],[Word56]" +
                              ",[Word57],[Word58],[Word59],[Word60],[Word61],[Word62],[Word63],[Word64],[Word65],[Word66],[Word67],[Word68],[Word69],[Word70]" +
                              ",[Word71],[Word72],[Word73],[Word74],[Word75],[Word76],[Word77],[Word78],[Word79],[Word80] " +
                              "FROM[Division].[dbo].[asyTestRecords] where LineNbr = 2 and computer = 'LN' and dtstamp > '2/1/2018 5:00' order by dtstamp desc";

            cmd.CommandType = CommandType.Text;
            cmd.Connection  = sqlConnection;

            Console.WriteLine("Open the SQL connection");
            sqlConnection.Open();
            cmd.CommandTimeout = 60;
            Console.WriteLine("Please wait while reading data from SQL");
            reader = cmd.ExecuteReader();

            // Data is accessible through the DataReader object here.
            ValueRange valueDataRange = new ValueRange()
            {
                MajorDimension = "ROWS"
            };

            var dataList = new List <object>();

            valueDataRange.Values = new List <IList <object> > {
                dataList
            };

            //API to append data to sheet
            SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange);
            appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
            appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;

            if (reader.HasRows)
            {
                //Console.WriteLine("{0}",reader.FieldCount);
                Object[] colValues = new Object[reader.FieldCount];

                int throttleCount = 0;
                int cnt           = 0;
                while (reader.Read())
                {
                    //This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
                    dataList.Clear();
                    for (int i = 0; i < reader.GetValues(colValues); i++)
                    {
                        dataList.Add(colValues[i]);
                    }

                    try
                    {
                        //This is the GOOGLE query Throttle they only allow 500 writes per 100 sec
                        System.Threading.Thread.Sleep(20);
                        AppendValuesResponse appendValueResponse = appendRequest.Execute();
                        Console.WriteLine("Writing to Sheet: row{0}", cnt);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Whoa buddy slowdown {0}", throttleCount);
                        System.Threading.Thread.Sleep(3000);
                        throttleCount++;
                    }
                    cnt++;
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }

            // sit here and wait for a while
            Console.WriteLine("Done waiting to close reader and SQL");
            System.Threading.Thread.Sleep(3000);

            reader.Close();
            sqlConnection.Close();
        }