public List <ExpandoObject> GetDataFromSheet(GoogleSheetParameters googleSheetParameters) { googleSheetParameters = MakeGoogleSheetDataRangeColumnsZeroBased(googleSheetParameters); var range = $"{googleSheetParameters.SheetName}!{GetColumnName(googleSheetParameters.RangeColumnStart)}{googleSheetParameters.RangeRowStart}:{GetColumnName(googleSheetParameters.RangeColumnEnd)}{googleSheetParameters.RangeRowEnd}"; SpreadsheetsResource.ValuesResource.GetRequest request = _sheetsService.Spreadsheets.Values.Get(_spreadsheetId, range); var numberOfColumns = googleSheetParameters.RangeColumnEnd - googleSheetParameters.RangeColumnStart; var columnNames = new List <string>(); var returnValues = new List <ExpandoObject>(); if (!googleSheetParameters.FirstRowIsHeaders) { for (var i = 0; i <= numberOfColumns; i++) { columnNames.Add($"Column{i}"); } } var response = request.Execute(); int rowCounter = 0; IList <IList <Object> > values = response.Values; if (values != null && values.Count > 0) { foreach (var row in values) { if (googleSheetParameters.FirstRowIsHeaders && rowCounter == 0) { for (var i = 0; i <= numberOfColumns; i++) { columnNames.Add(row[i].ToString()); } rowCounter++; continue; } var expando = new ExpandoObject(); var expandoDict = expando as IDictionary <String, object>; var columnCounter = 0; foreach (var columnName in columnNames) { expandoDict.Add(columnName, row[columnCounter].ToString()); columnCounter++; } returnValues.Add(expando); rowCounter++; } } return(returnValues); }
public void AddCells(GoogleSheetParameters googleSheetParameters, List <GoogleSheetRow> rows) { var requests = new BatchUpdateSpreadsheetRequest { Requests = new List <Request>() }; var sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName); GridCoordinate gc = new GridCoordinate { ColumnIndex = googleSheetParameters.RangeColumnStart - 1, RowIndex = googleSheetParameters.RangeRowStart - 1, SheetId = sheetId }; var request = new Request { UpdateCells = new UpdateCellsRequest { Start = gc, Fields = "*" } }; var listRowData = new List <RowData>(); foreach (var row in rows) { var rowData = new RowData(); var listCellData = new List <CellData>(); foreach (var cell in row.Cells) { var cellData = new CellData(); var extendedValue = new ExtendedValue { StringValue = cell.CellValue }; cellData.UserEnteredValue = extendedValue; var cellFormat = new CellFormat { TextFormat = new TextFormat() }; if (cell.IsBold) { cellFormat.TextFormat.Bold = true; } cellFormat.BackgroundColor = new Color { Blue = (float)cell.BackgroundColor.B / 255, Red = (float)cell.BackgroundColor.R / 255, Green = (float)cell.BackgroundColor.G / 255 }; cellData.UserEnteredFormat = cellFormat; listCellData.Add(cellData); } rowData.Values = listCellData; listRowData.Add(rowData); } request.UpdateCells.Rows = listRowData; // 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 requests.Requests.Add(request); _sheetsService.Spreadsheets.BatchUpdate(requests, _spreadsheetId).Execute(); }
private GoogleSheetParameters MakeGoogleSheetDataRangeColumnsZeroBased(GoogleSheetParameters googleSheetParameters) { googleSheetParameters.RangeColumnStart = googleSheetParameters.RangeColumnStart - 1; googleSheetParameters.RangeColumnEnd = googleSheetParameters.RangeColumnEnd - 1; return(googleSheetParameters); }