public async Task <bool> UpdateMessageStatus(IEnumerable <INeedSend> messages)
{
List <ValueRange> data = new List <ValueRange>();
foreach (var row in messages)
{
ValueRange valueRange = new ValueRange();
valueRange.Range = row.CellForUpdate;
var oblist = new List <object>()
{
"да"
};
valueRange.Values = new List <IList <object> > {
oblist
};
data.Add(valueRange);
}
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = "RAW";
requestBody.Data = data;
var service = await _sheetServiceProvider.GetService();
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, messages.First().Table);
request.Execute();
return(true);
}
public object Edit(SheetRow _Row)
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
ValueRange v = new ValueRange();
v.Range = "Data!A" + _Row.RowIndex;
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedColumns,totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
private void UpdateGoogleSpreadsheetCell(string spreadSheetID, string cell, string value)
{
List <List <string> > valueList = new List <List <string> >
{
new List <string> {
value
}
};
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "Update Poker Data";
UserCredential credential;
string credentialsPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\Dropbox\\Projects\\Poker Wizard\\Poker Wizard\\credentials.json";
using (var stream =
new FileStream(credentialsPath, FileMode.Open, FileAccess.ReadWrite))
{
// 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.
string credPath = "token.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,
});
List <ValueRange> dataList = new List <ValueRange>();
var obList = new List <object>();
var data = new ValueRange
{
Values = valueList.Select(list => list.ToArray()).ToArray(),
Range = cell
};
dataList.Add(data);
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = "RAW",
Data = dataList
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetID);
BatchUpdateValuesResponse response = request.Execute();
}
private static void Execute(SpreadsheetsResource.ValuesResource.BatchUpdateRequest request)
{
try
{
BatchUpdateValuesResponse response = request.Execute();
if (response != null)
{
}
}
catch (Exception ex)
{
string msg = ex.Message;
System.Diagnostics.Debugger.Break();
}
}
public object Create(SheetRow _Row)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
// Define request parameters.
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, "Data!A:B");
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
ValueRange v = new ValueRange();
v.Range = "Data!A" + (values.Count + 1) + ":Z" + (values.Count + 1);
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
public static void InserViewCountToPopularityColumn(List <SpotifyInfo> songs, string columnName)
{
try
{
var service = UserCredential();
IList <SpotifyInfo> dataList = songs;
List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
valueDataRange.Values = new List <IList <object> >()
{
};
valueDataRange.Values.Add(new List <object> {
DateTime.Now.ToString("POPULARITY")
});
int max = dataList.Count + 1;
valueDataRange.Range = range + "!" + columnName + "1:" + columnName + max.ToString();
for (int i = 0; i < dataList.Count; i++)
{
IList <object> list = new List <object> {
dataList[i].Popularity
};
valueDataRange.Values.Add(list);
}
data.Add(valueDataRange);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = "USER_ENTERED";
requestBody.Data = data;
// API to update data to sheet
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse response = request.Execute();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void writeToGoogleSheet(DataTable dt)
{
List <IList <Object> > googleDataRows = new List <IList <Object> >();
var googleDataRow = new List <object>();
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < 6; i++)
{
googleDataRow.Add(row[i]);
}
googleDataRows.Add(googleDataRow);
googleDataRow = new List <object>();
}
// The new values to apply to the spreadsheet.
List <Data.ValueRange> data = new List <Data.ValueRange>(); // TODO: Update placeholder value.
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = "RAW";
ValueRange vr = new ValueRange();
vr.Values = googleDataRows;
vr.Range = monthStrings[monthIndex];
data.Add(vr);
requestBody.Data = data;
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = Program.SpreadsheetService.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateValuesResponse response = request.Execute();
Console.Read();
}
public static void AddList(List <ServiceRequestModel> requestModelList, string spreadSheetId)
{
// Specifying Column Range for reading...
var range = $"{sheet}!A:F";
List <ValueRange> valueRangesList = new List <ValueRange>();
// Data for another Student...
for (int i = 2; i < requestModelList.Count + 2; i++)
{
}
int index = 2;
foreach (var requestModel in requestModelList)
{
var valueRange = new ValueRange();
var oblist = new List <object>()
{
requestModel.TicketId, requestModel.FullName, requestModel.ServiceNm, requestModel.Status,
requestModel.StaffNm, requestModel.DepartmentNm
};
valueRange.Range = $"{sheet}!A{index}:F";
valueRange.Values = new List <IList <object> > {
oblist
};
valueRangesList.Add(valueRange);
index++;
}
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest()
{
ValueInputOption = "USER_ENTERED",
Data = valueRangesList
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetId);
BatchUpdateValuesResponse response = request.Execute();
}
public static void InsertLinkYoutube(List <Songs> songs, int numberOfOldSongs)
{
try
{
var service = UserCredential();
IList <Songs> dataList = songs;
List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
valueDataRange.Values = new List <IList <object> >()
{
};
int max = numberOfOldSongs + songs.Count + 1;
valueDataRange.Range = range + "!K" + (numberOfOldSongs + 2).ToString() + ":" + "K" + max.ToString();
for (int i = 0; i < dataList.Count; i++)
{
IList <object> list = new List <object> {
dataList[i].YoutubeUrl
};
valueDataRange.Values.Add(list);
}
data.Add(valueDataRange);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = "USER_ENTERED";
requestBody.Data = data;
// API to update data to sheet
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse response = request.Execute();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void SaveToGoogleDrive(string fullName, string name, int seriaCount)
{
UserCredential credential;
using (var stream =
new FileStream(Directory.GetFiles(@"C:\MoviesParser")[0], 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: " + credPath);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = "1SRMlO18VipcSsekYMlsP2UMSKaNX7GOEXHjIulEeEsw";
int rowNum = 6080 + seriaCount;
String range = "B" + rowNum + ":D" + rowNum;
//String rangeGet = "A1:E1";
IList <object> valueToWrite = new List <object>()
{
fullName,
name,
"Жигайло"
};
ValueRange requestBody = new ValueRange()
{
MajorDimension = "ROWS",
Range = range,
Values = new List <IList <object> >()
{
valueToWrite
}
};
//SpreadsheetsResource.ValuesResource.GetRequest requestGet = service.Spreadsheets.Values.Get(spreadsheetId, rangeGet);
//ValueRange response = requestGet.Execute();
//IList<IList<Object>> values = response.Values;
BatchUpdateValuesRequest batchUpdate = new BatchUpdateValuesRequest();
batchUpdate.Data = new List <ValueRange>()
{
requestBody
};
batchUpdate.ValueInputOption = "RAW";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request =
service.Spreadsheets.Values.BatchUpdate(batchUpdate, spreadsheetId);
request.Execute();
}
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();
}