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