public static void AtualizarPlanilha()
{
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(idPlanilha);
planilhas = get.Execute();
valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, idPlanilha, "!A1:Z1000");
request.Execute();
for (int i = 0; i <= 5; i++)
{
GerarTabelaMesSheet(i);
}
for (int i = 0; i <= 5; i++)
{
GerarTabelaSavingsSheet(i);
}
Layout();
}
}
public void Clear(string sheetId, string range)
{
ClearValuesRequest body = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = _sheetService.Spreadsheets.Values.Clear(body, sheetId, range);
clearRequest.Execute();
}
public void ClearSheetAtRange(string sheetName, string range)
{
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = Service.Spreadsheets.Values.Clear(requestBody, SheetId, GetSheetAndRangeName(sheetName, range));
clearRequest.Execute();
}
public static void UpdateEntry()
{
ReadCart();
var range = $"cart!A:C";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
0
};
valueRange.Values = new List <IList <object> > {
oblist
};
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request =
_service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
ClearValuesResponse response = request.Execute();
// Data.ClearValuesResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
}
private void clearColumn(string range)
{
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest req = service.Spreadsheets.Values.Clear(clearRequest, spreadSheetId, range);
req.Execute();
}
private void RemoveEntry(string SpreadSheet, List <object> objectArray)
{
ValueRange values = GetValues(SpreadSheet);
int i = 1;
bool hasBeenSet = false;
foreach (IList <object> row in values.Values.Skip(1))
{
i++;
if (row[0].ToString() == objectArray[0].ToString())
{
hasBeenSet = true; break;
}
}
if (!hasBeenSet)
{
return;
}
string range = $"{SpreadSheet}!A{i}:Z{i}";
ClearValuesRequest valueRange = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(valueRange, SpreadSheetID, range);
ClearValuesResponse updateRespond = clearRequest.Execute();
}
public void ClearValues(string range)
{
var body = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request =
_service.Spreadsheets.Values.Clear(body, _sheetId, range);
request.Execute();
}
private void ClearSpreadsheet()
{
string range = "Server List!A1:A255";
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = SheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
ClearValuesResponse response = request.Execute();
}
private void CleanRange(SheetsService service, string range)
{
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.ClearValuesResponse response = request.Execute();
}
private void cleanGoogleSheets()
{
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, googleSheetsRange);
try
{
request.Execute();
}
catch (Exception e)
{
throw e;
}
}
private static void UpdateSheet(ref SheetsService service, string sheetName, ref IList <IList <Object> > values)
{
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest =
new SpreadsheetsResource.ValuesResource.ClearRequest(service, new ClearValuesRequest(), _googleSheets_Spreadsheet.SpreadsheetId, range: $"{sheetName}!A:D");
//preliminary cleanse the sheet
clearRequest.Execute();
SpreadsheetsResource.ValuesResource.AppendRequest updateRequest =
service.Spreadsheets.Values.Append(new ValueRange()
{
Values = values
}, _googleSheets_Spreadsheet.SpreadsheetId, range: $"{sheetName}!A:A");
updateRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
updateRequest.Execute();
}
/// <summary>
/// 清空資料表內容
/// </summary>
private void ClearV4Sheets()
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "Get Google SheetData with Google Sheets API",
});
// TODO: Assign values to desired properties of `requestBody`:
ClearValuesRequest requestBody = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, sheetName);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
ClearValuesResponse response = request.Execute();
// Data.ClearValuesResponse response = await request.ExecuteAsync();
}
private void UpdateTable()
{
try
{
if (spreadSheetsService == null)
{
spreadSheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = applicationName,
});
}
IList <IList <object> > insertValues = new List <IList <object> >();
foreach (Note note in Note.NoteList)
{
IList <object> ROW = new List <object>();
ROW.Add(JsonConvert.SerializeObject(note, jsonSettings));
insertValues.Add(ROW);
if (note.GetType() == typeof(Screenshot))
{
Screenshot screen = note as Screenshot;
UploadScreenshot(screen);
}
}
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
ValueRange body = new ValueRange();
body.Values = insertValues;
SpreadsheetsResource.ValuesResource.ClearRequest clearResult = spreadSheetsService.Spreadsheets.Values.Clear(clearValuesRequest, gSyncSettings.spreadshetrID, "Notesieve!A1:B");
SpreadsheetsResource.ValuesResource.UpdateRequest result = spreadSheetsService.Spreadsheets.Values.Update(body, gSyncSettings.spreadshetrID, "Notesieve!A1:B");
clearResult.Execute();
result.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
result.Execute();
}
catch (System.Net.Http.HttpRequestException e)
{
OnSyncStateChanged?.Invoke(gSyncState.eLostConnection);
}
}
public static Data.ClearValuesResponse Clearspreadsheet(string spreadsheetId, string range, SheetsService service)
{
Console.WriteLine("Do you want to give your own range? 1. Yes\t Any other key. No");
string rangeChoice = Console.ReadLine();
if (rangeChoice == "1")
{
range = GetRangeFromUser();
}
//Create a clear request
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(null, spreadsheetId, range);
Console.WriteLine("Clearing range " + range + " from spreadsheet...");
//Execute the request
Data.ClearValuesResponse response = request.Execute();
Headers.Clear();
return(response);
}
private static void CreateSheet(SheetsService service, string spreadsheetId, string name)
{
try
{
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = name;
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddSheet = addSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
batchUpdateRequest.Execute();
}
catch
{
try
{
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY");
ClearValuesResponse response = request.Execute();
Thread.Sleep(API_THROTTLE_WAIT);
}
catch
{
//sheet doesn`t exist
}
}
finally
{
Thread.Sleep(API_THROTTLE_WAIT);
}
}
public object deleteData(string spreadsheetId, string tenBang, string phamViCell)
{
/*Xóa dữ liệu trong bảng
* Đầu vào cần có ID bảng, Tên sheet vd: sheet 1, phạm vi cần xóa*/
UserCredential credential = GetCredential(ScopesWrite);
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "VolunteerDTU"
});
try
{
string range = "'" + tenBang + "'!" + phamViCell;;
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
Data.ClearValuesResponse response = request.Execute();
return(request);
}
catch (Exception e)
{
return(e);
}
}
public Form1()
{
InitializeComponent();
GoogleCredential credential;
using (var stream = new FileStream("My First Project-2dfed0050064.json", FileMode.Open, FileAccess.Read))
{
credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
}
service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Main Table!A1:Z100");
Google.Apis.Sheets.v4.Data.ClearValuesResponse response = request.Execute();
Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody2 = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request2 = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Data Table!A1:Z100");
Google.Apis.Sheets.v4.Data.ClearValuesResponse response2 = request2.Execute();
}
public void TesteSheet()
{
#region setup
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) {
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
List <GameView> lista_mock = new List <GameView>();
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste"
});
lista_mock.Add(new GameView {
Name = "teste teste teste teste teste teste teste"
});
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
#endregion
#region limpar, preencher e formatar abas
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
#endregion
#region deletar aba
// A list of updates to apply to the spreadsheet.
// Requests will be applied in the order they are specified.
// If any request is not valid, no requests will be applied.
/*List<Request> requests = new List<Request>(); // TODO: Update placeholder value.
* var a = new DeleteSheetRequest();
* a.SheetId = 0;
*
* var t = new Request();
* t.DeleteSheet = a;
*
* requests.Add(t);
*
* // TODO: Assign values to desired properties of `requestBody`:
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
* requestBody.Requests = requests;
*
* SpreadsheetsResource.BatchUpdateRequest req = sheetsService.Spreadsheets.BatchUpdate(requestBody, id);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* BatchUpdateSpreadsheetResponse response = req.Execute();*/
#endregion
#region criar planilha
// TODO: Assign values to desired properties of `requestBody`:
/*Spreadsheet requestBody3 = new Spreadsheet();
* var propriedades = new SpreadsheetProperties();
* propriedades.Title = "Games";
* requestBody3.Properties = propriedades;
*
* SpreadsheetsResource.CreateRequest request3 = sheetsService.Spreadsheets.Create(requestBody3);
*
* // To execute asynchronously in an async method, replace `request.Execute()` as shown:
* Spreadsheet response3 = request3.Execute();
* // Data.Spreadsheet response = await request.ExecuteAsync();
*
* // TODO: Change code below to process the `response` object:
* Console.WriteLine(response3);*/
#endregion
}
private void sendDataToGoogleSpreadsheets(object sender, EventArgs e)
{
UserCredential credential;
waitwindow f = new waitwindow();
f.Show();
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None).Result;
}
service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = SheetId;
String range = "A:A";
SpreadsheetsResource.ValuesResource.GetRequest getRequest =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange getResponse = getRequest.Execute();
IList <IList <Object> > getValues = getResponse.Values;
List <List <object> > data = new List <List <object> >();
int currentCount = getValues.Count() + 2;
String newRange = "A" + currentCount + ":A";
List <ValueRange> info = new List <ValueRange>();
int count = 3;
char cell = 'A';
foreach (var recipe in groceryList)
{
var reciperange = new ValueRange();
reciperange.Range = Char.ToString(cell) + count.ToString();
reciperange.MajorDimension = "COLUMNS";
List <object> ingreds = new List <object>();
foreach (var x in recipe)
{
string y = x.Substring(0, x.Length - 1);
ingreds.Add("----" + y + "----");
foreach (var item in ingredientList[x])
{
ingreds.Add(item);
}
ingreds.Add("");
}
reciperange.Values = new List <IList <object> > {
ingreds
};
info.Add(reciperange);
cell++;
}
string valueInputOption = "USER_ENTERED";
Google.Apis.Sheets.v4.Data.ClearValuesRequest yeah = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clear = service.Spreadsheets.Values.Clear(yeah, spreadsheetId, "A1:G50");
clear.Execute();
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = info;
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateValuesResponse response = request.Execute();
var headerRange = new ValueRange();
List <object> hd = new List <object>();
hd.Add("Monday");
hd.Add("Tuesday");
hd.Add("Wednesday");
hd.Add("Thursday");
hd.Add("Friday");
hd.Add("Saturday");
hd.Add("Sunday");
headerRange.Range = "A1:G1";
headerRange.Values = new List <IList <object> > {
hd
};
List <ValueRange> headerinfo = new List <ValueRange>();
headerinfo.Add(headerRange);
BatchUpdateValuesRequest requestBody2 = new BatchUpdateValuesRequest();
requestBody2.ValueInputOption = valueInputOption;
requestBody2.Data = headerinfo;
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 = service.Spreadsheets.Values.BatchUpdate(requestBody2, spreadsheetId);
BatchUpdateValuesResponse response2 = request2.Execute();
f.Close();
MessageBox.Show("Grocery Info Sent to Google Spreadsheet.");
}
//TODO start moving this crap to methods or other objects.
public void MainRun()
{
//TODO MEthod to connect and pull data from SQL
//TODO Method to clear the sheet
//TODO Method to write the header and data
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
credPath = System.IO.Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Credential file saved to:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Setup the SQL connection
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand
{
Connection = sqlConnection,
CommandTimeout = 60,
CommandType = CommandType.Text,
CommandText = select
};
try
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Open the SQL connection");
sqlConnection.Open();
}
catch (Exception e)
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoops we cannot connect to SQL - exception " + e.HResult.ToString());
//Environment.Exit(1);
}
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Please wait while reading data from SQL server");
SqlDataReader reader;
reader = cmd.ExecuteReader();
//API method to clear the sheet of all previous values
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Clear the Sheet");
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, sID, dataRange);
// TODO add a try catch statement
ClearValuesResponse clearResponse = cr.Execute();
/*
* //API method to batch update and Delete all the rows
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Delete all rows in Sheet");
* DimensionRange dr = new DimensionRange
* {
* Dimension = "ROWS",
* StartIndex = 1000,
* SheetId = 1809337217 //this is a problem
* };
*
* DeleteDimensionRequest ddr = new DeleteDimensionRequest() { Range = dr };
*
* Request r = new Request { DeleteDimension = ddr };
*
* //THIS IS FOR deleteDimension { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
* List<Request> batchRequests = new List<Request>() { r };
*
* BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest() { Requests = batchRequests };
* SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, sID);
* BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
*/
/* I have added this to the batch method
* //API method to update the header in the Sheet
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the header to the Sheet");
* IList<object> headerList;
* headerList = header.Split(',');
*
* ValueRange valueRange = new ValueRange { MajorDimension = "ROWS" };
* valueRange.Values = new List<IList<object>> { headerList };
*
* SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, sID, dataRange);
* update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
* UpdateValuesResponse result;
* result = update.Execute();
*/
//API method to batch value update the data in the Sheet
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Write the data to the Sheet");
IList <object> headerList;
headerList = header.Split(',');
ValueRange headerVR = new ValueRange
{
MajorDimension = "ROWS",
Range = "A1",
Values = new List <IList <object> > {
headerList
}
};
ValueRange dataVR = new ValueRange()
{
MajorDimension = "ROWS",
Range = "A2:ZZ",
Values = new List <IList <object> >()
};
//Need a list of ValueRanges for the BatchUpdateValuesRequest API
List <ValueRange> data = new List <ValueRange>
{
headerVR,
dataVR
};
//Build the data 2D List object with SQL data
if (reader.HasRows)
{
Object[] colValues = new Object[reader.FieldCount];
List <IList <object> > valueList = new List <IList <object> >();
var rows = 0;
while (reader.Read())
{
List <object> rowData = new List <object>();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
rowData.Add(colValues[i]);
}
valueList.Add(rowData);
rows++;
}
//after 2D list is built point it to the Values method in the ValueRange object
dataVR.Values = valueList;
}
else
{
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
}
BatchUpdateValuesRequest buvr = new BatchUpdateValuesRequest()
{
ValueInputOption = "RAW",
Data = data
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(buvr, sID);
BatchUpdateValuesResponse response = request.Execute();
/*
* //API to append data to sheet. This does not use the batchUpdate
* ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
* // Data is accessible through the DataReader object here.
* ValueRange valueDataRange = new ValueRange() { MajorDimension = "ROWS" };
*
* var dataList = new List<object>();
* valueDataRange.Values = new List<IList<object>> { dataList };
*
* SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, sID, dataRange);
* appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
* appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
*
* if (reader.HasRows)
* {
* Object[] colValues = new Object[reader.FieldCount];
*
* int throttleCount = 0;
* int cnt = 0;
* while (reader.Read())
* {
* //This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
* dataList.Clear();
* for (int i = 0; i < reader.GetValues(colValues); i++)
* {
* dataList.Add(colValues[i]);
* }
*
* try
* {
* //This is the GOOGLE query Throttle they only allow 100 writes per 100 sec per user
* System.Threading.Thread.Sleep(20);
* AppendValuesResponse appendValueResponse = appendRequest.Execute();
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Writing to Sheet: row{" + cnt.ToString() + "}");
* }
* catch (Exception e)
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Whoa buddy slowdown! Exception " + e.HResult.ToString());
* System.Threading.Thread.Sleep(3000);
* throttleCount++;
* }
* cnt++;
* }
* }
* else
* {
* SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("No rows found");
* }
*/
SQL2Sheets.MainWindow.AppWindow.SetDebugScreen("Close reader and SQL");
reader.Close();
sqlConnection.Close();
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to Sheets2CSV");
if (File.Exists("Settings.txt"))
{
Console.WriteLine("There Are Settings!!");
StreamReader infile = new StreamReader("Settings.txt");
configured = infile.ReadLine();
outputdir = infile.ReadLine();
spreadsheetID = infile.ReadLine();
headernum = infile.ReadLine();
entity = infile.ReadLine();
columns = infile.ReadLine();
infile.Close();
}
else
{
Console.WriteLine("Program has not been configured yet! Please Run SettingsUI first to start this program.");
Environment.Exit(0);
}
Console.WriteLine("Moving On...");
#region Configuration Level
string path = outputdir;
//string value = outputdir;
path = path + "\\";
#endregion
#region Preparation Level
#region Sheets API
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-Sheetstocsv.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
//Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = spreadsheetID;
char let = (char)(64 + Convert.ToInt32(headernum));
string range = "A2:" + let;
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
#endregion
Boolean fileExist;
//Check for File
if (File.Exists(path + "Lead_Sheet.csv"))
{
fileExist = true;
}
else
{
fileExist = false;
}
//Setup File IO
StreamWriter file = new StreamWriter(path + entity + "_Sheet.csv", true);
StreamWriter injectionfile = new StreamWriter(path + entity + "_inject.csv");
//Set up Headers
string[] headers = new string[]
{
/*"+new_createdon",
* "firstname",
* "lastname",
* "address1_postalcode",
* "emailaddress1",
* "telephone2",
* "new_isprimaryphonecellphone",
* "*new_besttimetocall",
* "subject"*/
"fname",
"lname",
"org_name",
"member_role"
};
//Check for exisitng records
if (!fileExist)
{
file.Write(entity + ", " + columns + "\n");
}
injectionfile.Write(entity + ", " + columns + "\n");
#endregion
#region Execution Level
//Handle Retrieved Data
if (values != null && values.Count > 0)
{
foreach (var row in values)
{
file.Write(entity + ", ");
injectionfile.Write(entity + ", ");
for (int i = 0; i < row.Count - 1; i++)
{
if (i == row.Count - 1)
{
file.Write(row[i]);
injectionfile.Write(row[i]);
}
if (headers[i] == "address1_postalcode")
{
string r = row[i].ToString();
if (r.Length > 5)
{
file.Write(r.Substring(0, 5) + ", ");
injectionfile.Write(r.Substring(0, 5) + ", ");
}
else
{
file.Write(row[i] + ", ");
injectionfile.Write(row[i] + ", ");
}
}
else if (headers[i] == "telephone2")
{
string val = row[i].ToString();
val = val.Replace("-", string.Empty);
val = val.Replace(" ", string.Empty);
val = val.Replace("(", string.Empty);
val = val.Replace(")", string.Empty);
val = val.Replace(".", string.Empty);
if (val.Length > 10)
{
val = val.Substring(0, 10);
}
file.Write(val + ", ");
injectionfile.Write(val + ", ");
}
else if (headers[i] == "new_isprimaryphonecellphone")
{
if (row[i].ToString() == "Yes")
{
file.Write("TRUE" + ", ");
injectionfile.Write("TRUE" + ", ");
}
else
{
file.Write("FALSE" + ", ");
injectionfile.Write("FALSE" + ", ");
}
}
else if (headers[i] == "*new_besttimetocall")
{
file.Write(callTime[row[i].ToString()] + ", ");
injectionfile.Write(callTime[row[i].ToString()] + ", ");
}
else
{
file.Write(row[i] + ", ");
injectionfile.Write(row[i] + ", ");
}
}
file.Write("\n");
injectionfile.Write("\n");
}
}
string ClearRange = "A2:Z";
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, ClearRange);
Data.ClearValuesResponse clearResponse = clearRequest.Execute();
file.Close();
injectionfile.Close();
#endregion
}
//static volatile bool exit = false;
static void Main(string[] args)
{
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Prints the data from a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So/edit#gid=0
// Define request parameters.
String spreadsheetId = "1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So";
// here is the actual data to be sent to sheet
List <object> headerList = new List <object>()
{
"ID", "DTStamp", "DTShiftStart", "ModelNbr", "SerialNbr", "PassFail", "LineNbr", "ShiftNbr", "Computer", "Word40", "Word41", "Word42"
, "Word43", "Word44", "Word45", "Word46", "Word47", "Word48", "Word49", "Word50", "Word51", "Word52", "Word53", "Word54", "Word55", "Word56"
, "Word57", "Word58", "Word59", "Word60", "Word61", "Word62", "Word63", "Word64", "Word65", "Word66", "Word67", "Word68", "Word69", "Word70"
, "Word71", "Word72", "Word73", "Word74", "Word75", "Word76", "Word77", "Word78", "Word79", "Word80"
};
//var dataList = new List<object>();
//Write some data
String writeRange = "WriteData!A1:ZZ";
ValueRange valueRange = new ValueRange {
MajorDimension = "ROWS"
};
Console.WriteLine("Clear the Sheet");
//API method to clear the sheet
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, writeRange);
ClearValuesResponse clearResponse = cr.Execute();
Console.WriteLine("Delete all rows in Sheet");
//API method to batch update
DimensionRange dr = new DimensionRange
{
Dimension = "ROWS",
StartIndex = 1,
SheetId = 1809337217
};
DeleteDimensionRequest ddr = new DeleteDimensionRequest()
{
Range = dr
};
Request r = new Request {
DeleteDimension = ddr
};
// { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
List <Request> batchRequests = new List <Request>()
{
r
};
BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest()
{
Requests = batchRequests
};
SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
Console.WriteLine("Write the Headers to the Sheet");
//API method to update the sheet
valueRange.Values = new List <IList <object> > {
headerList
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result;
result = update.Execute();
SqlConnection sqlConnection = new SqlConnection("Data Source=tul-mssql;Initial Catalog=Division;User ID=tqisadmin;Password=admin2k");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "SELECT TOP 1000 [ID],[DTStamp],[DTShiftStart],[ModelNbr],[SerialNbr],[PassFail],[LineNbr],[ShiftNbr],[Computer],[Word40],[Word41],[Word42]" +
",[Word43],[Word44],[Word45],[Word46],[Word47],[Word48],[Word49],[Word50],[Word51],[Word52],[Word53],[Word54],[Word55],[Word56]" +
",[Word57],[Word58],[Word59],[Word60],[Word61],[Word62],[Word63],[Word64],[Word65],[Word66],[Word67],[Word68],[Word69],[Word70]" +
",[Word71],[Word72],[Word73],[Word74],[Word75],[Word76],[Word77],[Word78],[Word79],[Word80] " +
"FROM[Division].[dbo].[asyTestRecords] where LineNbr = 2 and computer = 'LN' and dtstamp > '2/1/2018 5:00' order by dtstamp desc";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
Console.WriteLine("Open the SQL connection");
sqlConnection.Open();
cmd.CommandTimeout = 60;
Console.WriteLine("Please wait while reading data from SQL");
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
var dataList = new List <object>();
valueDataRange.Values = new List <IList <object> > {
dataList
};
//API to append data to sheet
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
if (reader.HasRows)
{
//Console.WriteLine("{0}",reader.FieldCount);
Object[] colValues = new Object[reader.FieldCount];
int throttleCount = 0;
int cnt = 0;
while (reader.Read())
{
//This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
dataList.Clear();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
dataList.Add(colValues[i]);
}
try
{
//This is the GOOGLE query Throttle they only allow 500 writes per 100 sec
System.Threading.Thread.Sleep(20);
AppendValuesResponse appendValueResponse = appendRequest.Execute();
Console.WriteLine("Writing to Sheet: row{0}", cnt);
}
catch (Exception e)
{
Console.WriteLine("Whoa buddy slowdown {0}", throttleCount);
System.Threading.Thread.Sleep(3000);
throttleCount++;
}
cnt++;
}
}
else
{
Console.WriteLine("No rows found.");
}
// sit here and wait for a while
Console.WriteLine("Done waiting to close reader and SQL");
System.Threading.Thread.Sleep(3000);
reader.Close();
sqlConnection.Close();
}
public void ExportarJogosJquery()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) {
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/games.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer {
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
string id = "1k7Reqz1ZqGXwr8lTy5Y5r6bX53hxWv4kJSWTs3ptAuc";
GameRepository game = new GameRepository();
PlatformRepository plataforma = new PlatformRepository();
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(id);
Spreadsheet planilhas = get.Execute();
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
var aba = planilha.Properties.Title;
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, id, aba + "!A1:Z1000");
ClearValuesResponse response = request.Execute();
List <GameView> lista = new List <GameView>();
List <int> plat = new List <int>();
List <object> cabecalho = null;
switch (aba)
{
case "Wishlist":
lista = game.ListarJogosWishlist(new List <int> {
0
});
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
case "Watchlist":
lista = game.ListarJogos(new List <int> {
0
}, 3);
cabecalho = new List <object>()
{
"", "Título", "Lançamento", "Plataformas"
};
break;
default:
int?plataformas = plataforma.GetIdBySigla(aba);
plat = new List <int> {
plataformas.Value
};
lista = game.ListarJogos(plat, 1);
cabecalho = new List <object>()
{
"", "Título"
};
break;
}
string range = aba + "!A1:D" + lista.Count + 1;
List <IList <object> > dados = new List <IList <object> >();
dados.Add(cabecalho);
foreach (GameView jogo in lista)
{
if (cabecalho.Count == 2)
{
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name
});
}
else
{
string data = null;
if (jogo.ReleaseDate != null)
{
data = jogo.ReleaseDate.Value.ToShortDateString();
}
dados.Add(new List <object>()
{
"=IMAGE(\"https://images.igdb.com/igdb/image/upload/t_micro/" + jogo.CloudnaryId + ".jpg\")", jogo.Name, data, String.Join(", ", jogo.Plataformas)
});
}
}
ValueRange valueRange = new ValueRange();
valueRange.Values = dados;
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = sheetsService.Spreadsheets.Values.Update(valueRange, id, range);
updateRequest.ValueInputOption = valueInputOption;
UpdateValuesResponse resposta = updateRequest.Execute();
Request alignLeftRequest = new Request();
alignLeftRequest.RepeatCell = new RepeatCellRequest();
alignLeftRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignLeftRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 2, EndColumnIndex = 3
};
alignLeftRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "LEFT"
}
};
Request alignCenterRequest = new Request();
alignCenterRequest.RepeatCell = new RepeatCellRequest();
alignCenterRequest.RepeatCell.Fields = "userEnteredFormat(HorizontalAlignment)";
alignCenterRequest.RepeatCell.Range = new GridRange {
SheetId = planilha.Properties.SheetId, StartColumnIndex = 0, EndColumnIndex = 1
};
alignCenterRequest.RepeatCell.Cell = new CellData {
UserEnteredFormat = new CellFormat {
HorizontalAlignment = "Center"
}
};
Request resizeRequest = new Request();
resizeRequest.AutoResizeDimensions = new AutoResizeDimensionsRequest();
resizeRequest.AutoResizeDimensions.Dimensions = new DimensionRange {
SheetId = planilha.Properties.SheetId, Dimension = "COLUMNS", StartIndex = 1, EndIndex = cabecalho.Count
};
BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest();
batch.Requests = new List <Request>();
batch.Requests.Add(alignLeftRequest);
batch.Requests.Add(alignCenterRequest);
batch.Requests.Add(resizeRequest);
SpreadsheetsResource.BatchUpdateRequest u = sheetsService.Spreadsheets.BatchUpdate(batch, id);
BatchUpdateSpreadsheetResponse responseResize = u.Execute();
}
}