private void UpdateEntry(string SpreadSheet, List <object> objectArray)
{
ValueRange values = GetValues(SpreadSheet);
int i = 1;
bool hasBeenSet = false;
foreach (IList <object> row in values.Values.Skip(1))
{
i++;
if (row[0].ToString() == objectArray[0].ToString())
{
hasBeenSet = true; break;
}
}
if (!hasBeenSet)
{
return;
}
string range = $"{SpreadSheet}!A{i}:Z{i}";
ValueRange valueRange = new ValueRange
{
Values = new List <IList <object> > {
objectArray
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadSheetID, range);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse updateRespond = updateRequest.Execute();
}
public bool InlineEditTable(string spreadsheetId, string sheetName, string text)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
var list = new List <object>()
{
text
};
valueRange.Values = new List <IList <object> > {
list
};
SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, sheetName);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse response = request.Execute();
return(true);
}
public void AddDamageNote()
{
// write the data (f5)
String spreadsheetId2 = "1tql5eJCaCZfaMGoFo01M4n-p1k6BT7LSrqyOuHdDOwY";
//String range2 = "Sheet2!G" + chromebookUser;
String range2 = "Sheet3!I" + chromebookUser;
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
addnote
};
valueRange.Values = new List <IList <object> > {
oblist
};
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
index = 1;
}
/// <summary>
/// Writes Data in list to rectangle specified by startCell and endCell. This will overwrite existing data
/// </summary>
/// <param name="spreadSheetID"></param>
/// <param name="cellData"></param>
/// <param name="SheetName"></param>
/// <param name="startCell"></param>
/// <param name="endCell"></param>
public static void WriteData(String spreadSheetID, List <object> cellData, string SheetName, String startCell, String endCell)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";//COLUMNS
//var oblist = new List<object>() {};
var oblist = cellData;
valueRange.Values = new List <IList <object> > {
oblist
};
// Define request parameters.
String range = SheetName + "!" + startCell + ":" + endCell;
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, spreadSheetID, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result2 = request.Execute();
}
//Overwrites cells
static void WriteData(string SheetTitle, string SheetName, string Column, int StartingCell, List <object> cellData)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
//var oblist = new List<object>() {};
var oblist = cellData;
valueRange.Values = new List <IList <object> > {
oblist
};
// Define request parameters.
String spreadsheetId = GetSheetIdByTitle(SheetTitle);
String range = SheetName + "!" + Column + Convert.ToString(StartingCell) + ":" + Column + Convert.ToString(StartingCell + cellData.Count);
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = request.Execute();
}
public void EditSheet(String txt, int row, String col)
{
var service = GetService_sheetv4();
var last = currSheet.Sheets.Last();
int lastrow = GetLastRow();
String range = $"{last.Properties.Title}!{col}{row}";
ValueRange valueRange = new ValueRange();
var oblist = new List <object>()
{
txt
};
valueRange.Values = new List <IList <object> > {
oblist
};
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
Console.WriteLine("Writing location: " + range);
Console.WriteLine("Content: " + txt);
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, SpreadSheetID, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
public async Task iLvl(string data)
{
bool valid = true;
if (data.Length > 3 || !data.All(char.IsDigit))
{
valid = false;
}
if (valid == false)
{
await ReplyAsync("Invalid parameter: **" + data + "**. Please type !halp to see a list of supported parameters."); return;
}
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added yourself with the !add *<Player Name>* command"); return;
}
range = "E" + Utils.GetRowByDiscord(Program.context.User.ToString());
Console.WriteLine(range);
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
var info = new List <object>()
{
data
};
requestbody.Values = new List <IList <object> > {
info
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
await ReplyAsync("Your item level has been set to: **" + data + "**.");
}
private static void WriteTable(SheetsService service, Table table, int indexToStartAt)
{
IList <IList <object> > matrix = new List <IList <object> >();
foreach (Team t in table.GetTeams())
{
var score = t.score[0].ToString() + ":" + t.score[1];
List <object> entry = new List <object>
{
t.name,
t.wins,
t.draws,
t.losses,
t.points,
score
};
matrix.Add(entry);
}
var valueRange = new ValueRange
{
Values = matrix
};
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, Sheet, $"Premier League 2020/21!O{indexToStartAt}:T{indexToStartAt+19}");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = request.Execute();
}
/// <summary>
/// Writes a two dimensional array of data to the sheet.
/// </summary>
/// <param name="spreadsheetId">The id of the spreadsheet to be read. docs.google.com/spreadsheets/d/[SpreadsheetIdIsHere]/edit#gid=0</param>
/// <param name="rangeGet">The range to read data from. format: "sheetname!A1:E32"</param>
/// <param name="readByColumn">If true data is written by column, if false data is written by row</param>
/// <returns></returns>
public static bool WriteDataToSheet(string spreadsheetId, string rangeWrite, IList <IList <object> > dataToWrite, Dimension dimension = Dimension.Columns)
{
if (m_shouldRun == false)
{
return(false);
}
if (dimension >= Dimension.Count)
{
throw new ArgumentOutOfRangeException(m_errorArgumentOutOfRange);
}
ValueRange values = new ValueRange();
values.MajorDimension = m_dimensions[(int)dimension];
values.Range = rangeWrite;
values.Values = dataToWrite;
SpreadsheetsResource.ValuesResource.UpdateRequest requestUpdate = TCOSheetsInterface.service.Spreadsheets.Values.Update(values, spreadsheetId, values.Range);
requestUpdate.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result = requestUpdate.Execute();
return(true);
}
public void UpdateSheet(List <DayCareModel> list, string workSheetName, string spreadsheetId)
{
var service = AuthorizeGoogleApp();
String range = string.Format("{0}!{1}", workSheetName, "A:AA"); //"工作表1!A1:E";
ValueRange valueRange = new ValueRange();
valueRange.Range = range;
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
"Status", "Facility Name", "Facility Street", "Facility City", "Facility State", "Facility Zip", "Facility County", "Facility Phone", "Facility LicenseStatus",
"Licensee Name", "Licensee Address", "Licensee Phone",
"License Number", "License FacilityType", "License Capacity", "License EffectiveDate", "License ExpirationDate", "License PeriodOfOperation",
"DaysOpen Sunday", "DaysOpen Monday", "DaysOpen Tuesday", "DaysOpen Wednesday", "DaysOpen Thursday", "DaysOpen Friday", "DaysOpen Saturday",
"ServicesOffered FullDay", "ServicesOffered Provides"
};
var dataList = HandleData(list);
valueRange.Values = new List <IList <object> > {
oblist
};
foreach (var r in dataList)
{
valueRange.Values.Add(r);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
public async Task <ServiceResponse <UpdateValuesResponse> > UpdateValue(UpdateValuesRequest req)
{
ServiceResponse <UpdateValuesResponse> serviceResponse = new ServiceResponse <UpdateValuesResponse>();
try
{
Value obj = _context.Values.FirstOrDefault(a => a.Id == req.Id);
obj.Name = req.Name;
obj.Age = req.Age != 0 ? req.Age : obj.Age;
_context.SaveChanges();
Value res = _context.Values.FirstOrDefault(a => a.Id == req.Id);
UpdateValuesResponse valuesResponse = new UpdateValuesResponse
{
Id = res.Id,
Name = res.Name,
Age = res.Age
};
serviceResponse.Data = valuesResponse;
}
catch (Exception ex)
{
serviceResponse.Success = false;
serviceResponse.Message = ex.Message;
}
return(serviceResponse);
}
// UpdateSheetAsync(): Will update information for specific sheet
// [param]sheetName: the name for the specific sheet the user wants from the spreadsheet file
// [param]startRange: specific cell number where reading will start
// [param]finishRange: specific cell number where reading will end
// [param]values: the sheet will be updated with these values
public async Task <int?> UpdateSheetAsync(string sheetName, string startRange, string finishRange, List <List <string> > values)
{
string range = "'" + sheetName + "'!" + startRange + ":" + finishRange;
var valueRange = new ValueRange();
//We need a IList<IList<object>> object to send the data. This loop is to convert [param]values into that object
IList <IList <object> > valuesObject = new List <IList <object> >();
foreach (var rowValue in values)
{
var rowObject = new List <object>();
foreach (var columnValue in rowValue)
{
rowObject.Add(columnValue);
}
valuesObject.Add(rowObject);
}
valueRange.Values = valuesObject;
SpreadsheetsResource.ValuesResource.UpdateRequest request = Service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse response = await request.ExecuteAsync();
return(response.UpdatedCells);
}
string Upload(string str)
{
string range = string.Format("{0}", r.Next(1, RowCount + 1));
string googleTranslate = @"=GOOGLETRANSLATE(A" + range + @",""" + source + @""",""" + target + @""")";
ValueRange VRx = new ValueRange();
IList <IList <object> > xx = new List <IList <object> >();
xx.Add(new List <object> {
"'" + str, googleTranslate
});
VRx.Values = xx;
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(VRx, spreadsheetId, title + "!A" + range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result = update.Execute();
/* 두번에 나눠서 처리할 경우, 위 ValueInputOptionEnum를 USERENTERED 말고 RAW를 쓴다
* xx = new List<IList<object>>();
* xx.Add(new List<object> { googleTranslate });
* VRx.Values = xx;
* update = service.Spreadsheets.Values.Update(VRx, spreadsheetId, title + "!B" + range);
* update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
* result = update.Execute();
*/
return(title + "!B" + range);
}
public void AddData(string spreadsheetId, string range, string[] data)
{
SheetsService sheetsService = Connect();
ClearValuesRequest clearrequestBody = new ClearValuesRequest();
//var result = sheetsService.Spreadsheets.Values.Clear(clearrequestBody, spreadsheetId, "Sheets1!A3:C").Execute();// Append( valueRange, spreadsheetId, "Sheets1!A6").Execute();
ValueRange body = new ValueRange();
List <Object> sub = new List <object>();
sub.Add("qwerewtreytrjy");
List <List <Object> > list1 = new List <List <object> >();
list1.Add(sub);
IList <IList <Object> > values = GetData(spreadsheetId, range);
values.Clear();
values.Add(data);
body.Values = values;
SpreadsheetsResource.ValuesResource.UpdateRequest request1 = sheetsService.Spreadsheets.Values.Update(body, spreadsheetId, range);
request1.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
Console.WriteLine(request1.ValueInputOption);
UpdateValuesResponse result = request1.Execute();
Console.WriteLine(JsonConvert.SerializeObject(result));
}
//write a formula of expected value in the spreadsheet cell
public List <FormTable> PutFormula()
{
//the cell with formula
String range = "A" + (N + 1);
//a range of values - only columns for this task
ValueRange valueRange = new ValueRange
{
MajorDimension = "COLUMNS"
};
//formula as query
task = "SV";
var q = "SELECT * FROM FormTable WHERE CodeTask ={0}";
var query = db.Database.SqlQuery <FormTable>(q, task).FirstOrDefault();
var tmp = query.Formula.Replace("N", N.ToString());
var obj = new List <object> {
tmp
};
valueRange.Values = new List <IList <object> > {
obj
};
//update the spreadsheet
//request
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, sheetID, range);
//allowance for editing the spreadsheet
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
//execute the request
UpdateValuesResponse res = update.Execute();
return(null);
}
private static UpdateValuesResponse UpdateGoogleRequest(SheetsService service, ValueRange valueRange, string spreadsheetId, string range)
{
SpreadsheetsResource.ValuesResource.UpdateRequest updateReq = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
updateReq.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result = updateReq.Execute();
return(result);
}
public async Task <bool> PutStat([FromUri] string gender, [FromUri] string terminalName)
{
try
{
UserCredential credential;
string[] Scopes = { SheetsService.Scope.Spreadsheets };
using (var stream =
new FileStream(HttpRuntime.AppDomainAppPath + "/client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = HttpRuntime.AppDomainAppPath;
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true));
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "neuronStats",
});
String spreadsheetId = "1mKVcyL0Xo8BHqVbcgTVfvzqM86V7jRUry3V3SKUf0wc";
String range = terminalName + "!" + ((gender == "male")?"B2":"C2");
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
var result = request.Execute();
int setInt = 0;
if (result.Values.Count != 0)
{
setInt = Int32.Parse(result.Values[0][0].ToString()) + 1;
}
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
setInt
};
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
return(true);
}
}
catch (Exception e)
{
return(false);
}
}
static void UpdateData()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new FileStream(@"D:\_Working\_3PSolution\myproject\research\google_sheets_v4\client_id.json", FileMode.Open, FileAccess.Read))
{
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,
});
if (credential != null)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
// The spreadsheet to request.
string spreadsheetId = "1Ln6hwAS31EII2XdlWN9PN0kaNEt4S3RIZewf81w-ePQ"; // TODO: Update placeholder value.
String range2 = "Sheet1!F5"; // update cell F5
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS"; //"ROWS";//COLUMNS
var oblist = new List <object>()
{
"1", 2
};
valueRange.Values = new List <IList <object> > {
oblist, oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
Console.ReadLine();
}
protected static bool WriteSingleCellToSheet(string strDataToSave, string strCellToSaveData)
{
try
{
Thread.Sleep(1000); // Sleep for a second so we don't go over the Google allotted requests.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
// TODO: Assign values to desired properties of `requestBody`. All existing
// properties will be replaced:
ValueRange requestBody = new ValueRange
{
MajorDimension = "COLUMNS" // "ROWS" / "COLUMNS"
};
var oblist = new List <object>()
{
strDataToSave
};
requestBody.Values = new List <IList <object> > {
oblist
};
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
SCOPES,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, SPREADSHEET_ID, strCellToSaveData);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
UpdateValuesResponse response = request.Execute();
// Data.UpdateValuesResponse response = await request.ExecuteAsync();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
} // End WriteSingleCellToSheet
private static void UpdatGoogleSheetinBatch(IList <IList <Object> > values, string spreadsheetId, string newRange, SheetsService service)
{
ValueRange valueRange = new ValueRange();
valueRange.Values = values;
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, newRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
public void UpdateSheet(IList <IList <Object> > data, string SheetName)
{
try
{
Monitor.Enter(gsLock);
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
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,
});
String spreadsheetId2 = "16HWTcJg5mFDbFch2HIYl_OI5xpe9FxzFeNrFQv-k7cU";
String range2 = SheetName + "!A2"; // update cell F5
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS"; //"ROWS";//COLUMNS
//var oblist = new List<object>() { "1" };
//oblist.Add("2");
valueRange.Values = data;//new List<IList<object>> { oblist };
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
Console.WriteLine("done!");
}
catch
{
}
finally
{
Monitor.Exit(gsLock);
}
}
private void UpdateSpreadsheet(string range, List <List <object> > data)
{
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";
valueRange.Values = new List <IList <object> >(data);
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result = update.Execute();
}
public void updateData()
{
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 update.
string spreadsheetId = "YOUR SPREADSHEET ID"; // TODO: Update placeholder value.
// The A1 notation of the values to update.
string range = "YOUR SPREADSHEET RANGE"; // TODO: Update placeholder value.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.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.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
UpdateValuesResponse response = request.Execute();
}
string UpdateRow(SheetsService service, string textBox)
{
ValueRange rVR;
String sRange;
int rowNumber = 1;
String rowNumberString;
sRange = String.Format("{0}!A:A", sheetName);
SpreadsheetsResource.ValuesResource.GetRequest getRequest
= service.Spreadsheets.Values.Get(spreadsheetId, sRange);
rVR = getRequest.Execute();
IList <IList <Object> > values = rVR.Values;
if (values != null && values.Count > 0)
{
rowNumber = values.Count + 1;
}
sRange = String.Format("{0}!A{1}:B{1}", sheetName, rowNumber);
ValueRange valueRange = new ValueRange();
valueRange.Range = sRange;
valueRange.MajorDimension = "ROWS";
DateTime dt = new DateTime();
dt = DateTime.Now;
List <object> oblist = new List <object>()
{
textBox, String.Format("{0}", rowNumber)
};
valueRange.Values = new List <IList <object> > {
oblist
};
//Console.WriteLine("{0}, {1}", oblist[0], oblist[1]);
rowNumber = rowNumber - 1;
rowNumberString = "A" + rowNumber.ToString();
if (oblist[0].ToString() != GetPreDate(service, rowNumberString))
{
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest
= service.Spreadsheets.Values.Update(valueRange, spreadsheetId, sRange);
updateRequest.ValueInputOption
= SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse uUVR = updateRequest.Execute();
}
else
{
MessageBox.Show("Показания уже вносились сегодня", "Ахтунг!", MessageBoxButtons.OK);
}
return(sRange);
}
public async Task MakeRequest(string sectionToEdit, List <object> obj)
{
ValueRange v = new ValueRange();
v.MajorDimension = "ROWS";
v.Values = new List <IList <object> > {
obj
};
SpreadsheetsResource.ValuesResource.UpdateRequest u = service.Spreadsheets.Values.Update(v, SpreadsheetID, sectionToEdit);//:O{idx+1}");
u.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse res = u.Execute();
}
private void returnBtn_Click(object sender, EventArgs e)
{
var service = CreateGoogleService();
string range;
// Define request parameters.
switch (currentPage)
{
case Pages.SignIn:
range = "Winter 2018!N" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString();
break;
case Pages.LabEquipment:
range = "Winter 2018!F" + (rowStart + actualRowIndex + listView1.SelectedItems[0].Index).ToString();
break;
default:
range = null;
break;
}
var valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
DateTime.Now.ToString("t")
};
listView1.SelectedItems[0].SubItems[5].Text = DateTime.Now.ToString("t");
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
returnBtn.Enabled = false;
returnBtn.Visible = false;
deleteBtn.Enabled = false;
deleteBtn.Visible = false;
copyBtn.Enabled = false;
copyBtn.Enabled = false;
}
static void Main(string[] args)
{
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
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,
});
String spreadsheetId2 = "<my spreadsheet ID>";
String range2 = "<my page name>!F5"; // update cell F5
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS"; //"ROWS";//COLUMNS
var oblist = new List <object>()
{
"My Cell Text"
};
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
Console.WriteLine("done!");
}
public async Task Alt(string name, string ilvl)
{
range = "H" + Utils.GetRowByDiscord(Program.context.User.ToString());
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added a main character with the !add *<Player Name>* command"); return;
}
if (Utils.GetRowByAlt(1, name) != 0 || Utils.GetRowByAlt(2, name) != 0)
{
await ReplyAsync("You've already added this alt."); return;
}
if (ilvl.All(char.IsDigit) == false || ilvl.Length > 3)
{
await ReplyAsync("Invalid item level."); return;
}
if (Utils.CellEmpty("H" + Utils.GetRowByDiscord(Program.context.User.ToString())) == false && Utils.CellEmpty("J" + Utils.GetRowByDiscord(Program.context.User.ToString())) == false)
{
await ReplyAsync("You can only have up to two alt's, if you need an alt removed contact an Officer."); return;
}
if (Utils.GetRowByAlt(1, name) == 0)
{
if (Utils.CellEmpty(range) == true)
{
range = "H" + Utils.GetRowByDiscord(Program.context.User.ToString());
}
else
{
if (Utils.GetRowByAlt(2, name) == 0)
{
range = "J" + Utils.GetRowByDiscord(Program.context.User.ToString());
}
}
}
IList <IList <object> > data = new List <IList <object> >()
{
new List <object> {
Utils.UpperCaseIt(name.ToLower()), ilvl
}
};
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
requestbody.Values = data;
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result = update.Execute();
await ReplyAsync("Successfully added **" + Utils.UpperCaseIt(name.ToLower()) + "** as an alt.");
}
private static void Execute(SpreadsheetsResource.ValuesResource.UpdateRequest request)
{
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
try
{
UpdateValuesResponse response = request.Execute();
if (response != null)
{
}
}
catch (Exception ex)
{
string msg = ex.Message;
System.Diagnostics.Debugger.Break();
}
}
public void Write(Cell beginCell, IList <IList <object> > cells)
{
ValueRange valueRange = new ValueRange()
{
Values = cells
};
SpreadsheetsResource.ValuesResource.UpdateRequest request =
this.service.Spreadsheets.Values.Update(
valueRange,
this.sheetId,
$"{this.sheetPage}!{beginCell}");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result = request.Execute();
}
