/// <summary>
/// Вставка строки со значениями
/// </summary>
/// <param name="sheet_ID"></param>
/// <param name="Row_after"></param>
static async System.Threading.Tasks.Task InsertRowAsync(Dictionary <string, object> dic, int Row_after)
{
var range = $"{sheet}!B{Row_after}:K{Row_after}";
var valueRange = new ValueRange();
IList <Object> obj = new List <Object>();
foreach (string item in dic.Keys)
{
obj.Add(dic[item]);
}
IList <IList <Object> > values = new List <IList <Object> >();
values.Add(obj);
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(new ValueRange()
{
Values = values
}, SpreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
//var response =
Debug.WriteLine("Entry RowIns");
var resp = request.Execute();
Debug.WriteLine("EXIT RowIns");
}
private void AddDataByV4Sheets2(PageData PageData)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "Get Google SheetData with Google Sheets API",
});
var valueRange = new ValueRange();
var oblist = new List <object>()
{
PageData.Time,
PageData.gcroup,
PageData.group,
PageData.Ename,
PageData.gender,
PageData.ClothesSize,
PageData.Course
};
valueRange.Values = new List <IList <object> > {
oblist
};
valueRange.MajorDimension = "Rows"; //Rows or Columns
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(valueRange, spreadsheetId, sheetName);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = request.Execute();
}
public IActionResult SpreadAppend(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_append")] HttpRequest req, ILogger log)
{
_sheetsService = ConnectSpreadSheet();
string range = "シート1!B2";
var valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
var insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1;
Data.ValueRange requestBody = new Data.ValueRange();
List <IList <object> > data = new List <IList <object> >();
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
requestBody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, _editSpreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
Data.AppendValuesResponse response = request.Execute();
return(new ObjectResult(JsonConvert.SerializeObject(response)));
}
public async void AddToDatabase(List <string> data, String spreadsheetId, string type, string Class)
{
// 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 = type + "!A:G";
IList <IList <object> > val = new List <IList <object> >();
ValueRange requestBody = new ValueRange()
{
Values = val
};
requestBody.Values.Add(data.Select(x => (object)x).ToList());
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
AppendValuesResponse response = await request.ExecuteAsync();
RefreshDatabase(type, Class);
}
public static async Task WriteAsync(List <IList <Object> > values, string spreadSheetId, string fileName)
{
var sheetService = GetSheetsService();
var valueRange = new ValueRange()
{
MajorDimension = "ROWS",
Values = values
};
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED); // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS); // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetService.Spreadsheets.Values.Append(valueRange, spreadSheetId, "A:A");
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
AppendValuesResponse response = await request.ExecuteAsync();
if (response?.Updates?.UpdatedRows != null)
{
Console.WriteLine($"{response.Updates.UpdatedRows} Rows Updated for Sheet: {fileName}");
}
else
{
Console.WriteLine($"Sheet : {fileName} is up to date");
}
}
public async Task Add(string name)
{
if (Utils.GetRowByDiscord(Program.context.User.ToString()) != 0)
{
await ReplyAsync("You have already added yourself."); return;
}
IList <IList <object> > data = new List <IList <object> >()
{
new List <object> {
Utils.UpperCaseIt(name.ToLower()), Program.context.User.ToString()
}
};
range = "B5";
requestbody = new Data.ValueRange();
requestbody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest r = service.Spreadsheets.Values.Append(requestbody, sheetid, range);
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueinputoption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED);
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertdataoption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS);
r.ValueInputOption = valueinputoption;
r.InsertDataOption = insertdataoption;
r.AccessToken = credential.Token.AccessToken;
Data.AppendValuesResponse response = await r.ExecuteAsync();
await ReplyAsync("Successfully added!");
}
// Appends existing sheet with specified rows.
internal static SpreadsheetsResource.ValuesResource.AppendRequest GetAppendRowsRequest(SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount, int rowsToAdd, IList <IList <object> > newRowsData)
{
// Construct string representing range(rows) appending the sheet
string appendedSheetRange = RangeTranslator.GetRangeString(sheetTitleId, LiveSheetLeftIndex, LiveSheetTopIndex, columnCount, rowCount + rowsToAdd);
// Construct requestBody value range.
ValueRange requestBody = new ValueRange();
// Assign request body range string.
requestBody.Range = appendedSheetRange;
// Assign request body values.
requestBody.Values = newRowsData;
// Constructs append rows request.
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, appendedSheetRange);
// Specify the way in which input data should be interpreted.
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
// Specify the way in which input data should be inserted.
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
// Return constructed append rows request.
return(request);
}
public bool InsertRecord(string spreadSheetId, string range, object insertObject)
{
string newId = Guid.NewGuid().ToString();
IList <IList <Object> > valueData = new List <IList <object> >();
valueData.Add(new List <object>()
{
newId, insertObject
});
ValueRange insertRecord = new ValueRange();
insertRecord.Values = valueData;
SpreadsheetsResource.ValuesResource.AppendRequest request =
Service.Spreadsheets.Values.Append(insertRecord, spreadSheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
if (response.Updates.UpdatedRows != null && response.Updates.UpdatedRows > 0)
{
return(true);
}
return(false);
}
public object writeData(string spreadsheetId, string tenBang, string phamViCell, IList <IList <object> > dataIn)
{
checkConnectServer chk = new checkConnectServer();
if (chk.checkConnectGoogle())
{
/*ĐÂY LÀ HÀM GHI DỮ LIỆU VÀO GOOGLE SHEET
* Hàm này cần đầu vào là ID bảng, Tên bảng, phạm vi điền (VD: A52 hoặc A5:A8 (Hàng ngang)) và mảng dữ liệu đầu vào.*/
UserCredential credential = GetCredential(ScopesWrite);
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "VolunteerDTU"
});
Data.ValueRange requestBody = new Data.ValueRange();
requestBody.Values = dataIn;
string range = "'" + tenBang + "'!" + phamViCell;
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
return(response);
}
else
{
MessageBox.Show("Không có kết nối internet");
return(null);
}
}
public async Task <int?> Append(DataTable table, string spreadsheetID, int deleteHeaders)
{
ValueRange valueRange = new ValueRange();
valueRange.Values = new List <IList <object> >();
for (int i = deleteHeaders; i < table.Rows.Count; i++)
{
valueRange.Values.Add(new List <object>(table.Rows[i].ItemArray));
}
SpreadsheetsResource.ValuesResource.GetRequest getRequest =
new SpreadsheetsResource.ValuesResource.GetRequest(service, spreadsheetID, "A:A");
ValueRange response = getRequest.Execute();
int responseRowsLength = 1;
if (response.Values != null)
{
responseRowsLength = response.Values.Count;
}
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest =
new SpreadsheetsResource.ValuesResource.AppendRequest(
service,
valueRange,
spreadsheetID,
string.Format("A{0}:A", responseRowsLength));
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
return((await appendRequest.ExecuteAsync()).Updates.UpdatedRows);
}
public async Task LogSendedList(IEnumerable <SendedMessage> messages)
{
try {
var service = await _sheetServiceProvider.GetService();
var range = $"{_configService.Config.SpreadsheetLog.Messages}!A:D";
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption
= SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption
= SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange valueRange = new ValueRange();
var listOfObj = messages.Select(x => (IList <object>) new List <object>()
{
"Исходящее", string.Format(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")), x.Message, x.To
}).ToList();
valueRange.Values = listOfObj;//new List<IList<object>> { listOfObj };
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, _configService.Config.SpreadsheetLog.Id, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
request.ResponseDateTimeRenderOption = SpreadsheetsResource.ValuesResource.AppendRequest.ResponseDateTimeRenderOptionEnum.FORMATTEDSTRING;
AppendValuesResponse response = request.Execute();
}
catch (Exception err)
{
_toFileLogger.LogError($"CANNOT LOG TO GOOGLE TABLE SENDED LIST. ERROR: {err.Message}");
}
}
public static void InsertYoutubeVN(List <SpotifyInfo> songs, string range)
{
try
{
var service = UserCredential();
IList <IList <object> > newValues = new List <IList <object> >();
foreach (var song in songs)
{
newValues.Add(new List <object> {
song.YoutubeLink,
song.ViewYoutube
});
}
ValueRange newValueRage = new ValueRange();
newValueRage.Values = newValues;
newValueRage.Range = range;
SpreadsheetsResource.ValuesResource.AppendRequest wrequest = service.Spreadsheets.Values.Append(newValueRage, "1k0G4J_HXLzOvaOvoUPHt8m7S-ogMxaeF53SE6ZfgXfo", range);
wrequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
wrequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var wresponse = wrequest.Execute();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
//小組長用
private void SendGoogleExcelByClass()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
//應用程式的名字需要英文
string ApplicationName = "Get Google SheetData with Google Sheets API";
UserCredential credential;
var folder = System.Web.HttpContext.Current.Server.MapPath("/App_Data/MyGoogleStorage");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
new ClientSecrets
{
ClientId = "117990626740-rptck4cro3bpbu3u7da3t4qlr20i3rsl.apps.googleusercontent.com",
ClientSecret = "zcFr6UCqdX-jo29QFogCcyf1"
},
Scopes,
"user",
CancellationToken.None,
new FileDataStore(folder)).Result;
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName
});
// Define request parameters.
String spreadsheetId = "1IzuEudxNvaO44mTZgXbvcxAfQ21FfxnPeW12PB3jW2M";
//String range = "工作表1!A:B";
String range = "工作表1";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
DateTime.UtcNow.AddHours(8).ToString("yyyy/MM/dd HH:mm:ss"), //時間
dropGroupClass.SelectedItem.Text, //社青
dropGroupName.SelectedItem.Text, //CA202.信豪牧區-彥伯小組
txtEname.Text.Trim(), //流大丹
rdogender0.Checked ? "女" : "男", //男生
dropClothesSize.SelectedItem.Text, //S
//dropCourse.SelectedItem.Text //生命突破
""
};
valueRange.Values = new List <IList <object> > {
oblist
};
valueRange.MajorDimension = "Rows"; //Rows or Columns
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = request.Execute();
}
/// <summary>
/// 傳送考卷給google excel
/// </summary>
private void SendGoogleExcel(AnswerData AnswerData, string _Score)
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
//應用程式的名字需要英文
string ApplicationName = "Get Google SheetData with Google Sheets API";
UserCredential credential;
var folder = System.Web.HttpContext.Current.Server.MapPath("/App_Data/MyGoogleStorage");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
new ClientSecrets
{
ClientId = "117990626740-rptck4cro3bpbu3u7da3t4qlr20i3rsl.apps.googleusercontent.com",
ClientSecret = "zcFr6UCqdX-jo29QFogCcyf1"
},
Scopes,
"user",
CancellationToken.None,
new FileDataStore(folder)).Result;
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName
});
// Define request parameters.
String spreadsheetId = "1gH0Gn-Qlt-VTbfLnqMccLclUqx9kbGztLEANNaC6EpY";
//String range = "工作表1!A:B";
String range = "工作表1";
var valueRange = new ValueRange();
//科目, 小組, 姓名, 手機, 分數, 交卷時間
var oblist = new List <object>()
{
AnswerData.user[0].eclass,
AnswerData.user[0].group,
AnswerData.user[0].name,
AnswerData.user[0].mobile,
_Score,
DateTime.UtcNow.AddHours(8).ToString("yyyy/MM/dd HH:mm:ss")
};
valueRange.Values = new List <IList <object> > {
oblist
};
valueRange.MajorDimension = "Rows"; //Rows or Columns
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = request.Execute();
}
public bool CreateNewSheet <T>(IEnumerable <T> list, string path) where T : class
{
try
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "GoogleSheetsTest";
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,
});
var myNewSheet = new Spreadsheet
{
Properties = new SpreadsheetProperties {
Title = $"New Sheet"
}
};
var googleNewSheet = service.Spreadsheets.Create(myNewSheet).Execute();
// Define request parameters.
String spreadsheetId = googleNewSheet.SpreadsheetId;
String range = $"A:Z";
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(new ValueRange()
{
Values = ToDisplayDataList(list)
}, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
}
catch (Exception ex)
{
return(false);
}
return(true);
}
private void SendButton_Click(object sender, EventArgs e)
{
if (!(OITBox.Text == "") && !(PaisBox.Text == "") && (!(HomNum.Value == 0) || !(MujNum.Value == 0)))
{
ValueRange RangoValor = new ValueRange();
RangoValor.MajorDimension = "ROWS";
FechaCorrecta = new DateTime(Calendario.Value.Year, Calendario.Value.Month, Calendario.Value.Day).ToString("dd-MM-yyyy");
HoraCorrecta = Calendario.Value.Hour.ToString() + ":" + Calendario.Value.Minute.ToString() + ":" + Calendario.Value.Second.ToString();
var oblist = new List <object> {
OITBox.Text, "", PaisBox.Text + "/" + PaisDervBox.SelectedValue, HorarioBox.SelectedValue, FechaCorrecta, TematicaBox.SelectedValue, XperBox.SelectedValue, RegiBox.SelectedValue, DestBox.SelectedValue, PIABox.SelectedValue, HomNum.Value, MujNum.Value
};
RangoValor.Values = new List <IList <object> > {
oblist
};
Contador = Contador + 1;
NumCount.Text = Contador.ToString();
SpreadsheetsResource.ValuesResource.AppendRequest request = Program.service.Spreadsheets.Values.Append(RangoValor, Program.spreadsheetId, Program.Range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
AppendValuesResponse resultadete = request.Execute();
PaisBox.SelectedIndex = 0;
HomNum.Value = 0;
MujNum.Value = 0;
RegiBox.SelectedIndex = 0;
DestBox.SelectedIndex = 0;
XperBox.SelectedIndex = 0;
TematicaBox.SelectedIndex = 0;
}
else if (OITBox.Text == "" && !(PaisBox.Text == ""))
{
mensaje = "El campo OIT es obligatorio";
MessageBox.Show(mensaje);
OITBox.Focus();
}
else if (PaisBox.Text == "" && !(OITBox.Text == ""))
{
mensaje = "El campo Nacionalidad es obligatorio";
MessageBox.Show(mensaje);
PaisBox.Focus();
}
else if ((HomNum.Value == 0) || (MujNum.Value == 0))
{
mensaje = "El campo Hombres o Mujeres debe ser mayor a 0";
MessageBox.Show(mensaje);
HomNum.Focus();
}
else
{
mensaje = "Los siguientes campos son obligatorios: \n -OIT\n -Nacionalidad";
MessageBox.Show(mensaje);
OITBox.Focus();
}
}
/// <summary>
/// Appends a row to the sheet.
/// </summary>
/// <param name="sheetsService">The sheets service created using CreateSheetsServiceInstance</param>
/// <param name="sheetId">Unique Id representing the sheet</param>
/// <param name="sheetName">SheetName, if supplied is used to generate the range</param>
/// <param name="nextRow"></param>
/// <param name="valueRange"></param>
/// <returns></returns>
public AppendValuesResponse AppendSheetData(SheetsService sheetsService, string sheetId, string sheetName, long nextRow, ValueRange valueRange)
{
// (FIX) Row Range is {SheetName}!{row}:{row}
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(valueRange, sheetId,
(string.IsNullOrEmpty(sheetName) == false ? "'" + sheetName + "'!" : "") + nextRow.ToString() + ":" + nextRow.ToString());
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
return(request.Execute());
}
//Appends values at the and of table
public void AppentCellsAtEnd(string range)
{
this.range = this.tab + "!" + range;
this.values.Range = this.range;
SpreadsheetsResource.ValuesResource.AppendRequest request =
this.service.Spreadsheets.Values.Append(this.values, this.sheetID, this.range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
}
private static void AppendGoogleSheetinBatch(IList <IList <Object> > values, string spreadsheetId, string newRange, SheetsService service)
{
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(new ValueRange()
{
Values = values
}, spreadsheetId, newRange);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
}
public void AppendData(String sheetID, ValueRange valueRange, String range)
{
StartService();
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, sheetID, range);
//request.ValueInputOption = valueInputOption;
//request.InsertDataOption = insertDataOption;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
request.Execute();
}
// Appends existing sheet with new range of rows
internal static async Task AppendRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount, int rowsToAdd, IList <IList <object> > newRowsData)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate append rows request.
SpreadsheetsResource.ValuesResource.AppendRequest appendRowsRequest = GoogleServicesExtensionsRequestsFactory.GetAppendRowsRequest(sheetsService, spreadsheetId, sheetTitleId, columnCount, rowCount, rowsToAdd, newRowsData);
// Execute append sheet rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <AppendValuesResponse>(appendRowsRequest);
}
private void AddNewNoteToTable(Note newNote)
{
try
{
if (gSyncSettings == null)
{
return;
}
if (!IsSyncAvaible())
{
OnSyncStateChanged?.Invoke(gSyncState.eFailed);
return;
}
OnSyncStateChanged?.Invoke(gSyncState.eInWork);
if (spreadSheetsService == null)
{
spreadSheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = applicationName,
});
}
IList <IList <object> > insertValues = new List <IList <object> >();
IList <object> ROW = new List <object>();
ROW.Add(JsonConvert.SerializeObject(newNote, jsonSettings));
insertValues.Add(ROW);
if (newNote.GetType() == typeof(Screenshot))
{
Screenshot screen = newNote as Screenshot;
UploadScreenshot(screen);
}
ValueRange body = new ValueRange();
body.Values = insertValues;
SpreadsheetsResource.ValuesResource.AppendRequest result = spreadSheetsService.Spreadsheets.Values.Append(body, gSyncSettings.spreadshetrID, "Notesieve!A1:B");
result.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
result.Execute();
UpdateDataVersion();
OnSyncStateChanged?.Invoke(gSyncState.eSuccessPut);
}
catch (System.Net.Http.HttpRequestException e)
{
OnSyncStateChanged?.Invoke(gSyncState.eLostConnection);
}
catch
{
OnSyncStateChanged?.Invoke(gSyncState.eFailed);
}
}
public void WriteToSheetAtRange(string sheetName, IList <IList <object> > data, string range)
{
SpreadsheetsResource.ValuesResource.AppendRequest request =
Service.Spreadsheets.Values.Append(new ValueRange()
{
Values = data
}, SheetId, GetSheetAndRangeName(sheetName, range));
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
}
public async Task UpdateSheet(IList <IList <Object> > newRecords, string range = _logBookRange)
{
SpreadsheetsResource.ValuesResource.AppendRequest request =
_sheetsService.Spreadsheets.Values.Append(new ValueRange()
{
Values = newRecords
}, _spreadsheetId, GetNewRange(range));
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = await request.ExecuteAsync();
}
internal static void NewMember(SheetsService service, string spreadsheetId, DatabaseSheet sheet, IList <IList <object> > values)
{
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(new ValueRange()
{
Values = values
}, spreadsheetId, $"{sheet.Name}!{sheet.DatabaseColumns.FirstOrDefault().Value.ColumnLetters}{sheet.ColumnHeadersRow+2}:{sheet.DatabaseColumns.LastOrDefault().Value.ColumnLetters}{sheet.ColumnHeadersRow + 2}");
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
}
public void insertData()
{
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);
}
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// The ID of the spreadsheet to Append.
string spreadsheetId = "YOUR SPREADSHEET ID"; // TODO: Update placeholder value.
// The A1 notation of the values to Append.
string range = "YOUR SPREADSHEET RANGE"; // TODO: Append placeholder value.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// TODO: Assign values to desired properties of `requestBody`. All existing
// properties will be replaced:
ValueRange requestBody = new ValueRange();
var oblist = new List <object>()
{
"YOUR SPREADSHEET VALUE"
};
requestBody.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
AppendValuesResponse response = request.Execute();
}
public void WriteValues(List <IList <object> > values)
{
ValueRange body = new ValueRange();
body.MajorDimension = "ROWS";
body.Values = values;
// Define request parameters.
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(body, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse response = request.Execute();
}
public static bool UpdateTrade(DateTime dateTime, double amount, string buyExchange, double buyPrice,
string sellExchange, double sellPrice, double gainAmount, double gainPerc)
{
try
{
ValueRange valueRangeObj = new ValueRange();
valueRangeObj.MajorDimension = "ROWS";
var valuesList = new List <object>()
{
dateTime.ToString("MM/dd/yyyy HH:mm:ss"),
amount,
buyExchange,
buyPrice,
sellExchange,
sellPrice,
gainAmount,
gainPerc
};
valueRangeObj.Values = new List <IList <object> > {
valuesList
};
// Create Google Sheets API service.
sheetService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
String range = string.Empty;
range = string.Format("{0}!{1}", sheetTrades, "A2:N");
//APPEND
//https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
SpreadsheetsResource.ValuesResource.AppendRequest requestAppend = sheetService.Spreadsheets.Values.Append(valueRangeObj, spreadsheetId, range);
requestAppend.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
requestAppend.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
AppendValuesResponse responseAppend = requestAppend.Execute();
return(true);
}
catch (Exception ex)
{
Console.WriteLine("\nERRO UpdateTradeExecuted: {0}\n{1}", ex.Message, ex.InnerException);
return(false);
}
}
//***********************************************************************
// Definition of append() *
// Takes a list of objects as input and forms a 2D list of objects which*
// forms a ValueRange object. The google API AppendRequest object is *
// instantiated with the ValueRange, a Google spreadsheet ID number and *
// a defined range of cells. The append request is executed. *
//***********************************************************************
public void append(IList <Object> oblist, string spreadsheetID, string range)
{
List <IList <Object> > values = new List <IList <object> > {
oblist
};
ValueRange valueRange = new ValueRange();
valueRange.Values = values;
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(valueRange, spreadsheetID, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
request.Execute();
}
private void AddEntry(string SpreadSheet, List <object> objectArray)
{
string range = $"{SpreadSheet}!A:Z";
ValueRange valueRange = new ValueRange
{
Values = new List <IList <object> > {
objectArray
}
};
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadSheetID, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse appendRespond = appendRequest.Execute();
}
