public async Task Write(
string tab,
string range,
List <string[]> valueList)
{
string writeRange = $"{tab}!{range}";
ValueRange valueRange = new ValueRange();
valueRange.Values = valueList.ToArray();
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest =
new SpreadsheetsResource.ValuesResource.UpdateRequest(service,
valueRange,
spreadsheetId,
writeRange);
updateRequest.ValueInputOption
= SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
while (true)
{
try
{
await writeThrottle.WaitTillReady();
await updateRequest.ExecuteAsync();
break;
}
catch (Exception e)
{
log.Error(e);
await Task.Delay(TimeSpan.FromSeconds(10));
}
}
}
public async Task SetAsync <T>(string table, string uId, T value)
{
string range = A1NotationByTableAndUid(table, uId);
ValueRange values = CreateValueRange(uId, JsonConvert.SerializeObject(value));
try
{
SpreadsheetsResource.ValuesResource.UpdateRequest request =
_service.Spreadsheets.Values.Update(values, _spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await request.ExecuteAsync();
}
catch (GoogleApiException ex)
{
if (ex.Message.Contains("Unable to parse range"))
{
await InsertInternal(table, uId, values, false);
}
else
{
throw;
}
}
}
// UpdateSheetAsync(): Will update information for specific sheet
// [param]sheetName: the name for the specific sheet the user wants from the spreadsheet file
// [param]startRange: specific cell number where reading will start
// [param]finishRange: specific cell number where reading will end
// [param]values: the sheet will be updated with these values
public async Task <int?> UpdateSheetAsync(string sheetName, string startRange, string finishRange, List <List <string> > values)
{
string range = "'" + sheetName + "'!" + startRange + ":" + finishRange;
var valueRange = new ValueRange();
//We need a IList<IList<object>> object to send the data. This loop is to convert [param]values into that object
IList <IList <object> > valuesObject = new List <IList <object> >();
foreach (var rowValue in values)
{
var rowObject = new List <object>();
foreach (var columnValue in rowValue)
{
rowObject.Add(columnValue);
}
valuesObject.Add(rowObject);
}
valueRange.Values = valuesObject;
SpreadsheetsResource.ValuesResource.UpdateRequest request = Service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse response = await request.ExecuteAsync();
return(response.UpdatedCells);
}
public async Task Write(string sheetId, string sheetRange, IList <IList <object> > values)
{
SheetsService sheetsService = await GetSheetService();
var valueRange = new ValueRange()
{
Values = values
};
SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(valueRange, sheetId, sheetRange);
request.ValueInputOption =
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse response = await request.ExecuteAsync();
}
private async Task InsertDSNCommand(string dsnCommand, int row)
{
ValueRange v = new ValueRange
{
MajorDimension = "ROWS",
Values = new List <IList <object> > {
new List <object>()
{
dsnCommand
}
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest u = service.Spreadsheets.Values.Update(v, SpreadsheetID, $"DSN Hub!AB{row}");//:O{idx+1}");
u.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
await u.ExecuteAsync();
}
public async Task WriteTransactions(List <BeansTransaction> transactions)
{
// Check connection
if (_googleSheet.Service == null)
{
await _googleSheet.Connect();
}
// Refresh transactions from the DB and add them to the list
await GetTransactionsFromDB();
_transactions.AddRange(transactions);
// Remove duplicates and order ascending
_transactions = _transactions
.GroupBy(transaction => new { transaction.Date, transaction.Name, transaction.Amount })
.Select(transactionGroup => transactionGroup.FirstOrDefault())
.OrderBy(transaction => transaction.Date)
.ToList();
string range = "Transactions!A:D";
ValueRange valueRange = new ValueRange();
IList <IList <object> > rows = new List <IList <object> >();
foreach (var transaction in _transactions)
{
IList <object> cells = new List <object>();
cells.Add(transaction.Date.ToString("yyyy-MM-dd"));
cells.Add(transaction.Name);
cells.Add(transaction.Amount);
cells.Add(transaction.Notes);
rows.Add(cells);
}
valueRange.Values = rows;
SpreadsheetsResource.ValuesResource.UpdateRequest request = _googleSheet.Service.Spreadsheets.Values.Update(valueRange, _googleSheet.SpreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await request.ExecuteAsync();
}
public async Task WriteTransactionTagRelationships(List <BeansTransactionTagRelationship> relationships)
{
// Check connection
if (_googleSheet.Service == null)
{
await _googleSheet.Connect();
}
// Refresh relationships from the DB and add them to the list
await GetTransactionTagRelationshipsFromDB();
_relationships.AddRange(relationships);
// Remove duplicates and order ascending
_relationships = _relationships
.GroupBy(relationship => relationship.TransactionName)
.Select(relationshipGroup =>
{
// If there are duplicate relationships, trump in order of passed relationships, then any with a tag
if (relationshipGroup.Count() > 1)
{
var relationshipsWithTags = relationshipGroup.Where(thisRelationship => thisRelationship.Tags.Count > 0);
if (relationshipsWithTags.Count() > 1)
{
// Use the relationship that was passed
return(relationshipsWithTags
.FirstOrDefault(thisRelationship => thisRelationship == relationships
.FirstOrDefault(passedRelationship => passedRelationship.TransactionName == thisRelationship.TransactionName)));
}
else
{
return(relationshipGroup.FirstOrDefault());
}
}
else
{
return(relationshipGroup.FirstOrDefault());
}
})
.OrderBy(relationship => relationship.TransactionName)
.ToList();
string range = "TransactionTagRelationships!A:B";
ValueRange valueRange = new ValueRange();
IList <IList <object> > rows = new List <IList <object> >();
foreach (var relationship in _relationships)
{
IList <object> cells = new List <object>();
cells.Add(relationship.TransactionName);
if (relationship.Tags != null)
{
var tags = relationship.Tags.Select(tag => tag.Id);
cells.Add(string.Join(",", tags));
}
else
{
cells.Add("");
}
rows.Add(cells);
}
valueRange.Values = rows;
SpreadsheetsResource.ValuesResource.UpdateRequest request = _googleSheet.Service.Spreadsheets.Values.Update(valueRange, _googleSheet.SpreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await request.ExecuteAsync();
}
public async Task CalcAndSendResponse(int idx, List <CalcData> CalcData, bool fromCommand = false)
{
if (fromCommand)
{
idx -= 1;
}
int S16Peak = 0;
int S17Peak = 0;
int S18Peak = 0;
for (int season = 16; season < 19; season++)
{
int highestVal = 0;
foreach (var y in CalcData)
{
if (y.Ratings is null)
{
continue;
}
if (y.Season == season)
{
highestVal = Math.Max(highestVal, y.Ratings.Count > 0 ? y.Ratings.Max() : 0);
}
else
{
continue;
}
}
switch (season)
{
case 16:
{
S16Peak = highestVal;
break;
}
case 17:
{
S17Peak = highestVal;
break;
}
case 18:
{
S18Peak = highestVal;
break;
}
}
}
int s16Games = CalcData.Where(x => x.Season == 16).Sum(x => x.GamesPlayed);
int s17Games = CalcData.Where(x => x.Season == 17).Select(x => x.GamesPlayed).Distinct().Sum();
int s18Games = CalcData.Where(x => x.Season == 18).Select(x => x.GamesPlayed).Distinct().Sum();
IList <object> obj = new List <object>();
UpdateValuesResponse res = null;
obj.Add(s16Games);
obj.Add(s17Games);
obj.Add(s18Games);
obj.Add(null);
obj.Add(S16Peak);
obj.Add(S17Peak);
obj.Add(S18Peak);
obj[3] = $"=IFS(\n" +
$"ISBLANK(A{idx + 1});;AND(NOT(ISBLANK(A{idx + 1}));ISBLANK(F{idx + 1});ISBLANK(G{idx + 1});ISBLANK(H{idx + 1});ISBLANK(J{idx + 1});ISBLANK(K{idx + 1});ISBLANK(L{idx + 1})); \"Pending\";\n" +
$"AND(NOT(ISBLANK(A{idx + 1}));OR(ISBLANK(F{idx + 1});ISBLANK(G{idx + 1});ISBLANK(H{idx + 1});ISBLANK(J{idx + 1});ISBLANK(K{idx + 1});ISBLANK(L{idx + 1}))); \"Missing Data\";\n" +
$"OR(H{idx + 1} < DV_MinGAbsolut; G{idx + 1} < DV_MinGAbsolut; F{idx + 1} < DV_MinGAbsolut; L{idx + 1} = 0; K{idx + 1} = 0; J{idx + 1} = 0); \"Investigate App\";\n" +
$"AND(H{idx + 1} < DV_MinGCurrent; G{idx + 1} < DV_MinGPrev1; F{idx + 1} < DV_MinGPrev2); \"Min Games not reached\";\n" +
$"AND(L{idx + 1} < DV_DSNMin; K{idx + 1} < DV_DSNMin; J{idx + 1} < DV_DSNMin); \"Too Low\";\n" +
$"OR(H{idx + 1} >= DV_MinGCurrent; G{idx + 1} >= DV_MinGPrev1; F{idx + 1} >= DV_MinGPrev2); \"Verified\")\n";
ValueRange v = new ValueRange
{
MajorDimension = "ROWS",
Values = new List <IList <object> > {
obj
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest u = service.Spreadsheets.Values.Update(v, SpreadsheetID, $"DSN Hub!F{idx + 1}");//:O{idx+1}");
u.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
res = await u.ExecuteAsync();
}
