public (bool hasError, string message) InsertRowAtSpecifiedPlace(string sheetName, string sheetId, int startRowIndex, int endRowIndex)
{
try
{
Spreadsheet spreadsheet = _service.Spreadsheets.Get(sheetId).Execute();
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int curretSheetId = (int)sheet.Properties.SheetId;
InsertRangeRequest insRow = new InsertRangeRequest();
insRow.Range = new GridRange()
{
SheetId = curretSheetId,
StartRowIndex = startRowIndex,
EndRowIndex = endRowIndex,
};
insRow.ShiftDimension = "ROWS";
BatchUpdateSpreadsheetRequest request = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
new Request {
InsertRange = insRow
}
}
};
BatchUpdateSpreadsheetResponse response1 = _service.Spreadsheets.BatchUpdate(request, sheetId).Execute();
return(hasError : false, message : "Row inserted successfully");
}
catch (Exception ex)
{
return(hasError : true, message : ex.Message);
}
}
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));
}
/// <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);
}
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();
}
public void UpdateCellValue(string column, int row, int sheet, string value, string sheetId)
{
var reqs = new BatchUpdateSpreadsheetRequest();
reqs.Requests = new List <Request>();
string[] colNames = new[] { value };
// Create starting coordinate where data would be written to
GridCoordinate gc = new GridCoordinate();
gc.ColumnIndex = GetNumberFromChar(column[0]);
gc.RowIndex = row - 1;
gc.SheetId = sheet; // Your specific sheet ID here
Request rq = new Request();
rq.UpdateCells = new UpdateCellsRequest();
rq.UpdateCells.Start = gc;
rq.UpdateCells.Fields = "*"; // needed by API, throws error if null
// Assigning data to cells
RowData rd = new RowData();
List <CellData> lcd = new List <CellData>();
foreach (String s in colNames)
{
ExtendedValue ev = new ExtendedValue();
ev.StringValue = s;
CellData cd = new CellData();
cd.UserEnteredValue = ev;
lcd.Add(cd);
}
rd.Values = lcd;
// Put cell data into a row
List <RowData> lrd = new List <RowData>();
lrd.Add(rd);
rq.UpdateCells.Rows = lrd;
// It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
reqs.Requests.Add(rq);
// Execute request
BatchUpdateSpreadsheetResponse response = service.Spreadsheets.BatchUpdate(reqs, sheetId).Execute(); // Replace Spreadsheet.SpreadsheetId with your recently created spreadsheet ID
}
public string AutoResize(string guildname)
{
log.Info("Auto resize function started up.");
// Get sheet name
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
{
AutoResizeDimensions = new AutoResizeDimensionsRequest()
{
Dimensions = new DimensionRange()
{
SheetId = Convert.ToInt32(_spreadsheetId),
Dimension = "COLUMNS",
StartIndex = 0,
EndIndex = 2
}
}
});
log.Debug("Executing auto resize.");
var batchUpdateRequest = _sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, _spreadsheetId);
BatchUpdateSpreadsheetResponse response = batchUpdateRequest.Execute();
return(JsonConvert.SerializeObject(response));
}
/// <summary>
/// locks the cells for a sheet, ---Doesnt actually work....
/// </summary>
/// <param name="SpreadSheetID"></param>
/// <param name="SheetName"></param>
public static void AddLockCells(string SpreadSheetID, string SheetName, int startCol, int endCol, int?startRow, int?endRow)
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
// Add locked request parameters
var addLockedCellRequest = new AddProtectedRangeRequest();
addLockedCellRequest.ProtectedRange = new ProtectedRange();
addLockedCellRequest.ProtectedRange.Range = new GridRange();
addLockedCellRequest.ProtectedRange.Range.SheetId = GetSheetIdBySheetName(SpreadSheetID, SheetName);
addLockedCellRequest.ProtectedRange.Range.EndColumnIndex = endCol;
addLockedCellRequest.ProtectedRange.Range.EndRowIndex = endRow;
addLockedCellRequest.ProtectedRange.Range.StartColumnIndex = startCol;
addLockedCellRequest.ProtectedRange.Range.StartRowIndex = startRow;
addLockedCellRequest.ProtectedRange.Editors = new Editors();
addLockedCellRequest.ProtectedRange.Editors.Users = new List <string>();
addLockedCellRequest.ProtectedRange.Editors.Users.Add("fake email");
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddProtectedRange = addLockedCellRequest
});
var batchUpdateRequest =
service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SpreadSheetID);
BatchUpdateSpreadsheetResponse result = batchUpdateRequest.Execute();
int i = 1;
}
//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();
}
public async Task RequestAsync([Summary("The item to request")] string item, [Summary("The amount to request")] int qty = 1, [Summary("The user to request the item for"), RequiredRoleParameter(388954821551456256)] IUser user = null)
{
IUser userInfo = user ?? Context.Message.Author;
string _name = await Program.GetIGNFromUser(userInfo);
// find the actual item name from the store
string _item = "";
ValueRange itemResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPStoreTab);
if (itemResult.Values != null && itemResult.Values.Count > 0)
{
int idx = GoogleSheetsHelper.Instance.IndexInRange(itemResult, item);
if (idx >= 0)
{
_item = itemResult.Values[idx][0].ToString();
}
}
if (string.IsNullOrEmpty(_item))
{
await ReplyAsync($"Unable to find {item} in the DKP store! Use !store to list available items.");
return;
}
// find any existing requests from this user for this item
int reqIdx = -1;
ValueRange reqResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPRequestsTab);
if (reqResult.Values != null && reqResult.Values.Count > 0)
{
reqIdx = GoogleSheetsHelper.Instance.SubIndexInRange(reqResult, _name, 1, _item);
}
int?result = -1;
// remove items from request
if (qty < 0)
{
if (reqIdx < 0)
{
await ReplyAsync($"{_name} is no longer requesting {_item} (and wasn't in the first place)");
return;
}
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out int currentQty);
currentQty += qty;
// if removing items requests in no items remaining, delete this request row
if (currentQty <= 0)
{
GridRange gridRange = Program.GetTabRange(Program.EDKPTabs.Requests);
BatchUpdateSpreadsheetResponse deleteResult = await GoogleSheetsHelper.Instance.DeleteRowsAsync(gridRange.SheetId ?? 0, reqIdx);
result = deleteResult.Replies.Count;
if (result < 0)
{
await ReplyAsync($"Error deleting {_name}'s request!");
}
else
{
await ReplyAsync($"{_name} is no longer requesting {_item}");
}
}
else // otherwise just deduct from the existing request
{
reqResult.Values[reqIdx][2] = currentQty.ToString();
UpdateValuesResponse writeResult = await GoogleSheetsHelper.Instance.UpdateAsync(reqResult.Range, reqResult);
result = writeResult.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error removing {_item} from request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {currentQty.ToString()} {_item}");
}
}
}
else // adding items to request
{
int currentQty = 0;
if (reqIdx > 0)
{
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out currentQty);
}
if (currentQty > 0) // updating request
{
currentQty += qty;
reqResult.Values[reqIdx][2] = currentQty.ToString();
UpdateValuesResponse writeResult = await GoogleSheetsHelper.Instance.UpdateAsync(reqResult.Range, reqResult);
result = writeResult.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error adding {_item} to request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {currentQty.ToString()} {_item}");
}
}
else // new request
{
IList <IList <object> > writeValues = new List <IList <object> > {
new List <object> {
_name, _item, qty.ToString()
}
};
ValueRange writeBody = new ValueRange
{
Values = writeValues
};
AppendValuesResponse writeResult = await GoogleSheetsHelper.Instance.AppendAsync(Config.Global.DKPRequestsTab, writeBody);
result = writeResult.Updates.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error adding {_item} to request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {qty.ToString()} {_item}");
}
}
}
}
private async Task ResetSorting(SpreadsheetsResource spreadsheets)
{
ValueRange response = await spreadsheets.Values.Get(this.spreadsheetId, this.headerRange).ExecuteAsync();
this.LoadHeaderIndexes(response.Values.FirstOrDefault());
List <Request> list = new List <Request>();
ClearBasicFilterRequest clearFilter = new ClearBasicFilterRequest()
{
SheetId = this.dataSheetId
};
list.Add(new Request()
{
ClearBasicFilter = clearFilter
});
SortRangeRequest sortRequest = new SortRangeRequest()
{
Range = new GridRange()
{
SheetId = this.dataSheetId,
StartColumnIndex = 0
},
SortSpecs = new List <SortSpec>()
{
new SortSpec()
{
DimensionIndex = this.dateRow,
SortOrder = "DESCENDING"
}
}
};
list.Add(new Request()
{
SortRange = sortRequest
});
SetBasicFilterRequest setFilter = new SetBasicFilterRequest()
{
Filter = new BasicFilter()
{
Range = new GridRange()
{
SheetId = this.dataSheetId,
StartColumnIndex = 0
},
}
};
list.Add(new Request()
{
SetBasicFilter = setFilter
});
BatchUpdateSpreadsheetRequest updateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest()
{
Requests = list
};
BatchUpdateSpreadsheetResponse updateResponse = await spreadsheets.BatchUpdate(updateSpreadsheetRequest, this.spreadsheetId).ExecuteAsync();
this.logger.Debug($"Updated filters and sorting.");
}
private static Sheet CreateSheet(string staffID, string staffName)
{
// Create new sheet
BatchUpdateSpreadsheetRequest createRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request
{
AddSheet = new AddSheetRequest {
Properties = new SheetProperties {
Title = staffName
}
}
}
}
};
BatchUpdateSpreadsheetResponse createResponse = service.Spreadsheets.BatchUpdate(createRequest, Config.spreadsheetID).Execute();
// Add metadata to the table specifying which staff member the table belongs to
int sheetID = createResponse.Replies.FirstOrDefault()?.AddSheet.Properties.SheetId ?? -1;
BatchUpdateSpreadsheetRequest metadataRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "StaffID",
MetadataValue = staffID,
Location = new DeveloperMetadataLocation {
SheetId = sheetID
}
}
}
},
new Request
{
AppendCells = new AppendCellsRequest
{
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "Ticket number"
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "Channel"
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "User"
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "Time created"
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "Last staff message"
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = "Summary"
}
}
}
}
},
SheetId = sheetID,
Fields = "*"
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "ticketNumber",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 0,
EndIndex = 1,
SheetId = sheetID
}
}
}
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "channel",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 1,
EndIndex = 2,
SheetId = sheetID
}
}
}
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "user",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 2,
EndIndex = 3,
SheetId = sheetID
}
}
}
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "timeCreated",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 3,
EndIndex = 4,
SheetId = sheetID
}
}
}
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "lastMessage",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 4,
EndIndex = 5,
SheetId = sheetID
}
}
}
}
},
new Request
{
CreateDeveloperMetadata = new CreateDeveloperMetadataRequest
{
DeveloperMetadata = new DeveloperMetadata
{
Visibility = "document",
MetadataKey = "TicketData",
MetadataValue = "summary",
Location = new DeveloperMetadataLocation
{
DimensionRange = new DimensionRange
{
Dimension = "COLUMNS",
StartIndex = 5,
EndIndex = 6,
SheetId = sheetID
}
}
}
}
},
}
};
service.Spreadsheets.BatchUpdate(metadataRequest, Config.spreadsheetID).Execute();
return(GetSpreadsheet().Sheets.FirstOrDefault(s => s.Properties.SheetId == sheetID));
}
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;
}
}
public static async Task <Tuple <bool, string> > ProcessPurchase(string item, string name, int qty, ValueRange itemResult = null, ValueRange dkpResult = null, ValueRange reqResult = null)
{
// find the actual item from the store
string _item = "";
int _cost = 0;
int _stock = 0;
int _lotSize = 1;
int _itemIdx = -1;
if (itemResult == null)
{
itemResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPStoreTab);
}
if (itemResult.Values != null && itemResult.Values.Count > 0)
{
_itemIdx = GoogleSheetsHelper.Instance.IndexInRange(itemResult, item);
if (_itemIdx >= 0)
{
_item = itemResult.Values[_itemIdx][0].ToString();
int.TryParse(itemResult.Values[_itemIdx][1].ToString(), out _cost);
int.TryParse(itemResult.Values[_itemIdx][2].ToString(), out _stock);
int.TryParse(itemResult.Values[_itemIdx][3].ToString(), out _lotSize);
}
}
if (string.IsNullOrEmpty(_item))
{
return(new Tuple <bool, string>(false, $"Unable to find {item} in the DKP store! Use !store to list available items."));
}
// check available clan stock of item
if (qty * _lotSize > _stock)
{
return(new Tuple <bool, string>(false, $"Unable to purchase {item} for {name}: Out of stock"));
}
// check available balance of player DKP
int _dkp = 0;
int _bonusPriority = 0;
int _limit = 0;
int _playerIdx = -1;
if (dkpResult == null)
{
dkpResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPMainTab);
}
if (dkpResult.Values != null && dkpResult.Values.Count > 0)
{
_playerIdx = GoogleSheetsHelper.Instance.IndexInRange(dkpResult, name);
if (_playerIdx >= 0)
{
int.TryParse(dkpResult.Values[_playerIdx][2].ToString(), out _dkp);
int.TryParse(dkpResult.Values[_playerIdx][6].ToString(), out _bonusPriority);
int.TryParse(dkpResult.Values[_playerIdx][7].ToString(), out _limit);
}
}
if (_dkp < _cost * qty)
{
return(new Tuple <bool, string>(false, $"Insufficient DKP [{_dkp}] for {name} to cover the cost [{_cost * qty}] of {qty} {_item}"));
}
if (_limit + qty * _lotSize > Config.Global.Commands.Buy.WeeklyLimit)
{
return(new Tuple <bool, string>(false, $"{name} is unable to purchase {_item} due to weekly limit of {_limit}/{Config.Global.Commands.Buy.WeeklyLimit}"));
}
IList <Request> transaction = new List <Request>();
// find any existing requests from this user for this item
if (reqResult == null)
{
reqResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPRequestsTab);
}
if (reqResult.Values != null && reqResult.Values.Count > 0)
{
int reqIdx = GoogleSheetsHelper.Instance.SubIndexInRange(reqResult, name, 1, _item);
if (reqIdx >= 0)
{
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out int currentQty);
currentQty -= qty;
GridRange staticReqRange = GetTabRange(EDKPTabs.Requests);
GridRange reqRange = new GridRange
{
SheetId = staticReqRange.SheetId,
StartRowIndex = reqIdx,
EndRowIndex = reqIdx + 1,
StartColumnIndex = staticReqRange.EndColumnIndex - 1,
EndColumnIndex = staticReqRange.EndColumnIndex
};
if (currentQty > 0)
{
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = reqRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = currentQty
}
}
}
}
}
}
});
}
else
{
transaction.Add(new Request
{
DeleteDimension = new DeleteDimensionRequest
{
Range = new DimensionRange
{
Dimension = "ROWS",
SheetId = reqRange.SheetId,
StartIndex = reqRange.StartRowIndex,
EndIndex = reqRange.EndRowIndex
}
}
});
}
}
}
// log the purchase
GridRange logRange = GetTabRange(EDKPTabs.Log);
transaction.Add(new Request
{
AppendCells = new AppendCellsRequest
{
SheetId = logRange.SheetId,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = name
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _cost * qty * -1
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = DateTime.Now.ToOADate()
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = Config.Global.Commands.Buy.PurchaseReason
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = _item
}
}
}
}
}
}
});
// log the priority (if necessary)
if (_bonusPriority > 0)
{
GridRange priorityRange = GetTabRange(EDKPTabs.PriorityLog);
transaction.Add(new Request
{
AppendCells = new AppendCellsRequest
{
SheetId = priorityRange.SheetId,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = name
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = Math.Min(_bonusPriority, _cost * qty) * -1
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = DateTime.Now.ToOADate()
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = _item
}
}
}
}
}
}
});
}
// update the stock
GridRange staticStoreRange = GetTabRange(EDKPTabs.Store);
GridRange storeRange = new GridRange
{
SheetId = staticStoreRange.SheetId,
StartRowIndex = staticStoreRange.StartRowIndex + _itemIdx,
EndRowIndex = staticStoreRange.StartRowIndex + _itemIdx + 1,
StartColumnIndex = staticStoreRange.StartColumnIndex + 2,
EndColumnIndex = staticStoreRange.StartColumnIndex + 3
};
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = storeRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _stock - (qty * _lotSize)
}
}
}
}
}
}
});
// update the weekly limit
GridRange staticMainRange = GetTabRange(EDKPTabs.Main);
GridRange dkpRange = new GridRange
{
SheetId = staticMainRange.SheetId,
StartRowIndex = staticMainRange.StartRowIndex + _playerIdx,
EndRowIndex = staticMainRange.StartRowIndex + _playerIdx + 1,
StartColumnIndex = staticMainRange.StartColumnIndex + 7,
EndColumnIndex = staticMainRange.EndColumnIndex
};
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = dkpRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _limit + qty * _lotSize
}
}
}
}
}
}
});
BatchUpdateSpreadsheetResponse txResponse = await GoogleSheetsHelper.Instance.TransactionAsync(transaction);
return(new Tuple <bool, string>(true, $"{name} purchased {qty} {_item} for {_cost * qty} DKP"));
}
public void ExportarJogosJquery()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
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/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
}
public void TesteSheet()
{
#region setup
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/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
List <GameView> lista_mock = new List <GameView>();
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste teste teste teste teste teste teste"
});
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
#endregion
#region limpar, preencher e formatar abas
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
#endregion
#region deletar aba
// A list of updates to apply to the spreadsheet.
// Requests will be applied in the order they are specified.
// If any request is not valid, no requests will be applied.
/*List<Request> requests = new List<Request>(); // TODO: Update placeholder value.
* var a = new DeleteSheetRequest();
* a.SheetId = 0;
*
* var t = new Request();
* t.DeleteSheet = a;
*
* requests.Add(t);
*
* // TODO: Assign values to desired properties of `requestBody`:
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
* requestBody.Requests = requests;
*
* SpreadsheetsResource.BatchUpdateRequest req = sheetsService.Spreadsheets.BatchUpdate(requestBody, id);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* BatchUpdateSpreadsheetResponse response = req.Execute();*/
#endregion
#region criar planilha
// TODO: Assign values to desired properties of `requestBody`:
/*Spreadsheet requestBody3 = new Spreadsheet();
* var propriedades = new SpreadsheetProperties();
* propriedades.Title = "Games";
* requestBody3.Properties = propriedades;
*
* SpreadsheetsResource.CreateRequest request3 = sheetsService.Spreadsheets.Create(requestBody3);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* Spreadsheet response3 = request3.Execute();
* // Data.Spreadsheet response = await request.ExecuteAsync();
*
* // TODO: Change code below to process the `response` object:
* Console.WriteLine(response3);*/
#endregion
}
