public static string PutCell(string sheetId, int row, string col, string value)
{
// create value range
ValueRange inputValue = new ValueRange();
inputValue.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
value
};
inputValue.Values = new List <IList <object> > {
oblist
};
// select the cell
string range = $"{col}{row}";
// create the request with the sheet ID
var request = GetService().Spreadsheets.Values.Append(inputValue, sheetId, range);
request.ValueInputOption = ValueInputOptionEnum.USERENTERED;
// get the data
AppendValuesResponse response = request.Execute();
// print the data
if (response != null && response.Updates.UpdatedCells > 0)
{
// should just be one sheet
return(response.Updates.UpdatedRange);
}
// return
return(null);
}
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 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 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}");
}
}
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();
}
}
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);
}
}
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();
}
public void WriteProfile(List <FollowData> rows)
{
if (rows == null)
{
return;
}
if (rows.Any() == false)
{
return;
}
var data = rows.Select(r => new { r.EmailAddress, r.TwitterUsername, r.NumOfFollowers }).ToList();
var sheetService = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
{
HttpClientInitializer = _GetCredential(),
ApplicationName = _appName
});
var range = "A2:B";
var valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var insertOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange body = new ValueRange();
body.MajorDimension = "ROWS";
body.Range = range;
body.Values = new List <IList <object> > {
data.ToList <object>()
};
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetService.Spreadsheets.Values.Append(body, _spreadSheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertOption;
AppendValuesResponse response = request.Execute();
}
private static AppendValuesResponse appendSpreadsheet(string docId,string sheetName,IList <object> values)
{
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";
valueRange.Values = new List <IList <object> > {
values
};
SheetsService sheetsSvc = new SheetsService();
SpreadsheetsResource.ValuesResource.AppendRequest request =
sheetsSvc.Spreadsheets.Values.Append(valueRange,docId,sheetName);
request.OauthToken = TokenManager.getAccessToken();
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse response = request.Execute();
return(response);
}
public static void CreateNewPostcodeEntry(string InPostcode, string InInformation, string InCellRange)
{
ValueRange RequestBody = new ValueRange();
RequestBody.MajorDimension = "ROWS";
var oblist = new List <object>()
{
InPostcode, InInformation
};
RequestBody.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest Request = SheetsService.Spreadsheets.Values.Append(RequestBody, SheetId, InCellRange);
Request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
AppendValuesResponse Response = Request.Execute();
}
public async Task <AppendValuesResponse> LogIdeaAsync(Initiative idea, ClaimsPrincipal owner, UserPrincipal adUser)
{
var values = new ValueRange()
{
MajorDimension = "ROWS"
};
IList <object> rowData = new List <object>()
{
idea.Id,
idea.Title,
idea.Description,
string.Empty,//idea.Url,
$"{_ideasApiBaseUrl}/{idea.Id}",
owner.GetDisplayName(),
owner.GetEmail(),
adUser?.SamAccountName,
idea.CreatedDate
};
values.Values = new List <IList <object> > {
rowData
};
var range = "Initiatives!A1:F1";
var request = SheetsService.Spreadsheets.Values.Append(values, _spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
try
{
AppendValuesResponse response = await request.ExecuteAsync();
return(response);
}
catch (Exception err)
{
System.Diagnostics.Trace.TraceError($"Unable to append values to Google sheet: { err.Message}");
throw;
}
}
private async Task AppendMessageAsync(SheetsService service, string spreadsheetId, Message message)
{
string range = await GetRange(service, spreadsheetId);
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(new ValueRange
{
Values = new List <IList <object> >()
{
new List <object>
{
message.AddedBy,
message.DateTime,
message.Comment
}.Union(message.Tags).ToList()
}
}, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse response = await request.ExecuteAsync();
}
private async Task LogToTable(string listname, string message, string user = null, MessageType?messageType = null)
{
var service = await _sheetServiceProvider.GetService();
var range = $"{listname}!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 oblist = new List <object>();
if (messageType != null)
{
oblist.Add(
messageType == MessageType.Outgoing ? "Исходящее" :
messageType == MessageType.Incoming ? "Входящее"
: "Системное"
);
}
oblist.AddRange(new List <object>()
{
string.Format(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")), message, user
});
valueRange.Values = new List <IList <object> > {
oblist
};
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 = await request.ExecuteAsync();
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string email = req.Query["email"].ToString().ToLower();
string answer = req.Query["answer"].ToString().ToLower() == "yes" ? "Yes" : "No";
//some of the following code provided by Google APIs https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
//Append our email and answer to the request body of values to add
ValueRange requestBody = new ValueRange();
var values = new[] { new { email, answer } };
requestBody.Values.Add(values);
//Create our request and set up the input options
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, sheetId, range);
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
//Obtain our response
AppendValuesResponse response = await request.ExecuteAsync();
//If everything is good we should have a valid response
return(response != null
? (ActionResult) new OkObjectResult(response)
: new BadRequestObjectResult("Something went wrong."));
}
public async Task RequestAsync([Summary("The item to request")] string item, [Summary("The amount to request")] int qty = 1, [Summary("The user to request the item for"), RequiredRoleParameter(388954821551456256)] IUser user = null)
{
IUser userInfo = user ?? Context.Message.Author;
string _name = await Program.GetIGNFromUser(userInfo);
// find the actual item name from the store
string _item = "";
ValueRange itemResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPStoreTab);
if (itemResult.Values != null && itemResult.Values.Count > 0)
{
int idx = GoogleSheetsHelper.Instance.IndexInRange(itemResult, item);
if (idx >= 0)
{
_item = itemResult.Values[idx][0].ToString();
}
}
if (string.IsNullOrEmpty(_item))
{
await ReplyAsync($"Unable to find {item} in the DKP store! Use !store to list available items.");
return;
}
// find any existing requests from this user for this item
int reqIdx = -1;
ValueRange reqResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPRequestsTab);
if (reqResult.Values != null && reqResult.Values.Count > 0)
{
reqIdx = GoogleSheetsHelper.Instance.SubIndexInRange(reqResult, _name, 1, _item);
}
int?result = -1;
// remove items from request
if (qty < 0)
{
if (reqIdx < 0)
{
await ReplyAsync($"{_name} is no longer requesting {_item} (and wasn't in the first place)");
return;
}
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out int currentQty);
currentQty += qty;
// if removing items requests in no items remaining, delete this request row
if (currentQty <= 0)
{
GridRange gridRange = Program.GetTabRange(Program.EDKPTabs.Requests);
BatchUpdateSpreadsheetResponse deleteResult = await GoogleSheetsHelper.Instance.DeleteRowsAsync(gridRange.SheetId ?? 0, reqIdx);
result = deleteResult.Replies.Count;
if (result < 0)
{
await ReplyAsync($"Error deleting {_name}'s request!");
}
else
{
await ReplyAsync($"{_name} is no longer requesting {_item}");
}
}
else // otherwise just deduct from the existing request
{
reqResult.Values[reqIdx][2] = currentQty.ToString();
UpdateValuesResponse writeResult = await GoogleSheetsHelper.Instance.UpdateAsync(reqResult.Range, reqResult);
result = writeResult.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error removing {_item} from request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {currentQty.ToString()} {_item}");
}
}
}
else // adding items to request
{
int currentQty = 0;
if (reqIdx > 0)
{
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out currentQty);
}
if (currentQty > 0) // updating request
{
currentQty += qty;
reqResult.Values[reqIdx][2] = currentQty.ToString();
UpdateValuesResponse writeResult = await GoogleSheetsHelper.Instance.UpdateAsync(reqResult.Range, reqResult);
result = writeResult.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error adding {_item} to request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {currentQty.ToString()} {_item}");
}
}
else // new request
{
IList <IList <object> > writeValues = new List <IList <object> > {
new List <object> {
_name, _item, qty.ToString()
}
};
ValueRange writeBody = new ValueRange
{
Values = writeValues
};
AppendValuesResponse writeResult = await GoogleSheetsHelper.Instance.AppendAsync(Config.Global.DKPRequestsTab, writeBody);
result = writeResult.Updates.UpdatedCells;
if (result < 0)
{
await ReplyAsync($"Error adding {_item} to request for {_name}");
}
else
{
await ReplyAsync($"{_name} is now requesting {qty.ToString()} {_item}");
}
}
}
}
public static void DB(string[] args)
{
// *******************************************************************
// Template code for initial setup
// *******************************************************************
// API code used to generate an access token for current user.
UserCredential credential;
// Using statement essentially releases FileStream object once it is no longer needed.
// This code opens the credential file this API just set up previously.
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
// Opens client-side browser to authenticate end-user with their google account.
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Your game data is now being uploaded to the cloud");
}
// Create Google Sheets API service.
// "var" can be used when storing a reference to an object of an anonymous type,
// which can't be known in advance. Normally, it just lets the compiler infer the data type.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName
});
// *******************************************************************
// Relevant code for updating database
// *******************************************************************
// These are the variables each API method needs to operate
String spreadsheetId = "1tFetfWWxEfTCuJHFySu6t8syozdTF0q4GEpbfaFQSgc";
String range = "Sheet1";
// Curly braces allow you to specify property parameters on initialization
// Object initializers like this let you set properties after construction, but before use.
// Apparently you can omit parentheses when creating a new object!
ValueRange valueRange = new ValueRange {
MajorDimension = "ROWS"
};
// This is the list of values which will be inserted into the table.
// The ValueRange object takes 2D lists, in which the inner list is the cell by cell data
// and the outer list is each row/column (depending on which MajorDimension is indicated).
// Object is used as a data type that can accept any values!
var oblist = new List <object>()
{
Environment.MachineName
};
// Adds final game choice values to object list
Program.Read(path, oblist);
// This particular list is the outer list. It contains all the lists of data from before, one per dimension.
valueRange.Values = new List <IList <object> > {
oblist
};
// Creates an UpdateRequest object with an ID, range, and content. Adjusts input option then executes the write command.
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
AppendValuesResponse response = request.Execute();
// *******************************************************************
}
public async Task IGNAsync([Summary("The name to set")] string name = "", [Summary("The user to set the name on"), RequiredRoleParameter(388954821551456256)] IUser user = null)
{
var userInfo = user ?? Context.Message.Author;
string _discordUser = userInfo.ToString();
string _name = "";
ValueRange readResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.Commands.IGN.NameMapSheet);
if (readResult.Values != null && readResult.Values.Count > 0)
{
int idx = GoogleSheetsHelper.Instance.IndexInRange(readResult, _discordUser);
if (idx >= 0)
{
_name = readResult.Values[idx][1].ToString();
readResult.Values[idx][1] = name;
}
}
// If no name is passed, the user wants to know their current name
if (string.IsNullOrEmpty(name))
{
// and if the bot doesn't know their current name, it assumes it's their discord username
if (string.IsNullOrEmpty(_name))
{
_name = userInfo.Username;
}
await ReplyAsync($"I think {userInfo.Username}'s IGN is {_name}.");
}
else
{
int?result = -1;
// if the bot doesn't know their current IGN, we're writing a new line
if (string.IsNullOrEmpty(_name))
{
IList <IList <object> > writeValues = new List <IList <object> > {
new List <object> {
_discordUser, name
}
};
ValueRange writeBody = new ValueRange
{
Values = writeValues
};
AppendValuesResponse writeResult = await GoogleSheetsHelper.Instance.AppendAsync(Config.Global.Commands.IGN.NameMapSheet, writeBody);
result = writeResult.Updates.UpdatedCells;
}
else // otherwise we're overwriting an existing line
{
UpdateValuesResponse writeResult = await GoogleSheetsHelper.Instance.UpdateAsync(readResult.Range, readResult);
result = writeResult.UpdatedCells;
}
if (result <= 0)
{
await ReplyAsync($"Error updating {userInfo.Username}'s IGN");
}
else
{
await Context.Message.AddReactionAsync(new Emoji(Config.Global.AcknowledgeEmoji));
}
}
}
static void Main(string[] args)
{
Console.WriteLine("\nIniciando ...");
/**
* Inicio de la configuración con la
* Hoja de Google
*/
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) // Cliente Json Descargado de Google
{
string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // de Donde jala el Json
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"*****@*****.**", // Cuenta de google con la que se accedera
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 = "1CAeC3cNHTLoevsqyrN5ryurz7m_iuTeMYCpLoYJAnQI"; //ID de Sheets, ultima parte de la URL
String range = "NuevasVersiones!A2"; //+celda; // Que Celda se Actualizara
ValueRange valueRange = new ValueRange();
var oblist = new List <object>();
/**
* Fin de la configuración con la
* Hoja de Google
*/
String p = ConfigurationManager.AppSettings["rutaVersionNueva"];
String ruta = ConfigurationManager.AppSettings["ruta"];
String[] nuevos = Directory.GetFiles(p, "*");
/*
* Obtiene los documentos que corresponden a las nuevas versiones.
*/
String path;
/*
* Esta variable se utilizara para
* buscar el path del archivo con la version
* vieja.
*/
String nameFile;
String[] s;
String rename;
foreach (String archivo in nuevos)
{
oblist = new List <object>();
/*
* Se creara un nuevo objeto de tipo
* oblist por cada archivo que se tenga.
*/
path = "";
path = ruta;
nameFile = Path.GetFileName(archivo);
s = Directory.GetFiles(path, nameFile, SearchOption.AllDirectories);
if (s.Length != 0)
{
/**
* Si entra al if significa que si encontro al
* archivo con la version vieja.
*/
if (s.Length > 1)
{
log.Info("\nEl archivo: " + archivo + " se duplica en : ");
oblist.Add((object)Path.GetFileName(archivo));
foreach (String i in s)
{
oblist.Add((object)i);
log.Info(i);
}
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest update = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse result = update.Execute();
}
else
{
rename = Path.GetDirectoryName(s[0]) + "\\" + Path.GetFileNameWithoutExtension(s[0]) + "__" + Path.GetExtension(s[0]);
File.Move(archivo, rename);
Console.WriteLine(archivo);
Console.WriteLine(rename);
}
}
else
{
/*
* Significa que el nombre del archivo con la nueva
* versión no coincide con el nombre del archivo de la version
* vieja o que el archivo no existe.
*/
log.Info("El archivo: " + nameFile + " no se encuentra ");
oblist.Add((object)nameFile);
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest update = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse result = update.Execute();
}
}
Console.ReadKey();
}
//static volatile bool exit = 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);
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:\r\n" + credPath + "\r\n");
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Prints the data from a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So/edit#gid=0
// Define request parameters.
String spreadsheetId = "1c54Cy_B43h5-nmE7r6Slvj2w8Pl0XFxgaWpTxO9s9So";
// here is the actual data to be sent to sheet
List <object> headerList = new List <object>()
{
"ID", "DTStamp", "DTShiftStart", "ModelNbr", "SerialNbr", "PassFail", "LineNbr", "ShiftNbr", "Computer", "Word40", "Word41", "Word42"
, "Word43", "Word44", "Word45", "Word46", "Word47", "Word48", "Word49", "Word50", "Word51", "Word52", "Word53", "Word54", "Word55", "Word56"
, "Word57", "Word58", "Word59", "Word60", "Word61", "Word62", "Word63", "Word64", "Word65", "Word66", "Word67", "Word68", "Word69", "Word70"
, "Word71", "Word72", "Word73", "Word74", "Word75", "Word76", "Word77", "Word78", "Word79", "Word80"
};
//var dataList = new List<object>();
//Write some data
String writeRange = "WriteData!A1:ZZ";
ValueRange valueRange = new ValueRange {
MajorDimension = "ROWS"
};
Console.WriteLine("Clear the Sheet");
//API method to clear the sheet
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest cr = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, writeRange);
ClearValuesResponse clearResponse = cr.Execute();
Console.WriteLine("Delete all rows in Sheet");
//API method to batch update
DimensionRange dr = new DimensionRange
{
Dimension = "ROWS",
StartIndex = 1,
SheetId = 1809337217
};
DeleteDimensionRequest ddr = new DeleteDimensionRequest()
{
Range = dr
};
Request r = new Request {
DeleteDimension = ddr
};
// { "requests": [{ "deleteDimension": { "range": { "sheetId": 1809337217, "startIndex": 1}} } ]};
List <Request> batchRequests = new List <Request>()
{
r
};
BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest()
{
Requests = batchRequests
};
SpreadsheetsResource.BatchUpdateRequest bRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
BatchUpdateSpreadsheetResponse busr = bRequest.Execute();
Console.WriteLine("Write the Headers to the Sheet");
//API method to update the sheet
valueRange.Values = new List <IList <object> > {
headerList
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result;
result = update.Execute();
SqlConnection sqlConnection = new SqlConnection("Data Source=tul-mssql;Initial Catalog=Division;User ID=tqisadmin;Password=admin2k");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "SELECT TOP 1000 [ID],[DTStamp],[DTShiftStart],[ModelNbr],[SerialNbr],[PassFail],[LineNbr],[ShiftNbr],[Computer],[Word40],[Word41],[Word42]" +
",[Word43],[Word44],[Word45],[Word46],[Word47],[Word48],[Word49],[Word50],[Word51],[Word52],[Word53],[Word54],[Word55],[Word56]" +
",[Word57],[Word58],[Word59],[Word60],[Word61],[Word62],[Word63],[Word64],[Word65],[Word66],[Word67],[Word68],[Word69],[Word70]" +
",[Word71],[Word72],[Word73],[Word74],[Word75],[Word76],[Word77],[Word78],[Word79],[Word80] " +
"FROM[Division].[dbo].[asyTestRecords] where LineNbr = 2 and computer = 'LN' and dtstamp > '2/1/2018 5:00' order by dtstamp desc";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
Console.WriteLine("Open the SQL connection");
sqlConnection.Open();
cmd.CommandTimeout = 60;
Console.WriteLine("Please wait while reading data from SQL");
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
var dataList = new List <object>();
valueDataRange.Values = new List <IList <object> > {
dataList
};
//API to append data to sheet
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
if (reader.HasRows)
{
//Console.WriteLine("{0}",reader.FieldCount);
Object[] colValues = new Object[reader.FieldCount];
int throttleCount = 0;
int cnt = 0;
while (reader.Read())
{
//This logic is flawed. If we get hit by the quota then the data row gets lost the next time this runs.
dataList.Clear();
for (int i = 0; i < reader.GetValues(colValues); i++)
{
dataList.Add(colValues[i]);
}
try
{
//This is the GOOGLE query Throttle they only allow 500 writes per 100 sec
System.Threading.Thread.Sleep(20);
AppendValuesResponse appendValueResponse = appendRequest.Execute();
Console.WriteLine("Writing to Sheet: row{0}", cnt);
}
catch (Exception e)
{
Console.WriteLine("Whoa buddy slowdown {0}", throttleCount);
System.Threading.Thread.Sleep(3000);
throttleCount++;
}
cnt++;
}
}
else
{
Console.WriteLine("No rows found.");
}
// sit here and wait for a while
Console.WriteLine("Done waiting to close reader and SQL");
System.Threading.Thread.Sleep(3000);
reader.Close();
sqlConnection.Close();
}
public string EditData(List <Messages> msgList)
{
log.Info("Editing sheet data.");
string resp = "";
// Column Names
IList <Object> header = new List <Object>();
header.Add("Timestamp");
header.Add("Author");
header.Add("Message");
IList <IList <Object> > headerValues = new List <IList <Object> >();
headerValues.Add(header);
string range = _sheetname + "!A1:Y";
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange requestBody = new ValueRange();
requestBody.Range = range;
requestBody.Values = headerValues;
SpreadsheetsResource.ValuesResource.AppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, _spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
AppendValuesResponse response = request.Execute();
// Data.AppendValuesResponse response = await request.ExecuteAsync();
resp = JsonConvert.SerializeObject(response);
foreach (Messages m in msgList)
{
range = _sheetname + "!A2:Y";
IList <Object> obj = new List <Object>();
obj.Add(m.Timestamp);
obj.Add(m.Author);
obj.Add(m.MessageContent);
IList <IList <Object> > values = new List <IList <Object> >();
values.Add(obj);
valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
requestBody = new ValueRange();
requestBody.Range = range;
requestBody.Values = values;
request = _sheetsService.Spreadsheets.Values.Append(requestBody, _spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
response = request.Execute();
// Data.AppendValuesResponse response = await request.ExecuteAsync();
resp = JsonConvert.SerializeObject(response);
}
return(resp);
}
public async Task StopAsync()
{
if (!Program.AttendanceEvent.active)
{
await ReplyAsync("Unable to stop event, no event is running!");
return;
}
string _attendanceName = Program.AttendanceEvent.type + Config.Global.Commands.Attendance.AttendanceSuffix;
string _excusedName = Program.AttendanceEvent.type + Config.Global.Commands.Attendance.ExcusedSuffix;
int attendanceWeight = 0;
int excusedWeight = 0;
ValueRange readResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPReasonsTab);
if (readResult.Values != null && readResult.Values.Count > 0)
{
int idx = GoogleSheetsHelper.Instance.IndexInRange(readResult, _attendanceName);
if (idx >= 0)
{
int.TryParse(readResult.Values[idx][1].ToString(), out attendanceWeight);
}
idx = GoogleSheetsHelper.Instance.IndexInRange(readResult, _excusedName);
if (idx >= 0)
{
int.TryParse(readResult.Values[idx][1].ToString(), out excusedWeight);
}
}
string timestamp = DateTime.Now.ToString();
IList <IList <object> > writeValues = new List <IList <object> >();
foreach (string member in Program.AttendanceEvent.members)
{
writeValues.Add(new List <object> {
member, attendanceWeight, timestamp, _attendanceName, Program.AttendanceEvent.note
});
}
foreach (string excused in Program.AttendanceEvent.excused)
{
writeValues.Add(new List <object> {
excused, excusedWeight, timestamp, _excusedName, Program.AttendanceEvent.note
});
}
ValueRange writeBody = new ValueRange
{
Values = writeValues
};
AppendValuesResponse writeResult = await GoogleSheetsHelper.Instance.AppendAsync(Config.Global.DKPLogTab, writeBody);
int?result = writeResult.Updates.UpdatedCells;
if (result <= 0)
{
await ReplyAsync($"Error writing DKP for event!");
}
string message = $"Ended event {Program.AttendanceEvent.type} {Program.AttendanceEvent.note} with the following {Program.AttendanceEvent.members.Count} members attending: ";
foreach (string member in Program.AttendanceEvent.members)
{
message += $"{member}, ";
}
if (Program.AttendanceEvent.excused.Count > 0)
{
message += $"and the following {Program.AttendanceEvent.excused.Count} member(s) excused: ";
}
foreach (string excused in Program.AttendanceEvent.excused)
{
message += $"{excused}, ";
}
message += "and that's all, folks!";
IUserMessage pin = await Context.Channel.GetMessageAsync(Program.AttendanceEvent.messageId) as IUserMessage;
if (pin != null)
{
await pin.UnpinAsync();
}
Program.AttendanceEvent = default(SAttendanceEvent);
await Context.Channel.SendMessageAsync(message);
}
