static void DeleteRowByIndexInSheet(string spreadsheetId, string tabName, int rowIndex)
{
if (rowIndex < 0)
{
return;
}
int? sheetId = GetTabId(spreadsheetId, tabName);
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = sheetId,
Dimension = "ROWS",
StartIndex = Convert.ToInt32(rowIndex),
EndIndex = Convert.ToInt32(rowIndex + 1)
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest deleteRequest = new BatchUpdateSpreadsheetRequest();
deleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest batchUpdate = service.Spreadsheets.BatchUpdate(deleteRequest, spreadsheetId);
batchUpdate.Execute();
}
internal static void DeleteRow(SheetsService service, string spreadsheetId, int row)
{
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = 593128798,
Dimension = "ROWS",
StartIndex = row - 1,
EndIndex = row
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, spreadsheetId);
Deletion.Execute();
}
public async Task <BatchUpdateSpreadsheetResponse> RenameSheet(string spreadsheetId, int sheetId, string oldName, string newName)
{
var req = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request
{
UpdateSheetProperties = new UpdateSheetPropertiesRequest
{
Properties = new SheetProperties
{
SheetId = sheetId,
Title = newName
}
}
}
}
};
SpreadsheetsResource.BatchUpdateRequest request = new SpreadsheetsResource.BatchUpdateRequest(_sheetsService, req, spreadsheetId);
var response = await request.ExecuteAsync();
return(response);
}
public void AddNewRow(string spreadsheetId)
{
Request RequestBody = new Request()
{
InsertDimension = new InsertDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = 0,
Dimension = "ROWS",
StartIndex = 0,
EndIndex = 10
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest insertRequest = new BatchUpdateSpreadsheetRequest();
insertRequest.Requests = RequestContainer;
var insert = new SpreadsheetsResource.BatchUpdateRequest(_sheetsService, insertRequest, spreadsheetId);
insert.Execute();
}
public async Task FormatUpdate(string spreadsheetId, RangePosition range, CellFormat userEnteredFormat)
{
//get sheet id by sheet name
int sheetId = await getSheetID(spreadsheetId, range.TableName);
//define cell color
BatchUpdateSpreadsheetRequest bussr = new BatchUpdateSpreadsheetRequest();
//create the update request for cells from the first row
Request updateCellsRequest = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = range.GetGridRange(sheetId),
Cell = new CellData()
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(BackgroundColor,TextFormat)"
}
};
bussr.Requests = new List <Request>();
bussr.Requests.Add(updateCellsRequest);
SpreadsheetsResource.BatchUpdateRequest bur = _service.Spreadsheets.BatchUpdate(bussr, spreadsheetId);
await autoAuthorize(bur);
}
private async Task CommitSignupRow(Sheet sheetToUpdate, RowData rowToUpdate, int rowIndex)
{
var sheetUpdates = new BatchUpdateSpreadsheetRequest();
sheetUpdates.Requests = new List <Request>()
{
new Request()
{
UpdateCells = new UpdateCellsRequest()
{
Start = new GridCoordinate()
{
SheetId = sheetToUpdate.Properties.SheetId, RowIndex = rowIndex, ColumnIndex = 0
},
Fields = "*",
Rows = new List <RowData>()
{
rowToUpdate
}
}
}
};
var updateRequest = new SpreadsheetsResource.BatchUpdateRequest(this.SheetsService, sheetUpdates, HeroicSheetsId);
await updateRequest.ExecuteAsync();
}
private void ClearAllValuesFromSheet(string spreadsheetId, int sheetId)
{
Request RequestBody = new Request()
{
UpdateCells = new UpdateCellsRequest()
{
Range = new GridRange()
{
SheetId = sheetId
},
Fields = "*"
}
};
var RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
var clearRequest = new BatchUpdateSpreadsheetRequest();
clearRequest.Requests = RequestContainer;
var batchUpdateRequest = new SpreadsheetsResource.BatchUpdateRequest(_sheetsService, clearRequest, spreadsheetId);
batchUpdateRequest.Execute();
}
/// <summary>
/// Sends a batch request to google.
/// </summary>
/// <param name="request">The batchrequest to send</param>
/// <param name="spreadsheetId">The id of the sheet doc to send the request for</param>
/// <returns></returns>
private static bool ExecuteBatchRequest(BatchUpdateSpreadsheetRequest request, string spreadsheetId)
{
SpreadsheetsResource.BatchUpdateRequest finalRequest = service.Spreadsheets.BatchUpdate(request, spreadsheetId);
BatchUpdateSpreadsheetResponse response = finalRequest.Execute();
return(true);
}
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();
}
private void Delete(int startIndex, int endIndex)
{
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = 0,
Dimension = "ROWS",
StartIndex = Convert.ToInt32(startIndex),
EndIndex = Convert.ToInt32(endIndex)
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(_sheetsService, DeleteRequest, _spreadsheetId);
Deletion.Execute();
}
// Removes specified rows range from the existing sheet.
internal static async Task RemoveRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int?sheetId, int toBeRemovedTopRowIndex, int toBeRemovedRowCount)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate remove rows batch request.
SpreadsheetsResource.BatchUpdateRequest removeRowsRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveRowsRequest(sheetsService, spreadsheetId, sheetId, toBeRemovedTopRowIndex, toBeRemovedRowCount);
// Execute remove rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(removeRowsRequest);
}
// Add new sheet into the existing spreadsheet specified with provided spreadsheetId.
internal static async Task AddSheetAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate add sheet BatchUpdateRequest.
SpreadsheetsResource.BatchUpdateRequest addSheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSheetRequest(sheetsService, spreadsheetId, sheetTitleId, columnCount, rowCount);
// Execute add sheet request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(addSheetRequest);
}
// Adjust multiple rows ranges height dimensions.
internal static async Task AdjustMultipleRowsRangesHeightDimensionsAsync(this SheetsService sheetsService, string commonSpreadsheetId, IList <Tuple <int?, int, int, int> > rowsRangesDiemansionsAdjustmentBlueprint)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate adjust multiple rows ranges height dimension request
SpreadsheetsResource.BatchUpdateRequest adjustMultipleRowsRangesHeightDimensionsRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustMultipleRowsRangesHeightDimensionsRequest(sheetsService, commonSpreadsheetId, rowsRangesDiemansionsAdjustmentBlueprint);
// Execute adjust multiple spreadsheet rows ranges height dimensions sheet request in safe asynchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(adjustMultipleRowsRangesHeightDimensionsRequest);
}
// Adjust multiple columns ranges width dimensions.
internal static void AdjustMultipleColumnsRangesWidthDimensionsSync(this SheetsService sheetsService, string commonSpreadsheetId, IList <Tuple <int?, int, int, int> > columnsRangesDiemansionsAdjustmentBlueprint)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain appropriate adjust multiple columns ranges width dimension request
SpreadsheetsResource.BatchUpdateRequest adjustMultipleColumnsRangesWidthDimansionsRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustMultipleColumnsRangesWidthDimensionsRequest(sheetsService, commonSpreadsheetId, columnsRangesDiemansionsAdjustmentBlueprint);
// Execute adjust multiple spreadsheet columns ranges with dimensions sheet request in safe synchronous manner.
RequestsExecutor.SafeExecuteSync <BatchUpdateSpreadsheetResponse>(adjustMultipleColumnsRangesWidthDimansionsRequest);
}
public void DeleteInvoice(SpiceModel inv)
{
int row = 0;
var range = $"{dataSheet}!A:D";
int j = 0;
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(SpreadsheetId, range);
var response = request.Execute();
IList <IList <object> > values = response.Values;
if (values != null && values.Count > 0)
{
foreach (var invoicerow in values)
{
j++;
if (j > 1)
{
var Id = Int32.Parse(invoicerow[0].ToString());
if (Id == inv.Id)
{
row = j - 1;
}
}
}
}
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = 461012880,
Dimension = "ROWS",
StartIndex = row,
EndIndex = row + 1
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, SpreadsheetId);
Deletion.Execute();
}
public static void Delete(string ticketId, string spreadSheetId)
{
int row = 0;
var range = $"{sheet}!A:F";
int j = 0;
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadSheetId, range);
var response = request.Execute();
IList <IList <object> > values = response.Values;
if (values != null && values.Count > 0)
{
foreach (var user in values)
{
j++;
if (j > 1)
{
string ticket = (string)user[0];
if (ticket.Equals(ticketId))
{
row = j - 1;
}
}
}
}
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = 0,
Dimension = "ROWS",
StartIndex = row,
EndIndex = row + 1
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, spreadSheetId);
Deletion.Execute();
}
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 static void Layout()
{
Request clearBoldRequest = new Request();
clearBoldRequest.RepeatCell = new RepeatCellRequest();
clearBoldRequest.RepeatCell.Fields = "userEnteredFormat(textFormat)";
clearBoldRequest.RepeatCell.Range = new GridRange {
SheetId = planilhas.Sheets[0].Properties.SheetId,
StartColumnIndex = 1,
EndColumnIndex = 18,
StartRowIndex = 3,
EndRowIndex = linha_total_savings
};
clearBoldRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
TextFormat = new TextFormat {
Bold = false
}
}
};
Request boldRequest = new Request();
boldRequest.RepeatCell = new RepeatCellRequest();
boldRequest.RepeatCell.Fields = "userEnteredFormat(textFormat)";
boldRequest.RepeatCell.Range = new GridRange {
SheetId = planilhas.Sheets[0].Properties.SheetId,
StartColumnIndex = 1,
EndColumnIndex = 18,
StartRowIndex = limite,
EndRowIndex = linha_total_savings
};
boldRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
TextFormat = new TextFormat {
Bold = true
}
}
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(clearBoldRequest);
batch.Requests.Add(boldRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, idPlanilha);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
// STATUS [ August 3, 2019 ] : this works
// STEP 4: execute format changes in sheet
private void ExecuteFormatChange(Request updateCellsRequest, SheetsService service)
{
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
updateCellsRequest,
},
};
SpreadsheetsResource.BatchUpdateRequest batchUpdateRequest = service.Spreadsheets.BatchUpdate(
batchUpdateSpreadsheetRequest,
_spreadSheetId
);
batchUpdateRequest.Execute();
}
// Adjust columns range width dimensions.
internal static void AdjustColumnsWidthDimensionSync(this SheetsService sheetsService, string spreadsheetId, int?sheetId, int leftColumnIndex, int columnsToAdjustWidthCount, int columnWidthPixelsCount)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain adjust columns width dimension request.
SpreadsheetsResource.BatchUpdateRequest adjustColumnsWidthDimensionRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustColumnsWidthDimensionRequest(sheetsService, spreadsheetId, sheetId, leftColumnIndex, columnsToAdjustWidthCount, columnWidthPixelsCount);
// Execute add sheet request in safe synchronous manner.
RequestsExecutor.SafeExecuteSync <BatchUpdateSpreadsheetResponse>(adjustColumnsWidthDimensionRequest);
}
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));
}
// Remove existing, (not-last in a spreadsheet) sheet from the specified existing spreadsheet.
internal static async Task RemoveSheetAsync(this SheetsService sheetsService, string spreadsheetId, int?sheetId)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate remove sheet BatchUpdateRequest.
SpreadsheetsResource.BatchUpdateRequest removeSheetRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveSheetRequest(sheetsService, spreadsheetId, sheetId);
// Execute remove sheet request in safe asynchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(removeSheetRequest);
}
// Adjust rows range height dimensions.
internal static async Task AdjustRowsHeightDimensionAsync(this SheetsService sheetsService, string spreadsheetId, int?sheetId, int topRowIndex, int rowsToAdjustHeightCount, int rowHeightPixelsCount)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain adjust columns width dimension request.
SpreadsheetsResource.BatchUpdateRequest adjustRowsHeightDimensionRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustRowsHeightDimensionRequest(sheetsService, spreadsheetId, sheetId, topRowIndex, rowsToAdjustHeightCount, rowHeightPixelsCount);
// Execute add sheet request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(adjustRowsHeightDimensionRequest);
}
// removes or clears a row depending on the deleteRows switch setting
private void DeleteEntry(string row)
{
SheetsService service = GetServiceAccount();
if (deleteRows)
{
/* this code deletes the record (row) completely */
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = mySheetID,
Dimension = "ROWS",
StartIndex = Convert.ToInt32(row) - 1,
EndIndex = Convert.ToInt32(row)
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
// service.Spreadsheets.BatchUpdate(DeleteRequest, spreadsheetId);
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, spreadsheetId);
Deletion.Execute();
/* end of delete code */
}
else
{
/* this code clears the entry */
var range = mySheet + "!A" + row + ":F" + row;
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
var deleteReponse = deleteRequest.Execute();
}
service.Dispose();
}
// Inserts rows range into the existing sheet
internal static async Task InsertRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int?sheetId, int columnCount, int toBeInsertedTopRowIndex, int toBeInsertedRowCount, IList <IList <object> > newRowsData)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate insert row batch request.
SpreadsheetsResource.BatchUpdateRequest insertRowsRequest = GoogleServicesExtensionsRequestsFactory.GetInsertRowsRequest(sheetsService, spreadsheetId, sheetId, toBeInsertedTopRowIndex, toBeInsertedRowCount);
// Execute insert sheet rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(insertRowsRequest);
// If new rows data haven't been provided
if (!(newRowsData is null))
{
// Obtain new rows range string.
string newRowsRangeString = RangeTranslator.GetRangeString(sheetTitleId, 0, toBeInsertedTopRowIndex, columnCount, toBeInsertedRowCount);
// Fills inserted rows with provided data.
await sheetsService.UpdateRangeDataAsync(spreadsheetId, newRowsRangeString, newRowsData);
}
}
public void Delete <T>(int id)
{
Backup <T>();
int sheetId = GetSheetIdByNameClass(typeof(T).Name);
//DELETE THIS ROW
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = sheetId,
Dimension = "ROWS",
StartIndex = id - 1,
EndIndex = id
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(Service, DeleteRequest, SpreadsheetId);
Deletion.Execute();
//var range = $"{typeof(T).Name}!A{id}:F{id}";
//var requestBody = new ClearValuesRequest();
//var deleteRequest = Service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range);
//var deleteReponse = deleteRequest.Execute();
}
/// <summary>
/// 초기화 합니다. spreadsheetId를 변경하려면 초기화 전에 변경해주세요.
/// </summary>
public void Initialize()
{
try
{
//Resize
Request RequestBody = new Request()
{
AddSheet = new AddSheetRequest()
{
Properties = new SheetProperties()
{
SheetId = sheetId,
Title = title,
GridProperties = new GridProperties()
{
RowCount = RowCount,
ColumnCount = 2
},
TabColor = new Color()
{
Red = 1.0f,
Green = 0.3f,
Blue = 0.4f
}
},
}
};
BatchUpdateSpreadsheetRequest CreateRequest = new BatchUpdateSpreadsheetRequest();
CreateRequest.Requests = new List <Request>()
{
RequestBody
};
bool isNew = false;
try
{
lastError = System.Net.HttpStatusCode.OK;
Console.WriteLine("service ready");
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, CreateRequest, spreadsheetId);
Deletion.Execute();
isNew = true;
Console.WriteLine("service start");
}
catch (Google.GoogleApiException e)
{
lastError = e.HttpStatusCode;
if (e.HttpStatusCode == System.Net.HttpStatusCode.NotFound)
{
isInit = false;
return;
}
Console.WriteLine("service error " + e.HttpStatusCode);
if (e.Message.Contains("already exists"))
{
RequestBody = new Request()
{
UpdateSheetProperties = new UpdateSheetPropertiesRequest()
{
Properties = new SheetProperties()
{
SheetId = sheetId,
Title = title,
GridProperties = new GridProperties()
{
RowCount = RowCount,
ColumnCount = 2
},
TabColor = new Color()
{
Red = 1.0f,
Green = 0.3f,
Blue = 0.4f
}
},
Fields = "*"
}
};
}
else if (e.Message.Contains("Google.Apis.Requests.RequestError") && e.Message.Contains(title))
{
var sheet_metadata = service.Spreadsheets.Get(spreadsheetId).Execute();
for (int i = 0; i < sheet_metadata.Sheets.Count; i++)
{
if (sheet_metadata.Sheets[i].Properties.Title == title)
{
sheetId = sheet_metadata.Sheets[i].Properties.SheetId;
break;
}
}
RequestBody = new Request()
{
UpdateSheetProperties = new UpdateSheetPropertiesRequest()
{
Properties = new SheetProperties()
{
SheetId = sheetId,
Title = title,
GridProperties = new GridProperties()
{
RowCount = RowCount,
ColumnCount = 2
},
TabColor = new Color()
{
Red = 1.0f,
Green = 0.3f,
Blue = 0.4f
}
},
Fields = "*"
}
};
}
else
{
throw e;
}
isNew = false;
}
finally
{
if (!isNew)
{
Console.WriteLine("service restart");
CreateRequest = new BatchUpdateSpreadsheetRequest();
CreateRequest.Requests = new List <Request>()
{
RequestBody
};
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, CreateRequest, spreadsheetId);
Deletion.Execute();
}
}
isInit = true;
Console.WriteLine("Init complete");
}
catch
{
Console.WriteLine("Init false");
isInit = false;
}
}
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();
}
private void ResortSpreadsheet(SheetsService service)
{
var sheetId = GetSheetIdForTitle(service, "Pixels");
BatchUpdateSpreadsheetRequest reqbody = new BatchUpdateSpreadsheetRequest();
var sortReq = new Request()
{
SortRange = new SortRangeRequest()
{
Range = new GridRange()
{
StartColumnIndex = 0,
EndColumnIndex = 12,
StartRowIndex = 3,
SheetId = sheetId
},
SortSpecs = new List <SortSpec>()
{
new SortSpec()
{
DimensionIndex = 0,
SortOrder = "ASCENDING"
}
}
}
};
var formatReq = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Cell = new CellData()
{
UserEnteredFormat = new CellFormat()
{
HorizontalAlignment = "CENTER"
}
},
Range = new GridRange()
{
StartColumnIndex = 1,
EndColumnIndex = 2,
StartRowIndex = 3,
SheetId = sheetId
},
Fields = "userEnteredFormat(horizontalAlignment)"
}
};
IList <Request> requests = new List <Request> {
sortReq,
formatReq
};
reqbody.Requests = requests;
SpreadsheetsResource.BatchUpdateRequest sortreq = service.Spreadsheets.BatchUpdate(reqbody, Spreadsheet);
sortreq.Execute();
}
//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();
}
