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();
}
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;
}
}
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);
}
}
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);
}
}
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();
}
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();
}
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);
}
}
