/// <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);
}
// Adjust the pixelSize rows height.
internal static SpreadsheetsResource.BatchUpdateRequest GetAdjustRowsHeightDimensionRequest(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));
}
// Create appropriate partial request of type UpdateDimensionPropertiesRequest, where rows are considered as the dimension to adjust size.
UpdateDimensionPropertiesRequest updateDimensionPropertiesRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustRowsHeightDimensionPartialRequest(spreadsheetId, sheetId, topRowIndex, rowsToAdjustHeightCount, rowHeightPixelsCount);
// 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 {
UpdateDimensionProperties = updateDimensionPropertiesRequest
});
// 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));
}
public async Task DeleteIncidentsAsync(IList <BehaviourIncident> incidents)
{
if (incidents.Count == 0)
{
return;
}
var rowsToDelete = incidents.Select(o => o.SpreadsheetRow.Value).OrderByDescending(o => o);
var sheetId = await GetExpectationsSheetId();
var requestBody = new BatchUpdateSpreadsheetRequest {
Requests = rowsToDelete.Select(o => new Request {
DeleteDimension = new DeleteDimensionRequest {
Range = new DimensionRange {
SheetId = sheetId,
Dimension = DIMENSION_ROWS,
StartIndex = o - 1, // Zero-based index
EndIndex = o // Exclusive
}
}
}).ToList()
};
var request = Resource.BatchUpdate(requestBody, SpreadsheetId);
await Execute(request);
}
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();
}
// Add
internal static SpreadsheetsResource.BatchUpdateRequest GetAddSheetRequest(SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount)
{
// Create new add sheet partial request
AddSheetRequest addSheetRequest = new AddSheetRequest();
// Create and assign instance of SheetProperties class into the addSheetRequest.
addSheetRequest.Properties = new SheetProperties();
// Set sheet title id.
addSheetRequest.Properties.Title = sheetTitleId;
// Create new sheet grid properties, and set number of columns and rows according the provided columnCount and row count property.
addSheetRequest.Properties.GridProperties = new GridProperties();
addSheetRequest.Properties.GridProperties.ColumnCount = columnCount;
addSheetRequest.Properties.GridProperties.RowCount = rowCount;
// Create new instance of BatchUpdateSpreadsheetRequest, data of the to be returned instance of SpreadsheetsResource.BatchUpdateRequest ..
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequestData = new BatchUpdateSpreadsheetRequest();
// .. initialize new list of partial requests ..
batchUpdateSpreadsheetRequestData.Requests = new List <Request>();
// .. and add sheet partial request to it.
batchUpdateSpreadsheetRequestData.Requests.Add(new Request {
AddSheet = addSheetRequest
});
// Set ResponseIncludeGridData flag to false to increase performance.
batchUpdateSpreadsheetRequestData.ResponseIncludeGridData = false;
// Construct and return SpreadsheetsResource.BatchUpdateRequest.
return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequestData, spreadsheetId));
}
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();
}
public void Update(BranchInfo[] branches)
{
var sheetId = (int)DateTimeOffset.UtcNow.ToUnixTimeSeconds();
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request {
AddSheet = GetAddSheetRequest(sheetId, branches.Length)
},
new Request {
UpdateCells = GetUpdateCellsRequest(sheetId, branches)
},
}
};
GetUpdateDimensionPropertiesRequest(sheetId)
.ForEach(r => batchUpdateSpreadsheetRequest.Requests.Add(new Request {
UpdateDimensionProperties = r
}));
var credential = LoadCredentials();
using (var service = new SheetsService(GetInitializer(credential)))
{
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadSheetId);
batchUpdateRequest.Execute();
}
}
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 static void CreateSheet(string sheetName)
{
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = sheetName;
var updateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>()
};
updateSpreadsheetRequest.Requests.Add(
new Request
{
AddSheet = addSheetRequest,
});
_service.Spreadsheets.BatchUpdate(updateSpreadsheetRequest, _spreadsheetId).Execute();
var valueRange = new ValueRange()
{
Values = new List <IList <object> >()
{
new List <object>()
{
"Сервер", "База данных", "Размер в ГБ", "Дата обновления"
}
}
};
var updateRequest = _service.Spreadsheets.Values.Update(valueRange, _spreadsheetId, $"{sheetName}!A1:4");
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
updateRequest.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;
}
}
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();
}
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();
}
/// <summary>
/// adds blank tab specified by SheetName to spreadsheet
/// </summary>
/// <param name="SpreadSheetID"></param>
/// <param name="SheetName"></param>
public static void AddTabToSpreadSheet(string SpreadSheetID, string SheetName)
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
// 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 =
service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SpreadSheetID);
batchUpdateRequest.Execute();
}
public BatchUpdateSpreadsheetResponse CreateSheet(string sheetName)
{
String spreadsheetId = "13CwpWvOWW1B-lyB_kzzvcCeQPukM5AgGoZ32l7bl5e4";
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 =
Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
try
{
var res = batchUpdateRequest.Execute();
return(res);
}
catch { }
return(null);
}
/// <summary>
/// Создание новой таблицы
/// </summary>
/// <param name="service"></param>
/// <param name="sheetName"></param>
/// <param name="SpreadsheetId"></param>
public static void CreateNewSheet(SheetsService service, string sheetName, string SpreadsheetId)
{
var addShettRequest = new Request
{
//Добавляет новый лист.
AddSheet = new AddSheetRequest
{
// Свойства листа.
Properties = new SheetProperties
{
Title = sheetName
}
}
};
List <Request> requests = new List <Request> {
addShettRequest
};
//обновление любого аспекта электронной таблицы
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheet = new BatchUpdateSpreadsheetRequest();
//Список обновлений, которые необходимо применить к электронной таблице.
batchUpdateSpreadsheet.Requests = requests;
//Применяет одно или несколько обновлений к электронной таблице. Каждый запрос проверяется заранее применяется.
//Если какой-либо запрос не является действительным, то весь запрос не будет выполнен.
service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheet, SpreadsheetId).Execute();
}
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();
}
public bool CreateNewSheet(string sheetName)
{
var addSheetRequest = new Request
{
AddSheet = new AddSheetRequest
{
Properties = new SheetProperties
{
Title = sheetName
}
}
};
var requests = new List <Request> {
addSheetRequest
};
var batchUpdate = new BatchUpdateSpreadsheetRequest();
batchUpdate.Requests = requests;
BatchUpdateSpreadsheetResponse response;
try { response = Service.Spreadsheets.BatchUpdate(batchUpdate, spreadsheetId).Execute(); }
catch (Exception e)
{
Console.WriteLine("CreateNewSheet method exception:\n" + e.Message);
response = null;
}
return(response != null);
}
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 void DeletRow()
{
List <Request> request = new List <Request>();
request.Add(
new Request
{
DeleteDimension = new DeleteDimensionRequest
{
Range = new DimensionRange
{
SheetId = sheet,
Dimension = "ROWS",
StartIndex = int.Parse(listBox_spis.SelectedIndex.ToString()) + 2,
EndIndex = int.Parse(listBox_spis.SelectedIndex.ToString()) + 3
}
}
}
);
BatchUpdateSpreadsheetRequest busr = new BatchUpdateSpreadsheetRequest {
Requests = request
};
service.Spreadsheets.BatchUpdate(busr, SpredsheetsID).Execute();
MessageBox.Show("Выбранный ученик удален!");
}
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 static void Clean(SheetsService service, int count)
{
List <Request> request = new List <Request>();
request.Add(
new Request
{
DeleteDimension = new DeleteDimensionRequest
{
Range = new DimensionRange
{
SheetId = Zayav,
Dimension = "ROWS",
StartIndex = 13,
EndIndex = 13 + count
}
}
}
);
BatchUpdateSpreadsheetRequest busr = new BatchUpdateSpreadsheetRequest {
Requests = request
};
service.Spreadsheets.BatchUpdate(busr, SpredsheetsID1).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);
}
private BatchUpdateSpreadsheetRequest GetBatchUpdate()
{
var batch = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request {
DeleteSheet = new DeleteSheetRequest {
SheetId = 0
}
},
new Request
{
UpdateCells = new UpdateCellsRequest
{
Fields = "*",
Start = new GridCoordinate {
SheetId = _sheetId
},
Rows = GetRows()
},
}
}
};
batch.Requests.AddRange(GetMerges());
batch.Requests.AddRange(GetColumnWidths());
return(batch);
}
public string UpdateTab(string guildname)
{
GoogleConnection();
log.Info("Google Sheets connection successful.");
// Add new Sheet
string sheetName = string.Format("{0}/{1}/{2} {3}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, guildname);
_sheetname = sheetName;
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);
BatchUpdateSpreadsheetResponse response = batchUpdateRequest.Execute();
return(JsonConvert.SerializeObject(response));
}
// 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));
}
public override void Export(IEnumerable <Flat> flats)
{
var service = CreateService();
var latestSheetProperties = GetLatestSheetProperties(service);
var newSheetInfo = GetNewSheetInfo();
var duplicateSheetRequest = GetDuplicateRequest(newSheetInfo, latestSheetProperties);
var updateCellsRequests = GetUpdateRequests(newSheetInfo, latestSheetProperties, flats);
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>()
};
batchUpdateSpreadsheetRequest.Requests.Add(new Request {
DuplicateSheet = duplicateSheetRequest
});
foreach (var request in updateCellsRequests)
{
batchUpdateSpreadsheetRequest.Requests.Add(new Request {
UpdateCells = request
});
}
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SpreadsheetId);
batchUpdateRequest.Execute();
}
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();
}
/// <summary>
/// 創建一張工作表
/// </summary>
private void CreateV4Sheets()
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "Get Google SheetData with Google Sheets API",
});
// 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 async Task <BatchUpdateSpreadsheetResponse> DeleteRowsAsync(int tabId, int startRow, int endRow = -1)
{
if (endRow < 0)
{
endRow = startRow + 1;
}
Request request = new Request
{
DeleteDimension = new DeleteDimensionRequest
{
Range = new DimensionRange
{
Dimension = "ROWS",
SheetId = tabId,
StartIndex = startRow,
EndIndex = endRow
}
}
};
BatchUpdateSpreadsheetRequest batchRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>()
};
batchRequest.Requests.Add(request);
return(await _service.Spreadsheets.BatchUpdate(batchRequest, SheetId()).ExecuteAsync());
}
/// <summary>
/// Applies the data and format of a list of rows to a range.
/// </summary>
/// <param name="spreadsheetId">The id of the sheets doc</param>
/// <param name="tabName">The name of the tab to write to</param>
/// <param name="startCell">The top-left cell in the range to be written to</param>
/// <param name="endCell">The bottom-right cell in the range to be written to</param>
/// <param name="rows">The list of rows to be written</param>
/// <returns></returns>
public static bool updateCells(string spreadsheetId, string tabName, string startCell, string endCell, List <RowData> rows)
{
if (m_shouldRun == false)
{
return(false);
}
Point startPoint = convertCellToPoint(startCell);
Point endPoint = convertCellToPoint(endCell);
GridRange range = new GridRange();
range.SheetId = getTabIndex(spreadsheetId, tabName);
range.StartColumnIndex = startPoint.X;
range.StartRowIndex = startPoint.Y;
range.EndColumnIndex = endPoint.X + 1;
range.EndRowIndex = endPoint.Y + 1;
Request updateCells = requestUpdateCells(spreadsheetId, tabName, rows, range, "*");
Request autoSize = requestDimensionAutoSize(spreadsheetId, tabName, 0);
BatchUpdateSpreadsheetRequest batchRequest = new BatchUpdateSpreadsheetRequest();
batchRequest.Requests = new List <Request>();
batchRequest.Requests.Add(autoSize);
batchRequest.Requests.Add(updateCells);
return(ExecuteBatchRequest(batchRequest, spreadsheetId));
}
