public static void appendEmptyColumn(string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
{
// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
// string range = "1 - Performance Test Results"; // TODO: Update placeholder value.
//"'1 - Performance Test Results'!A:G"
// The ID of the spreadsheet to update.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
Data.Request reqBody = new Data.Request
{
AppendDimension = new Data.AppendDimensionRequest
{
SheetId = sheetId,
Dimension = "COLUMNS",
Length = 1
}
};
List <Data.Request> requests = new List <Data.Request>();
requests.Add(reqBody);
// TODO: Assign values to desired properties of `requestBody`:
Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
//SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateSpreadsheetResponse response = request.Execute();
// Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
// SpreadsheetsResource.ValuesResource.AppendRequest request =
// sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
// request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
//var response = request.Execute();
}
private async void HighlightCellAsync(int row, int col)
{
Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
// Send a request that get the range, set the background color to highlight yellow, and set the date as today
requestBody.Requests = new List <Google.Apis.Sheets.v4.Data.Request>()
{
new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = this.SheetID,
StartRowIndex = row,
EndRowIndex = row + 1,
StartColumnIndex = col,
EndColumnIndex = col + 1
},
Cell = new CellData()
{
UserEnteredFormat = new CellFormat()
{
BackgroundColor = new Color()
{
Red = 1,
Green = 1,
Blue = 0
},
},
UserEnteredValue = new ExtendedValue()
{
StringValue = DateTime.Today.ToString("MM/dd/yyyy")
}
},
Fields = "userEnteredValue,userEnteredFormat.backgroundColor"
},
}
};
// Send the request
SpreadsheetsResource.BatchUpdateRequest request = Service.Spreadsheets.BatchUpdate(requestBody, this.SpreadSheetID);
Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
Console.WriteLine(JsonConvert.SerializeObject(response));
}
public static void mergeCells(int startCol, int startRow, string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
{
// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
//"'1 - Performance Test Results'!A:G"
// The ID of the spreadsheet to update.
// string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
Data.Request reqBody = new Data.Request
{
MergeCells = new Data.MergeCellsRequest
{
Range = new Data.GridRange
{
SheetId = sheetId,
StartRowIndex = startRow,
EndRowIndex = startRow + 1,
StartColumnIndex = startCol,
EndColumnIndex = startCol + 9
},
MergeType = "MERGE_ROWS"
}
};
List <Data.Request> requests = new List <Data.Request>();
requests.Add(reqBody);
// TODO: Assign values to desired properties of `requestBody`:
Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
//SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateSpreadsheetResponse response = request.Execute();
// Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
// SpreadsheetsResource.ValuesResource.AppendRequest request =
// sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
// request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
//var response = request.Execute();
}
private static void FieldMatrixHeaderData(SheetsService sheetsService, string sheetName, List <FieldCordinates> fieldCoordRes)
{
// The ID of the spreadsheet to update.
// TODO: Update placeholder value.
BatchUpdateSpreadsheetRequest bussr = new BatchUpdateSpreadsheetRequest();
//get sheet id by sheet name
Spreadsheet spr = sheetsService.Spreadsheets.Get(spreadSheetId).Execute();
Sheet sh = spr.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int sheetId = (int)sh.Properties.SheetId;
//create the update request for cells from the first row
var userEnteredFormat = new CellFormat()
{
BackgroundColor = new Color()
{
Blue = (float)0.933,
Red = (float)0.393,
Green = (float)0.586,
},
HorizontalAlignment = "CENTER",
TextFormat = new TextFormat()
{
FontSize = 24,
ForegroundColor = new Color()
{
Blue = 1,
Red = 1,
Green = 1,
}
},
};
var updateCellsRequest = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = sheetId,
StartRowIndex = 0,
EndRowIndex = 1
},
Cell = new CellData()
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(BackgroundColor,TextFormat)"
}
};
//// TODO: Assign values to desired properties of `requestBody`:
bussr.Requests = new List <Request>();
bussr.Requests.Add(updateCellsRequest);
SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(bussr, spreadSheetId);
//// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateSpreadsheetResponse response = request.Execute();
//Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
}
