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)));
}
/// 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 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);
}
}
