protected void AddNewSheet(string title, int?sheetId, params string[] headings)
{
var addSheetRequest = new AddSheetRequest
{
Properties = new SheetProperties
{
Title = title,
SheetId = sheetId,
Index = 1, // The presumes we will add sheets in reverse order
GridProperties = new GridProperties {
FrozenRowCount = 1
},
}
};
var row = headings.ToRowData();
var updateRequest = new UpdateCellsRequest
{
Range = new GridRange {
SheetId = sheetId, StartColumnIndex = 0, StartRowIndex = 0, EndColumnIndex = headings.Length, EndRowIndex = 1
},
Rows = new List <RowData> {
row
},
Fields = "userEnteredValue"
};
_requests.Add(new Request {
AddSheet = addSheetRequest
});
_requests.Add(new Request {
UpdateCells = updateRequest
});
}
public PushColumnSheetRequest(int sheetId, SheetColumn column)
{
Column = column;
ColumnIndex = column.ColumnIndex;
UpdateHeaderRequest = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = sheetId,
StartColumnIndex = ColumnIndex,
EndColumnIndex = ColumnIndex + 1,
StartRowIndex = 0,
EndRowIndex = 1 // header only
},
Fields = "userEnteredValue,note"
};
UpdateCellsRequest = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = sheetId,
StartColumnIndex = ColumnIndex,
EndColumnIndex = ColumnIndex + 1,
StartRowIndex = 1, // skip header
},
Rows = Rows
};
switch (Column.PushFields)
{
case PushFields.Value:
UpdateCellsRequest.Fields = "userEnteredValue";
break;
case PushFields.Note:
UpdateCellsRequest.Fields = "note";
break;
case PushFields.ValueAndNote:
UpdateCellsRequest.Fields = "userEnteredValue,note";
break;
}
Requests = new Request[]
{
new Request {
UpdateCells = UpdateHeaderRequest
},
new Request {
UpdateCells = UpdateCellsRequest
}
};
}
public UpdateCellsRequest AddUpdateCells(int sheetId, string fields = "*")
{
var updateCellsRequest = new UpdateCellsRequest {
Range = new GridRange {
SheetId = sheetId
}, Fields = fields, Rows = new List <RowData>()
};
this.Requests.Add(new Request {
UpdateCells = updateCellsRequest
});
return(updateCellsRequest);
}
/// <summary>
/// Creates a request to overlay the input cells onto the specified cells.
/// </summary>
/// <param name="spreadsheetId">The id of the sheets doc</param>
/// <param name="tabName">The name of the tab containing the cells to update</param>
/// <param name="rows">List of rows to apply to cells</param>
/// <param name="range">The range of cells to update</param>
/// <param name="fieldMask">Set this to a field of the request to only override that field</param>
/// <returns></returns>
private static Request requestUpdateCells(string spreadsheetId, string tabName, List <RowData> rows, GridRange range, string fieldMask = "*")
{
UpdateCellsRequest updateCellsRequest = new UpdateCellsRequest();
updateCellsRequest.Range = range;
updateCellsRequest.Fields = fieldMask;
updateCellsRequest.Rows = rows;
Request request = new Request();
request.UpdateCells = updateCellsRequest;
return(request);
}
public static void UpdateCells(SheetsService service, String spreadsheetId, int gid, int row, int col, String[] value)
{
List <Request> requests = new List <Request>();
List <RowData> rowDatas = new List <RowData>();
for (int i = 0; i < value.Length; i++)
{
List <CellData> valuess = new List <CellData>();
ExtendedValue extendedValue = new ExtendedValue();
extendedValue.StringValue = value[i];
CellData cd = new CellData();
cd.UserEnteredValue = extendedValue;
valuess.Add(cd);
RowData rowData = new RowData();
rowData.Values = valuess;
rowDatas.Add(rowData);
}
GridCoordinate gridCoordinate = new GridCoordinate();
gridCoordinate.SheetId = gid;
gridCoordinate.RowIndex = row;
gridCoordinate.ColumnIndex = col;
UpdateCellsRequest updateCellsRequest = new UpdateCellsRequest();
updateCellsRequest.Rows = rowDatas;
updateCellsRequest.Start = gridCoordinate;
updateCellsRequest.Fields = "userEnteredValue";
Request request = new Request();
request.UpdateCells = updateCellsRequest;
requests.Add(request);
BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateRequest.Requests = requests;
service.Spreadsheets.BatchUpdate(batchUpdateRequest, spreadsheetId).Execute();
}
private static UpdateCellsRequest CreateUpdateCellsRequest(int sheetId, int startRowIndex, int startColumnIndex, DataColumnCollection columns, int fgColorHeader, int bgColorHeader, bool doFullFormatting)
{
var rowData = CreateRowData(sheetId, columns, fgColorHeader, bgColorHeader, doFullFormatting);
var rowDataList = new List <RowData>();
rowDataList.Add(rowData);
var updateRequest = new UpdateCellsRequest();
updateRequest.Start = new GridCoordinate()
{
SheetId = sheetId,
ColumnIndex = startColumnIndex,
RowIndex = startRowIndex,
};
updateRequest.Rows = rowDataList;
updateRequest.Fields = "UserEnteredValue,UserEnteredFormat";
return(updateRequest);
}
private static UpdateCellsRequest CreateUpdateCellsRequest(int sheetId, int startRowIndex, int startColumnIndex, DataRowCollection rows, int fgColorRow, int bgColorRow)
{
var rowDataList = new List <RowData>();
foreach (DataRow row in rows)
{
var rowData = CreateRowData(sheetId, row, fgColorRow, bgColorRow);
rowDataList.Add(rowData);
}
var updateRequest = new UpdateCellsRequest();
updateRequest.Start = new GridCoordinate()
{
SheetId = sheetId,
ColumnIndex = startColumnIndex,
RowIndex = startRowIndex,
};
updateRequest.Rows = rowDataList;
updateRequest.Fields = "UserEnteredValue,UserEnteredFormat";
return(updateRequest);
}
