Ejemplo n.º 1
0
        // Inserts rows into the existing spreadsheet.
        internal static SpreadsheetsResource.BatchUpdateRequest GetInsertRowsRequest(SheetsService sheetsService, string spreadsheetId, int?sheetId, int toBeInsertedTopRowIndex, int toBeInsertedRowCount)
        {
            // Build insert dimensions partial request...
            InsertDimensionRequest request = new InsertDimensionRequest();

            request.Range            = new DimensionRange();
            request.Range.SheetId    = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
            request.Range.Dimension  = "ROWS";
            request.Range.StartIndex = toBeInsertedTopRowIndex;
            request.Range.EndIndex   = toBeInsertedTopRowIndex + toBeInsertedRowCount;
            if (toBeInsertedTopRowIndex > 0)
            {
                request.InheritFromBefore = true;
            }

            // 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 {
                InsertDimension = request
            });

            // Set ResponseIncludeGridData flag to false to increase performance.
            batchUpdateSpreadsheetRequest.ResponseIncludeGridData = false;

            // Construct and return appropriate batch update request based on the provided request body and spreadsheet id.
            return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId));
        }
Ejemplo n.º 2
0
    public static void UpdateSheetAtParticularIndex(string newSheetName, string sheetName, int index, IUoW uow, int clientId, string sheetId, int apiKeySecretId = 20, int accountTypeId = 27)
    {
        try
        {
            var         service       = GetGoogleSpreadSheetService(uow, clientId, sheetId, apiKeySecretId, accountTypeId);
            Spreadsheet spreadsheet   = service.Spreadsheets.Get(sheetId).Execute();
            Sheet       sheet         = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
            int         curretSheetId = (int)sheet.Properties.SheetId;
            var         dd            = sheet.Data;
            //getting all data from sheet
            var valTest   = service.Spreadsheets.Values.Get(sheetId, $"{sheetName}!A2:D2").Execute();
            var valvalues = valTest.Values;
            //adding to specific row
            InsertRangeRequest insRow = new InsertRangeRequest();
            insRow.Range = new GridRange()
            {
                SheetId       = curretSheetId,
                StartRowIndex = 3,
                EndRowIndex   = 4,
            };
            insRow.ShiftDimension = "ROWS";
            //sorting
            SortRangeRequest sorting = new SortRangeRequest();
            sorting.Range = new GridRange()
            {
                SheetId          = curretSheetId,
                StartColumnIndex = 0,       // sorted by firstcolumn for all data after 1st row
                StartRowIndex    = 1
            };
            sorting.SortSpecs = new List <SortSpec>
            {
                new SortSpec
                {
                    SortOrder = "DESCENDING"
                }
            };
            //
            InsertDimensionRequest insertRow = new InsertDimensionRequest();
            insertRow.Range = new DimensionRange()
            {
                SheetId    = curretSheetId,
                Dimension  = "ROWS",
                StartIndex = 1,    // 0 based
                EndIndex   = 2
            };
            var oblistt = new List <object>()
            {
                "Helloins", "This4ins", "was4ins", "insertd4ins"
            };
            PasteDataRequest data = new PasteDataRequest
            {
                Data      = string.Join(",", oblistt),
                Delimiter = ",",
                // data gets inserted form this cordinate point( i.e column and row) index and to the right
                Coordinate = new GridCoordinate
                {
                    ColumnIndex = 0,  // 0 based Col A is 0, col B is 1 and so on
                    RowIndex    = 2,  //
                    SheetId     = curretSheetId
                },
            };

            BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest()
            {
                Requests = new List <Request>
                {
                    //new Request{ InsertDimension = insertRow },
                    //new Request{ PasteData = data },
                    //new Request { InsertRange = insRow},
                    new Request {
                        SortRange = sorting
                    }
                }
            };

            BatchUpdateSpreadsheetResponse response1 = service.Spreadsheets.BatchUpdate(r, sheetId).Execute();
            //adding data to sheet
            var valueRange = new ValueRange();

            var oblist = new List <object>()
            {
                "Hello466", "This466", "was466", "insertd466"
            };
            valueRange.Values = new List <IList <object> > {
                oblist
            };

            var appendRequest = service.Spreadsheets.Values.Append(valueRange, sheetId, $"{sheetName}!A:D");
            //appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
            appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var appendReponse = appendRequest.Execute();
            //updating data to sheet
            var valueRangeUpdate = new ValueRange();

            var oblistUpdate = new List <object>()
            {
                "Hello4uqq", "This4uqq", "was4uqq", "insertd4uqq"
            };
            valueRangeUpdate.Values = new List <IList <object> > {
                oblistUpdate
            };

            var updateRequest = service.Spreadsheets.Values.Update(valueRangeUpdate, sheetId, $"{sheetName}!A10:D510");

            updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
            var updateReponse = updateRequest.Execute();

            //deleting row from sheet
            var range       = $"{sheetName}!A7:G7";
            var requestBody = new ClearValuesRequest();

            var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, sheetId, range);
            var deleteReponse = deleteRequest.Execute();


            // update new Sheet
            var updateSheetRequest = new UpdateSheetPropertiesRequest();
            updateSheetRequest.Properties         = new SheetProperties();
            updateSheetRequest.Properties.Title   = sheetName;  //Sheet1
            updateSheetRequest.Properties.Index   = index;
            updateSheetRequest.Properties.SheetId = curretSheetId;
            updateSheetRequest.Fields             = "Index,Title";//fields that needs to be updated.* means all fields from that sheet

            BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
            batchUpdateSpreadsheetRequest.Requests = new List <Request>();
            batchUpdateSpreadsheetRequest.Requests.Add(new Request
            {
                UpdateSheetProperties = updateSheetRequest
            });

            var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, sheetId);
            var response           = batchUpdateRequest.Execute();
        }
        catch (Exception ex)
        {
            throw;
        }
    }