public static void appendEmptyColumn(string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
        {
            // The A1 notation of a range to search for a logical table of data.
            // Values will be appended after the last row of the table.
            //   string range = "1 - Performance Test Results";  // TODO: Update placeholder value.
            //"'1 - Performance Test Results'!A:G"
            // The ID of the spreadsheet to update.

            SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
            {
                HttpClientInitializer = GetCredential(),
                ApplicationName       = "RaveReportAutomationUtility",
            });

            Data.Request reqBody = new Data.Request
            {
                AppendDimension = new Data.AppendDimensionRequest
                {
                    SheetId   = sheetId,
                    Dimension = "COLUMNS",
                    Length    = 1
                }
            };

            List <Data.Request> requests = new List <Data.Request>();

            requests.Add(reqBody);


            // TODO: Assign values to desired properties of `requestBody`:
            Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
            requestBody.Requests = requests;

            SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
            //SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);


            // To execute asynchronously in an async method, replace `request.Execute()` as shown:
            Data.BatchUpdateSpreadsheetResponse response = request.Execute();
            // Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();

            // TODO: Change code below to process the `response` object:
            //Console.WriteLine(JsonConvert.SerializeObject(response));


            //     SpreadsheetsResource.ValuesResource.AppendRequest request =
            //         sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
            //   request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
            //request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
            //var response = request.Execute();
        }
        private async void HighlightCellAsync(int row, int col)
        {
            Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();

            // Send a request that get the range, set the background color to highlight yellow, and set the date as today
            requestBody.Requests = new List <Google.Apis.Sheets.v4.Data.Request>()
            {
                new Request()
                {
                    RepeatCell = new RepeatCellRequest()
                    {
                        Range = new GridRange()
                        {
                            SheetId          = this.SheetID,
                            StartRowIndex    = row,
                            EndRowIndex      = row + 1,
                            StartColumnIndex = col,
                            EndColumnIndex   = col + 1
                        },
                        Cell = new CellData()
                        {
                            UserEnteredFormat = new CellFormat()
                            {
                                BackgroundColor = new Color()
                                {
                                    Red   = 1,
                                    Green = 1,
                                    Blue  = 0
                                },
                            },
                            UserEnteredValue = new ExtendedValue()
                            {
                                StringValue = DateTime.Today.ToString("MM/dd/yyyy")
                            }
                        },
                        Fields = "userEnteredValue,userEnteredFormat.backgroundColor"
                    },
                }
            };

            // Send the request
            SpreadsheetsResource.BatchUpdateRequest request = Service.Spreadsheets.BatchUpdate(requestBody, this.SpreadSheetID);
            Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();

            Console.WriteLine(JsonConvert.SerializeObject(response));
        }
        public static void mergeCells(int startCol, int startRow, string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
        {
            // The A1 notation of a range to search for a logical table of data.
            // Values will be appended after the last row of the table.

            //"'1 - Performance Test Results'!A:G"
            // The ID of the spreadsheet to update.


            // string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox



            SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
            {
                HttpClientInitializer = GetCredential(),
                ApplicationName       = "RaveReportAutomationUtility",
            });

            Data.Request reqBody = new Data.Request
            {
                MergeCells = new Data.MergeCellsRequest
                {
                    Range = new Data.GridRange
                    {
                        SheetId          = sheetId,
                        StartRowIndex    = startRow,
                        EndRowIndex      = startRow + 1,
                        StartColumnIndex = startCol,
                        EndColumnIndex   = startCol + 9
                    },
                    MergeType = "MERGE_ROWS"
                }
            };

            List <Data.Request> requests = new List <Data.Request>();

            requests.Add(reqBody);


            // TODO: Assign values to desired properties of `requestBody`:
            Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
            requestBody.Requests = requests;

            SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
            //SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);


            // To execute asynchronously in an async method, replace `request.Execute()` as shown:
            Data.BatchUpdateSpreadsheetResponse response = request.Execute();
            // Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();

            // TODO: Change code below to process the `response` object:
            //Console.WriteLine(JsonConvert.SerializeObject(response));


            //     SpreadsheetsResource.ValuesResource.AppendRequest request =
            //         sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
            //   request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
            //request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
            //var response = request.Execute();
        }
Exemplo n.º 4
0
        private static void FieldMatrixHeaderData(SheetsService sheetsService, string sheetName, List <FieldCordinates> fieldCoordRes)
        {
            // The ID of the spreadsheet to update.
            // TODO: Update placeholder value.

            BatchUpdateSpreadsheetRequest bussr = new BatchUpdateSpreadsheetRequest();

            //get sheet id by sheet name
            Spreadsheet spr     = sheetsService.Spreadsheets.Get(spreadSheetId).Execute();
            Sheet       sh      = spr.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
            int         sheetId = (int)sh.Properties.SheetId;

            //create the update request for cells from the first row
            var userEnteredFormat = new CellFormat()
            {
                BackgroundColor = new Color()
                {
                    Blue  = (float)0.933,
                    Red   = (float)0.393,
                    Green = (float)0.586,
                },
                HorizontalAlignment = "CENTER",
                TextFormat          = new TextFormat()
                {
                    FontSize        = 24,
                    ForegroundColor = new Color()
                    {
                        Blue  = 1,
                        Red   = 1,
                        Green = 1,
                    }
                },
            };

            var updateCellsRequest = new Request()
            {
                RepeatCell = new RepeatCellRequest()
                {
                    Range = new GridRange()
                    {
                        SheetId       = sheetId,
                        StartRowIndex = 0,
                        EndRowIndex   = 1
                    },
                    Cell = new CellData()
                    {
                        UserEnteredFormat = userEnteredFormat
                    },
                    Fields = "UserEnteredFormat(BackgroundColor,TextFormat)"
                }
            };

            //// TODO: Assign values to desired properties of `requestBody`:
            bussr.Requests = new List <Request>();
            bussr.Requests.Add(updateCellsRequest);

            SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(bussr, spreadSheetId);

            //// To execute asynchronously in an async method, replace `request.Execute()` as shown:
            Data.BatchUpdateSpreadsheetResponse response = request.Execute();
            //Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();

            // TODO: Change code below to process the `response` object:
            //Console.WriteLine(JsonConvert.SerializeObject(response));
        }