public void DeleteColumn(string ClientId, string ClientSecret, string spreadSheetID, int sheetID, int colIndex)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);

            var request = new Request
            {
                DeleteDimension = new DeleteDimensionRequest
                {
                    Range = new DimensionRange
                    {
                        SheetId    = sheetID,
                        Dimension  = "COLUMNS",
                        StartIndex = colIndex,
                        EndIndex   = colIndex + 1
                    }
                }
            };

            var deleteColRequest = new BatchUpdateSpreadsheetRequest {
                Requests = new List <Request> {
                    request
                }
            };

            sheetsService.Spreadsheets.BatchUpdate(deleteColRequest, spreadSheetID).Execute();
        }
예제 #2
0
        public void RenameSpreadsheet(string ClientId, string ClientSecret, string spreadSheetID, string newName, out bool Success)
        {
            try
            {
                var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);

                var request = new Request
                {
                    UpdateSpreadsheetProperties = new UpdateSpreadsheetPropertiesRequest
                    {
                        Properties = new SpreadsheetProperties()
                        {
                            Title = newName
                        },
                        Fields = "Title"
                    }
                };

                var RenameSheetRequest = new BatchUpdateSpreadsheetRequest {
                    Requests = new List <Request> {
                        request
                    }
                };

                sheetsService.Spreadsheets.BatchUpdate(RenameSheetRequest, spreadSheetID).Execute();

                Success = true;
            }
            catch
            {
                Success = false;
            }
        }
예제 #3
0
        public DataTable GetAllSheets(string ClientID, string ClientSecret, string spreadSheetID, out bool Success)
        {
            GoogleAPI  google     = new GoogleAPI();
            var        table      = new DataTable();
            DataColumn SheetTitle = table.Columns.Add("Sheet Title");

            SheetTitle.DataType = System.Type.GetType("System.String");
            DataColumn SheetID = table.Columns.Add("Sheet ID");

            SheetID.DataType = System.Type.GetType("System.Int32");
            try
            {
                var sheetsService = GoogleAPI.GetSheetsService(ClientID, ClientSecret);

                var results = sheetsService.Spreadsheets.Get(spreadSheetID).Execute();

                for (int row = 0; row < results.Sheets.Count; row++)
                {
                    DataRow dataRow = table.NewRow();
                    dataRow["Sheet ID"]    = results.Sheets[row].Properties.SheetId;
                    dataRow["Sheet Title"] = results.Sheets[row].Properties.Title;
                    table.Rows.Add(dataRow);
                }
                Success = true;
                return(table);
            }
            catch
            {
                Success = false;
                return(table);
            }
        }
예제 #4
0
        public int GetRowIndexbasedonTwoStrings(string ClientID, string ClientSecret, string spreadSheetID, string range, string columnName1, string searchString1, string columnName2, string searchString2, out bool Success)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientID, ClientSecret);
            int rowIndex      = 0;

            // Post request to get range of values in spreadsheet
            var rangeRequest = sheetsService.Spreadsheets.Values.Get(spreadSheetID, range);
            var cellValues   = rangeRequest.Execute().Values;
            var collection   = cellValues.ToList();

            Success = false;

            int col1Index = GetColumnIndex(ClientID, ClientSecret, spreadSheetID, range, columnName1, out bool col1Success);
            int col2Index = GetColumnIndex(ClientID, ClientSecret, spreadSheetID, range, columnName2, out bool col2Success);

            if (cellValues == null)
            {
                Success = false;
                return(0);
            }
            else
            {
                // remove all empty records from List
                // just need to loop through cellValues[0] for column index
                foreach (var row in cellValues)
                {
                    if (row.Count == 0)
                    {
                        collection.Remove(row);
                    }
                }
                foreach (var row in collection)
                {
                    if (row[col1Index].ToString() == searchString1 && row[col2Index].ToString() == searchString2)
                    {
                        Success = true;
                        return(rowIndex);
                    }
                    rowIndex++;
                }
                Success = false;
                return(0);
            }
        }
예제 #5
0
        public SheetProperties CopySheets(string ClientId, string ClientSecret, string destinationSpreadsheetId, string sourceSpreadsheetId, int sourceSheetId)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);

            CopySheetToAnotherSpreadsheetRequest requestBody = new CopySheetToAnotherSpreadsheetRequest();

            requestBody.DestinationSpreadsheetId = destinationSpreadsheetId;

            SpreadsheetsResource.SheetsResource.CopyToRequest request = sheetsService.Spreadsheets.Sheets.CopyTo(requestBody, sourceSpreadsheetId, sourceSheetId);

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

            // Data.SheetProperties response = await request.ExecuteAsync();

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

            return(response);
        }
        public void DeleteSheet(string ClientId, string ClientSecret, string spreadSheetID, int sheetID)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);

            var request = new Request
            {
                DeleteSheet = new DeleteSheetRequest
                {
                    SheetId = sheetID
                }
            };

            var deletesheetRequest = new BatchUpdateSpreadsheetRequest {
                Requests = new List <Request> {
                    request
                }
            };

            //execute request
            sheetsService.Spreadsheets.BatchUpdate(deletesheetRequest, spreadSheetID).Execute();
        }
예제 #7
0
        public void AddSheet(string ClientId, string ClientSecret, string spreadSheetID, string sheetName)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);

            // Add new Sheet
            var addSheetRequest = new AddSheetRequest();

            addSheetRequest.Properties       = new SheetProperties();
            addSheetRequest.Properties.Title = sheetName;
            BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();

            batchUpdateSpreadsheetRequest.Requests = new List <Request>();
            batchUpdateSpreadsheetRequest.Requests.Add(new Request
            {
                AddSheet = addSheetRequest
            });

            var batchUpdateRequest =
                sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadSheetID);

            batchUpdateRequest.Execute();
        }
예제 #8
0
        public int GetColumnIndex(string ClientID, string ClientSecret, string spreadSheetID, string range, string searchString, out bool Success)
        {
            var sheetsService = GoogleAPI.GetSheetsService(ClientID, ClientSecret);
            int colIndex      = 0;
            // Post request to get range of values in spreadsheet
            var rangeRequest = sheetsService.Spreadsheets.Values.Get(spreadSheetID, range);
            var cellValues   = rangeRequest.Execute().Values;
            var collection   = cellValues.ToList();

            if (cellValues == null)
            {
                Success = false;
                return(0);
            }
            else
            {
                // remove all empty records from List
                // just need to loop through cellValues[0] for column index
                foreach (var row in cellValues)
                {
                    if (row.Count == 0)
                    {
                        collection.Remove(row);
                    }
                }
                for (colIndex = 0; colIndex <= collection[0].Count; colIndex++)
                {
                    if (collection[0][colIndex].ToString() == searchString)
                    {
                        break;
                    }
                }
                Success = true;
                return(colIndex);
            }
        }