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