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);
}
public IActionResult SpreadEdit(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_edit")] HttpRequest req, ILogger log)
{
_sheetsService = ConnectSpreadSheet();
String range = "シート1!B2";
string valueInputOption = "USER_ENTERED";
List <Data.ValueRange> updateData = new List <Data.ValueRange>();
var dataValueRange = new Data.ValueRange();
List <IList <object> > data = new List <IList <object> >();
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
data.Add(GetRandomList());
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, _editSpreadsheetId);
Data.BatchUpdateValuesResponse response = request.Execute();
return(new ObjectResult(JsonConvert.SerializeObject(response)));
}
private static void test()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "Brawlhalla Stats";
UserCredential credential;
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.
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,
});
// Define request parameters.
String spreadsheetId = "1MwKJaBCy4xRbT0hKpKGOr6vfZHmzhy4uJ76nfD78YWA";
String range = "Ark1!A2:E";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
range = "Ark1!A2:Y";
string valueInputOption = "USER_ENTERED";
List <IList <object> > data = new List <IList <object> >();
// The new values to apply to the spreadsheet.
List <Data.ValueRange> updateData = new List <Data.ValueRange>();
var dataValueRange = new Data.ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
//request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
service.Spreadsheets.Values.Append(null, spreadsheetId, range);
Console.Read();
}
/// Inserts the data into specific sheet name at row # provided
private static void InsertData(SheetsService service, string sheetName, int row, List <IList <object> > data)
{
String range = sheetName + "!A" + row + ":Y";
string valueInputOption = "USER_ENTERED";
// The new values to apply to the spreadsheet.
List <Google.Apis.Sheets.v4.Data.ValueRange> updateData = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
var dataValueRange = new Google.Apis.Sheets.v4.Data.ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse response = request.Execute();
}
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 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);
}
}
