/// <summary>
///
/// </summary>
/// <param name="spreadsheetId"></param>
/// <param name="sheetTitle"></param>
/// <param name="headers"></param>
/// <param name="data"></param>
/// <returns></returns>
public async Task <BatchUpdateValuesResponse> BatchUpdateAsync(string spreadsheetId, string sheetTitle, List <IList <object> > headers, List <IList <object> > data)
{
// How the input data should be interpreted.
const string valueInputOption = "USER_ENTERED";
var headerRange = CalculateCellRange(headers, "A", 1);
var dataRange = CalculateCellRange(data, "A", 2);
// The new values to apply to the spreadsheet.
var reqData = new List <ValueRange>
{
new ValueRange
{
Range = $"{sheetTitle}!{headerRange}",
Values = headers
},
new ValueRange
{
Range = $"{sheetTitle}!{dataRange}",
Values = data
}
};
var requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = valueInputOption,
IncludeValuesInResponse = true,
Data = reqData
};
SpreadsheetsResource.ValuesResource.BatchUpdateRequest batchUpdateRequest = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateValuesResponse batchUpdateResponse = await batchUpdateRequest.ExecuteAsync();
return(batchUpdateResponse);
}
public static string UpdateData(List <IList <object> > data)
{
String range = "Page1!A1";
string valueInputOption = "USER_ENTERED";
// The new values to apply to the spreadsheet.
List <ValueRange> updateData = new List <ValueRange>();
var dataValueRange = new ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadSheetId);
BatchUpdateValuesResponse response = request.Execute();
// Data.BatchUpdateValuesResponse response = await request.ExecuteAsync(); // For async
return(JsonConvert.SerializeObject(response));
}
public void PrintRequestBodyData(BatchUpdateValuesRequest requestBody)
{
// Count = 1;
IList <ValueRange> requestBodyData = requestBody.Data;
C.WriteLine();
_helpers.Spotlight("DATA TO BE ADDED");
C.WriteLine("-------------------------------------------------------");
foreach (ValueRange vR in requestBodyData)
{
// listOfLists: System.Collections.Generic.List`1[System.Collections.Generic.IList`1[System.Object]]
// count = the # of lists within the listOfLists
IList <IList <object> > listOfLists = vR.Values;
int listCount = 1;
// list: System.Collections.Generic.List`1[System.Object]
foreach (IList <object> list in listOfLists)
{
C.WriteLine($"LIST: {listCount}");
int dataCounter = 1;
foreach (object data in list)
{
C.WriteLine($"{dataCounter}. {data}");
dataCounter++;
}
C.WriteLine();
listCount++;
}
}
C.WriteLine($"-------------------------------------------------------\n");
}
// Updates data in a specified range.
internal static SpreadsheetsResource.ValuesResource.BatchUpdateRequest GetUpdateRangeDataRequest(SheetsService sheetsService, string spreadsheetId, string rangeString, IList <IList <object> > rangeData)
{
// Declare update data list.
List <ValueRange> data = new List <ValueRange>();
// Construct new instance of ValueRange.
ValueRange valueRange = new ValueRange();
// Assign value range locations/dimensions coordinates.
valueRange.Range = rangeString;
// Assign rangeData to update.
valueRange.Values = rangeData;
// Add value range to data list.
data.Add(valueRange);
// Build google apis request body.
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
// Set request body value input option - "RAW" or "USER_ENTERED"
requestBody.ValueInputOption = "RAW";
// Assign data into the request body.
requestBody.Data = data;
// Construct and return google apis request based on the constructed request body for the spreadsheet specified with the provided spreadsheet id.
return(sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId));
}
/// <summary>
/// Update a value of the spread sheet
/// </summary>
/// <param name="value">values to be set</param>
/// <param name="range">place to setit</param>
/// <returns></returns>
public static BatchUpdateValuesResponse updateValue(string value, string range)
{
IList <IList <Object> > values = new List <IList <object> >
{
new List <object>()
};
values[0].Add(value);
ValueRange body = new ValueRange()
{
Values = values
};
List <ValueRange> ranges = new List <ValueRange>();
ranges.Add(new ValueRange()
{
Range = range, Values = values
});
BatchUpdateValuesRequest body2 = new BatchUpdateValuesRequest();
body2.Data = ranges;
body2.ValueInputOption = "USER_ENTERED";
return(Service.Spreadsheets.Values.BatchUpdate(body2, info.SheetId).Execute());
}
private async Task UpdateSheet(IList <ValueRange> valueRanges)
{
try
{
foreach (var item in valueRanges)
{
item.Range = "A5:G2000";
}
var requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = "USER_ENTERED",
Data = valueRanges
};
var request = Service.Spreadsheets.Values.BatchUpdate(requestBody, SheetId);
await request.ExecuteAsync(Cts.Token).ConfigureAwait(false);
}
catch (TaskCanceledException tce)
{
if (!Cts.IsCancellationRequested)
{
Logger.Warn(tce);
}
}
catch (Exception e)
{
if (e is GoogleApiException gae && gae.Error?.Code == 429)
{
Logger.Warn(gae, "Too many Google Api requests. Cooling down.");
await Task.Delay(5000, Cts.Token).ConfigureAwait(false);
}
// Очистка старых записей(если количество БД на сервере уменьшилось, лишние строки в таблице должны быть удалены) и публикация новых.
public async Task UpdateSpreadSheetAsync(Dictionary <string, List <DbSize> > serverInfoCollection)
{
BatchClearValuesRequest clearRequest = new BatchClearValuesRequest();
clearRequest.Ranges = new List <string>();
foreach (var server in serverInfoCollection)
{
clearRequest.Ranges.Add($"{server.Key}!A2:D50");
}
// Асинхронно запускаем очистку таблицы
var clearTask = _service.Spreadsheets.Values.BatchClear(clearRequest, _spreadSheetId).ExecuteAsync();
BatchUpdateValuesRequest addRequest = new BatchUpdateValuesRequest();
addRequest.Data = new List <ValueRange>();
addRequest.ValueInputOption = "USER_ENTERED";
foreach (var server in serverInfoCollection)
{
addRequest.Data.Add(replaceContnet(server.Key, server.Value));
}
//Ждем пока завершится асинхронная задача очистики и отправляем запрос на запись обновленных данных
await clearTask;
await _service.Spreadsheets.Values.BatchUpdate(addRequest, _spreadSheetId).ExecuteAsync();
}
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);
}
private static BatchUpdateValuesResponse updateSheet(
BatchUpdateValuesRequest request,
string spreadsheetId,
SheetsService service)
{
return(service.Spreadsheets.Values.BatchUpdate(request, spreadsheetId).Execute());
}
private static BatchUpdateValuesRequest GetBatchUpdateRequest()
{
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.Data = new List <ValueRange>();
requestBody.ValueInputOption = "USER_ENTERED";
return(requestBody);
}
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();
}
// STATUS [ August 3, 2019 ] : this works
// BatchUpdateRequest
// * Sets values in 1+ ranges of spreadsheet
// * Caller must specify sheet ID, a valueInputOption, and 1+ ValueRanges
// * Type is Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+BatchUpdateRequest
// * See: https://bit.ly/2KBkBbt
private BatchUpdateRequest CreateBatchUpdateRequest(BatchUpdateValuesRequest requestBody, string spreadSheetId)
{
BatchUpdateRequest request = _sheetsService.Spreadsheets.Values.BatchUpdate(
requestBody,
spreadSheetId
);
return(request);
}
private static BatchUpdateValuesRequest getUpdateValuesRequest(
List <ValueRange> requestData,
string valueInputOption = "USER_ENTERED")
{
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.Data = requestData;
requestBody.ValueInputOption = valueInputOption;
return(requestBody);
}
//public async Task<int> SaveDestinationData(List<object> sourceData)
//public void SaveDestinationData(List<object> sourceData)
public void SaveDestinationData(List <IList <object> > sourceData)
{
try
{
SheetsServiceInitializer();
var _spreadsheetId = SourceDetail["SpreadsheetId"];
var _range = $"{SourceDetail["SheetName"]}!{SourceDetail["Range"]}";
// var data = new List<IList<object>>();
if (sourceData.Count > 0)
{
//var list = (from i in (ExpandoObject)sourceData[0] select i.Key).Cast<object>().ToList();
//data.Add(list);
ClearAllValuesFromSheet(_spreadsheetId, 0);
//data.AddRange(sourceData.Select(item => (from kvp in (ExpandoObject)item select kvp.Value).ToList())
// .Cast<IList<object>>().ToList());
var valueInputOption = "USER_ENTERED";
var updateData = new List <ValueRange>();
var dataValueRange = new ValueRange
{
Range = _range,
//Values = data
Values = sourceData
};
updateData.Add(dataValueRange);
var requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = valueInputOption,
Data = updateData
};
var requestBodyEmpty = new BatchUpdateValuesRequest
{
ValueInputOption = valueInputOption,
Data = null
};
var request = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBodyEmpty, _spreadsheetId);
var response = request.Execute();
request = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, _spreadsheetId);
response = request.Execute();
}
}
catch //(Exception ex)
{
}
}
// STATUS [ August 3, 2019 ] : this works
// BatchUpdateValuesRequest
// * "The request for updating more than one range of values in a spreadsheet."
// * See: https://bit.ly/3364meV
private static BatchUpdateValuesRequest CreateBatchUpdateValuesRequest(string valueInputOption, IList <ValueRange> updateData)
{
// ValueInputOption
// * "How the input data should be interpreted."
// Data
// * "The new values to apply to the spreadsheet"
// * Type is System.Collections.Generic.List`1[Google.Apis.Sheets.v4.Data.ValueRange]
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = valueInputOption,
Data = updateData,
};
return(requestBody);
}
public async Task BatchUpdate(string spreadsheetId, IEnumerable <UpdateRange> updateDatas)
{
BatchUpdateValuesRequest body = new BatchUpdateValuesRequest();
body.Data = updateDatas.Select(d => new ValueRange
{
Range = d.Range.GetStr(),
Values = d.Data,
MajorDimension = "ROWS"
}).ToList();
body.ValueInputOption = "USER_ENTERED";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request =
_service.Spreadsheets.Values.BatchUpdate(body, spreadsheetId);
await autoAuthorize(request);
}
/// <summary>
/// Register a user on the spread sheet
/// </summary>
/// <param name="id">Id of the user</param>
/// <param name="username">Username</param>
/// <returns>If the user was registered</returns>
public static bool register(Int64 id, string username)
{
//check that the user is not in the list
List <LoginToken> tokens = GetLoginTokens();
if (tokens.Any(item => item.id == id))
{
return(false);
}
// get the new user row number
int row = 2;
if (tokens.Count != 0)
{
row = tokens.Max(item => item.row) + 1;
}
// get the title of the spread sheet
var spreadSheet = Service.Spreadsheets.Get(info.SheetId).Execute();
string sheetTitle = spreadSheet.Sheets[0].Properties.Title;
// Formatt the user data
IList <IList <object> > data = new List <IList <object> >();
data.Add(new List <object>());
data[0].Add($"{id}");
data[0].Add(username);
data[0].Add(DateTime.Now.ToString("yyyy/MM/dd"));
List <ValueRange> ranges = new List <ValueRange>();
ranges.Add(new ValueRange()
{
Range = $"{sheetTitle}!A{row}:C{row}", Values = data
});
//Create a new request to change sheet cells
BatchUpdateValuesRequest body2 = new BatchUpdateValuesRequest();
body2.Data = ranges;
body2.ValueInputOption = "USER_ENTERED";
//submit the request to google sheets
BatchUpdateValuesResponse result = Service.Spreadsheets.Values.BatchUpdate(body2, info.SheetId).Execute();
return(result.Responses.Count == 1);
}
public BatchUpdateValuesResponse UpdateData(string sheetname, string column, int row, List <IList <object> > data)
{
List <ValueRange> updateData = new List <ValueRange>();
var dataValueRange = new ValueRange();
dataValueRange.Range = $"{sheetname}!{column.ToUpper()}{row}";
dataValueRange.Values = data;
updateData.Add(dataValueRange);
BatchUpdateValuesRequest requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = "USER_ENTERED";
requestBody.Data = updateData;
var request = SheetsService.Spreadsheets.Values.BatchUpdate(requestBody, SpreadsheetId);
return(request.Execute());
}
// STATUS [ August 3, 2019 ] : this works
/// <param name="data">todo: describe data parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="sheetName">todo: describe sheetName parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="range">todo: describe range parameter on WriteGoogleSheetColumnsAsync</param>
/// <param name="jsonGroupName">todo: describe jsonGroupName parameter on WriteGoogleSheetColumnsAsync</param>
/// <example>
/// await _gSC.WriteGoogleSheetColumnsAsync(listOfLists, "YAHOO_TRENDS","A1:Z1000","CoreCalculator");
/// </example>
public async Task <string> WriteGoogleSheetColumnsAsync(IList <IList <object> > data, string sheetName, string range, string jsonGroupName)
{
// _helpers.StartMethod();
ConnectToGoogle();
_spreadSheetId = SelectGoogleSheetToRead(jsonGroupName, "SpreadsheetId");
ValueRange dataValueRange = SetSheetDataValueRange(_columnsMajorDimension, sheetName, range, data);
List <ValueRange> updateData = CreateValueRangeList(dataValueRange);
BatchUpdateValuesRequest requestBody = CreateBatchUpdateValuesRequest(_userEnteredValueInputOption, updateData);
BatchUpdateRequest request = CreateBatchUpdateRequest(requestBody, _spreadSheetId);
BatchUpdateValuesResponse response = await CreateBatchUpdateValuesResponseAsync(request).ConfigureAwait(false);
// PRINTERS
// PrintRequestBodyData(requestBody);
// PrintUpdateRangeDetails(sheetName, range, jsonGroupName, spreadsheetId);
// LogSpreadsheetUpdateDetails(response);
return(JsonConvert.SerializeObject(response));
}
/// <summary>
///
/// </summary>
/// <param name="docName"></param>
/// <param name="tabName"></param>
/// <param name="instances"></param>
/// <param name="instanceType"></param>
/// <param name="saveOnlyTheseMembersStr">Comma-separated list of names of member properties to save.</param>
public static void SaveChanges(string docName, string tabName, object[] instances, Type instanceType, string saveOnlyTheseMembersStr = null)
{
if (instances == null || instances.Length == 0)
{
return;
}
ValidateDocAndTabNames(docName, tabName, true);
string[] saveOnlyTheseMembers = null;
if (saveOnlyTheseMembersStr != null)
{
saveOnlyTheseMembers = saveOnlyTheseMembersStr.Split(',');
}
string spreadsheetId = spreadsheetIDs[docName];
List <string> headerRow;
IList <IList <object> > allRows;
GetHeaderRow(docName, tabName, out headerRow, out allRows);
MemberInfo[] indexFields = GetSerializableFields <IndexerAttribute>(instanceType);
MemberInfo[] serializableFields = GetSerializableFields <ColumnAttribute>(instanceType);
BatchUpdateValuesRequest requestBody = GetBatchUpdateRequest();
for (int i = 0; i < instances.Length; i++)
{
int rowIndex = GetInstanceRowIndex(instances[i], indexFields, allRows, headerRow);
if (rowIndex < 0)
{
AddRow(tabName, serializableFields, headerRow, serializableFields, allRows, instances[i], requestBody);
}
else
{
UpdateRow(tabName, instances, saveOnlyTheseMembers, headerRow, allRows, serializableFields, i, rowIndex, requestBody);
}
}
ExecuteRequest(spreadsheetId, requestBody);
}
/// <summary>
/// Updates through a batch update from a given start cell
/// </summary>
/// <param name="start">Start cell</param>
/// <param name="data">Rows+Cells to update</param>
/// <returns>Updated cells</returns>
async Task <int> SetDataAsync(string start, List <IList <object> > data, string valueInputOption = "USER_ENTERED")
{
BatchUpdateValuesRequest update = new BatchUpdateValuesRequest()
{
Data = new List <ValueRange>()
{
new ValueRange()
{
Range = $"{Table}!{start}",
Values = data
}
},
ValueInputOption = valueInputOption
};
var request = _service.Spreadsheets.Values.BatchUpdate(update, SheetId);
var response = await request.ExecuteAsync().ConfigureAwait(false);
return(response.TotalUpdatedCells ?? 0);
}
private async Task UpdateSheet(IList <ValueRange> valueRanges)
{
try
{
var requestBody = new BatchUpdateValuesRequest
{
ValueInputOption = "USER_ENTERED",
Data = valueRanges
};
var request = service.Spreadsheets.Values.BatchUpdate(requestBody, Settings.Instance.SheetId);
await request.ExecuteAsync(cts.Token);
}
catch (Exception e)
{
if (e is GoogleApiException gae && gae.Error.Code == 429)
{
Logger.Error(gae, "Too many Google Api requests. Cooling down.");
await Task.Delay(5000, cts.Token);
}
public static BatchUpdateValuesResponse BatchUpdateSheet(
string spreadsheetId,
Spreadsheets.SheetsRanges.SheetRangeData rangeData,
SheetsService service)
{
// gaunami duomenys iš sheetų
IList <IList <Object> > sheetValues = getRangeValues(spreadsheetId, rangeData.RangeAddress, service);
List <ValueRange> requestData = new List <ValueRange>();
string dateFormat = Properties.Settings.Default.DateFormat;
object valueToWrite = DateTime.Now.Date.ToString(dateFormat);
// pridedami duomenys į requestData
addDataToRequest(sheetValues, rangeData, valueToWrite, requestData);
// sukuriamas batch update request
BatchUpdateValuesRequest request = getUpdateValuesRequest(requestData);
// updateinamas spreadsheet
return(updateSheet(request, spreadsheetId, service));
}
public async Task BatchUpdate(string sheetId, string majorDimension, Dictionary <string, IList <IList <object> > > values)
{
var updateRequestBody = new BatchUpdateValuesRequest {
Data = new List <ValueRange>(),
ValueInputOption = "RAW",
IncludeValuesInResponse = false
};
foreach (var data in values)
{
updateRequestBody.Data.Add(
new ValueRange()
{
MajorDimension = majorDimension,
Range = data.Key,
Values = data.Value
});
}
var updateRequest = Service.Spreadsheets.Values.BatchUpdate(updateRequestBody, sheetId);
var updateResponse = await updateRequest.ExecuteAsync();
}
// Batch Update
public WrappedResponse UpdateRange(string spreadsheetId, IList <RangeUpdateModel> models)
{
try
{
var data = new List <ValueRange>();
foreach (var model in models)
{
var valueRange = new ValueRange();
valueRange.Values = model.Values;
valueRange.MajorDimension = model.Dimension.GetString();
valueRange.Range = model.Range;
data.Add(valueRange);
}
var requestBody = new BatchUpdateValuesRequest();
requestBody.ValueInputOption = "USER_ENTERED"; // Google's magic string
requestBody.Data = data;
var request = _googleSheets.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
var response = request.Execute();
if ((response.TotalUpdatedCells ?? 0) == 0)
{
throw new ApplicationException("Update Range had no effect");
}
return(new WrappedResponse {
Success = true
});
}
catch (Exception e)
{
return(new WrappedResponse {
Success = false, Exception = e
});
}
}
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 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 static void UpdateRow(string tabName, object[] instances, string[] saveOnlyTheseMembers, List <string> headerRow, IList <IList <object> > allRows, MemberInfo[] serializableFields, int i, int rowIndex, BatchUpdateValuesRequest requestBody)
{
for (int j = 0; j < serializableFields.Length; j++)
{
MemberInfo memberInfo = serializableFields[j];
if (!HasMember(saveOnlyTheseMembers, memberInfo.Name))
{
continue;
}
if (instances[i] is ITrackPropertyChanges trackPropertyChanges && trackPropertyChanges.ChangedProperties != null)
{
if (trackPropertyChanges.ChangedProperties.IndexOf(memberInfo.Name) < 0)
{
continue;
}
}
int columnIndex = GetColumnIndex(headerRow, GetColumnName <ColumnAttribute>(memberInfo));
string existingValue = null;
if (columnIndex < allRows[rowIndex].Count) // Some rows may have fewer columns because the Google Sheets engine will efficiently return only the columns holding data.
{
existingValue = GetExistingValue(allRows, columnIndex, rowIndex);
}
string value = GetValue(instances[i], memberInfo);
if (existingValue == null /* New */ || value != existingValue /* Mod */)
{
//ValueRange body = new ValueRange();
//body.MajorDimension = "ROWS";
//body.Range = $"{tabName}!{range}";
//body.Values = new List<IList<object>>();
//body.Values.Add(new List<object>());
//body.Values[0].Add(value);
ValueRange body = GetValueRange(tabName, columnIndex, rowIndex, value);
requestBody.Data.Add(body);
//SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(body, spreadsheetId, body.Range);
//Execute(request);
}
}
}
private static void ExecuteRequest(string spreadsheetId, BatchUpdateValuesRequest requestBody)
{
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
Execute(request);
}
static int AddRow(string tabName, MemberInfo[] indexFields, List <string> headerRow, MemberInfo[] serializableFields, IList <IList <object> > allRows, object instance, BatchUpdateValuesRequest requestBody)
{
foreach (MemberInfo memberInfo in indexFields)
{
int columnIndex = GetColumnIndex(headerRow, GetColumnName <ColumnAttribute>(memberInfo));
int rowIndex = allRows.Count;
ValueRange body = GetValueRange(tabName, columnIndex, rowIndex, GetValue(instance, memberInfo));
requestBody.Data.Add(body);
}
AddInstance(allRows, headerRow, serializableFields, instance);
return(allRows.Count - 1);
}
