static void Main(string[] args)
{
Environment.CurrentDirectory = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.LastIndexOf("osum", StringComparison.Ordinal)) + @"\osum\osum\Localisation\";
ss.setUserCredentials("*****@*****.**", "osumisawsum");
SpreadsheetFeed feed = ss.Query(new SpreadsheetQuery());
SpreadsheetEntry sheet = null;
//get spreadsheet
foreach (var entry in feed.Entries.OfType <SpreadsheetEntry>())
{
if (entry.Title.Text == "osu!stream localisation")
{
sheet = entry;
break;
}
}
if (sheet == null)
{
Console.WriteLine("failed to get spreadsheet");
Console.ReadLine();
}
//get worksheet
AtomLink link = sheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = ss.Query(query);
WorksheetEntry worksheet = wfeed.Entries[0] as WorksheetEntry;
ProcessWorksheet(worksheet);
}
public AtomEntryCollection ObtenerListaHojas(string linkLibro, SpreadsheetsService servicio)
{
var consulta = new WorksheetQuery(linkLibro);
var hojas = servicio.Query(consulta);
return(hojas.Entries);
}
private static WorksheetEntry GetWorkSheetEntry(string sheetId)
{
WorksheetEntry wsEntry = null;
try
{
if (null == sheetsService)
{
sheetsService = GetUserCredential();
}
if (null != sheetsService)
{
WorksheetQuery worksheetquery = new WorksheetQuery("https://spreadsheets.google.com/feeds/worksheets/" + sheetId + "/private/full");
WorksheetFeed wsFeed = sheetsService.Query(worksheetquery);
if (wsFeed.Entries.Count > 0)
{
wsEntry = (WorksheetEntry)wsFeed.Entries[0];
}
if (null != wsEntry)
{
//create headers if missing
bool updatedHeaders = UpdateHeaders(wsEntry);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to get Google Worksheet.\n" + ex.Message, "Get Work Sheet Entry", MessageBoxButton.OK, MessageBoxImage.Warning);
}
return(wsEntry);
}
//Fetch all worksheets for given Spreadsheet
public static ArrayList getWorksheetList(string userName, string passWord, string spreadSheetName)
{
ArrayList worksheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService(spreadSheetName + "Service");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == spreadSheetName)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = service.Query(wquery);
foreach (WorksheetEntry worksheet in wfeed.Entries)
{
worksheetList.Add(worksheet.Title.Text);
}
}
}
return(worksheetList);
}
private static WorksheetEntry GetWorksheet()
{
AtomLink link = sheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = ss.Query(query);
return(wfeed.Entries[0] as WorksheetEntry);
}
public static AtomEntryCollection GetSheetNames(SpreadsheetsService zSpreadsheetService, AtomEntry zSheetEntry)
{
var link = zSheetEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString());
var wsfeed = zSpreadsheetService.Query(wsquery);
return(wsfeed.Entries);
}
/// <summary>
/// Handler for when the Worksheets page becomes active. Depends on
/// the spreadsheet being selected on the Spreadsheets page.
/// </summary>
/// <param name="sender">Ignored</param>
/// <param name="e">Ignored</param>
void OnEnterSelectWorksheetTab(object sender, System.EventArgs e)
{
if (spreadsheetListView.SelectedItems.Count > 0)
{
// Get the worksheet feed from the selected entry
WorksheetQuery query = new WorksheetQuery(spreadsheetListView.SelectedItems[0].SubItems[1].Text);
SetWorksheetListView(service.Query(query));
}
}
public List <Worksheet> ListWorksheets()
{
AtomLink link = _entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = _service.Query(query);
return(CollectionUtils.Map <WorksheetEntry, Worksheet, List <Worksheet> >(
feed.Entries,
delegate(WorksheetEntry entry) { return new Worksheet(_service, entry); }));
}
public List <TDataRow> ReadAll <TDataRow>(String documentName, String sheetTitle) where TDataRow : GdataRow, new()
{
SpreadsheetQuery sheetQuery = new SpreadsheetQuery();
SpreadsheetFeed sheetFeed = AuthorisedSpreadsheetsService.Query(sheetQuery);
AtomEntry affiliates = (from x in sheetFeed.Entries where x.Title.Text.Contains(documentName) select x).First();
// Get the first Worksheet...
AtomLink sheetLink = affiliates.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery workSheetQuery = new WorksheetQuery(sheetLink.HRef.ToString());
WorksheetFeed workSheetFeed = AuthorisedSpreadsheetsService.Query(workSheetQuery);
AtomEntry affiliateSheet = workSheetFeed.Entries.First(s => s.Title.Text.Equals(sheetTitle, StringComparison.InvariantCultureIgnoreCase));
// Get the cells...
UInt32 startRow = 2;
AtomLink cellLink = affiliateSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellQuery = new CellQuery(cellLink.HRef.ToString())
{
MinimumRow = startRow,
};
CellFeed cellFeed = AuthorisedSpreadsheetsService.Query(cellQuery);
List <TDataRow> table = new List <TDataRow>();
TDataRow row = null;
foreach (CellEntry currentCell in cellFeed.Entries)
{
if (currentCell.Column == 1)
{
if (currentCell.Value.Equals("-end-", StringComparison.InvariantCultureIgnoreCase))
{
break;
}
row = new TDataRow {
RowNbr = currentCell.Row, Service = AuthorisedSpreadsheetsService, CellLink = cellLink
};
table.Add(row);
}
if (currentCell.Column > row.LastColumn)
{
continue;
}
row.SetValue(currentCell);
}
return(table);
}
private WorksheetEntry GetWorksheet(SpreadsheetEntry pSpreadsheetEntry)
{
AtomLink link = tDicingSpreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
string tLink = link.HRef.ToString();
WorksheetQuery tWorksheetQuery = new WorksheetQuery(tLink);
tWorksheetQuery.Title = tSheetName;
WorksheetFeed wfeed = tService.Query(tWorksheetQuery);
WorksheetEntry wentry = wfeed.Entries.FirstOrDefault() as WorksheetEntry;
return(wentry);
}
public void SelectWorkSheet(string sSheetName)
{
WorksheetQuery worksheetQuery = new WorksheetQuery(m_oLink.HRef.ToString());
worksheetQuery.Title = sSheetName;
m_oWorksheetFeed = m_oAccount.Service.Query(worksheetQuery);
m_oWorksheet = (WorksheetEntry)m_oWorksheetFeed.Entries[0];
AtomLink listFeedLink = m_oWorksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
m_oListFeed = m_oAccount.Service.Query(listQuery);
}
private IEnumerable <WorksheetEntry> GetWorksheetEntrees(SpreadsheetsService service = null)
{
if (service == null)
{
service = new SpreadsheetsService(applicationName);
}
service.setUserCredentials(userName, password);
var worksheetQuery = new WorksheetQuery(spreadsheetKey, "private", "full");
var wsFeed = service.Query(worksheetQuery);
return(wsFeed.Entries.OfType <WorksheetEntry>());
}
private List <WorksheetEntry> GetAllWorksheets(SpreadsheetEntry entry)
{
AtomLink listLink = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, AtomLink.ATOM_TYPE);
WorksheetQuery query = new WorksheetQuery(listLink.HRef.ToString());
WorksheetFeed feed = spreadsheetService.Query(query);
var list = new List <WorksheetEntry>();
foreach (WorksheetEntry worksheet in feed.Entries)
{
list.Add(worksheet);
}
return(list);
}
private void FindValidAuctionTypes()
{
validTypes = new List <string>();
WorksheetQuery wsQuery = new WorksheetQuery(String.Format(@"http://spreadsheets.google.com/feeds/worksheets/{0}/private/full", NotificationAdministrationSheetKey));
AtomEntryCollection worksheetEntries = MySpreadsheetService.Query(wsQuery).Entries;
foreach (WorksheetEntry sheet in worksheetEntries)
{
if (!"Admin,Template".Contains(sheet.Title.Text))
{
validTypes.Add(sheet.Title.Text);
}
}
}
private void GetLocalizationList()
{
if (string.IsNullOrEmpty(settings.spreadSheetKey))
{
Debug.LogError("spreadSheetKey can not be null!");
return;
}
PlayerPrefs.SetString(PREF_SHEET_KEY, settings.spreadSheetKey);
PlayerPrefs.Save();
if (IsTokkenEmpty)
{
EditorUtility.ClearProgressBar();
GetAccessCode();
return;
}
progressMessage = "Authenticating...";
GOAuth2RequestFactory requestFactory = RefreshAuthenticate();
SpreadsheetsService service = new SpreadsheetsService(appName);
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
query.Uri = new System.Uri(urlRoot + settings.spreadSheetKey);
progressMessage = "Get list of spreadsheets...";
spreadsheetFeed = service.Query(query);
if ((spreadsheetFeed == null) || (spreadsheetFeed.Entries.Count <= 0))
{
Debug.LogError("Not found any data!");
EditorUtility.ClearProgressBar();
return;
}
AtomEntry mySpreadSheet = spreadsheetFeed.Entries[0];
AtomLink link = mySpreadSheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery sheetsQuery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed sheetsFeed = service.Query(sheetsQuery);
foreach (WorksheetEntry sheet in sheetsFeed.Entries)
{
wantedSheetNames.Add(sheet.Title.Text);
}
localizationCount = wantedSheetNames.Count;
}
/// <summary>
/// Prints a list of all worksheets in the specified spreadsheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the spreadsheet whose worksheets are to be retrieved</param>
private static void PrintAllWorksheets(SpreadsheetsService service,
SpreadsheetEntry entry)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);
Console.WriteLine("Worksheets in " + entry.Title.Text + ":");
foreach (WorksheetEntry worksheet in feed.Entries)
{
allWorksheets.Add(worksheet);
Console.WriteLine(" " + worksheet.Title.Text);
}
Console.WriteLine();
}
private void getAllWorksheets()
{
AtomLink link = m_spreadsheetEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = m_service.Query(query);
Trace.WriteLine("Found " + feed.Entries.Count + " worksheets.");
Trace.Indent();
foreach (WorksheetEntry worksheet in feed.Entries)
{
Trace.WriteLine(worksheet.Title.Text);
m_worksheets.Add(worksheet.Title.Text, worksheet);
}
Trace.Unindent();
}
public static void SpreadSheetRead()
{
//Authenticate:
SpreadsheetsService myService = new SpreadsheetsService(null);
myService.setUserCredentials("yokolnov.logstream", "klamsi81");
//Get a list of spreadsheets:
SpreadsheetQuery query = new SpreadsheetQuery();
query.Uri = new Uri(@"https://docs.google.com/spreadsheets/d/19qX8B8skvYMRcmgMQhsek2ZHi1fdiyf1LC7wVm7D4L4/edit#gid=0");
SpreadsheetFeed feed = myService.Query(query);
Console.WriteLine("Your spreadsheets: ");
foreach (SpreadsheetEntry entry in feed.Entries)
{
Console.WriteLine(entry.Title.Text);
//Given a SpreadsheetEntry you've already retrieved, you can get a list of all worksheets in this spreadsheet as follows:
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wsquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wsfeed = myService.Query(wsquery);
foreach (WorksheetEntry worksheet in wsfeed.Entries)
{
Console.WriteLine(worksheet.Title.Text);
//And get a cell based feed:
AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cquery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cfeed = myService.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
foreach (CellEntry curCell in feed.Entries)
{
Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,
curCell.Cell.Column, curCell.Cell.Value);
}
}
}
}
public MTWorksheet GetWorksheet(string name)
{
if (!_worksheets.ContainsKey(name)) // query for worksheet
{
AtomLink link = sheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
query.Title = name;
WorksheetFeed feed = _service.Query(query);
if (feed.Entries.Count > 0)
{
_worksheets [name] = new MTWorksheet(feed.Entries[0] as WorksheetEntry, _service);
}
else
{
_worksheets [name] = null;
}
}
return(_worksheets [name]);
}
private void getAllWorksheets(Boolean debug_flag = false)
{
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);
if (debug_flag)
{
Console.WriteLine("Finding Worksheets. Found...");
}
foreach (WorksheetEntry worksheet in feed.Entries)
{
if (debug_flag)
{
Console.WriteLine(worksheet.Title.Text);
}
worksheets.Add(worksheet.Title.Text, worksheet);
}
}
private CellEntry[,] GetCellEntryArray(string workbookKey, string SheetName)
{
WorksheetQuery wsQuery = new WorksheetQuery(String.Format(@"http://spreadsheets.google.com/feeds/worksheets/{0}/private/full", workbookKey));
AtomEntryCollection worksheetEntries = MySpreadsheetService.Query(wsQuery).Entries;
CellEntry[,] ce = null;
AtomLink MySheetLink = worksheetEntries.First(x => x.Title.Text == SheetName).Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
CellQuery MySheetQuery = new CellQuery(MySheetLink.HRef.Content);
CellFeed MySheetFeed = MySpreadsheetService.Query(MySheetQuery);
AtomEntryCollection MySheetEntries = MySheetFeed.Entries;
ce = new CellEntry[MySheetFeed.RowCount.IntegerValue, MySheetFeed.ColCount.IntegerValue];
for (int i = 0; i < MySheetEntries.Count; i++)
{
CellEntry entry = MySheetEntries[i] as CellEntry;
//google doc is a 1 based array, convert to 0 based
ce[entry.Row - 1, entry.Column - 1] = entry;
}
return(ce);
}
private void btnConnectJenkins_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
username = txtUserName.Text;
password = txtPasswpord.Text;
btnConnectJenkins.Enabled = false;
dgvJenkins.AutoGenerateColumns = false;
progressBar2.Value = 0;
joblist = new List <string>();
JenkinServers = new Dictionary <string, List <string> >();
spreadsheetsService = new SpreadsheetsService("AlmJenkinsIntegration");
if (txtUserName.Text.Contains("ngahr"))
{
//oAuth2();
NGAMail();
}
else
{
oAuth2();
//spreadsheetsService.setUserCredentials(txtUserName.Text, txtPasswpord.Text);
}
//Passing Spreadsheet Name
SpreadsheetQuery spreadsheetQuery = new SpreadsheetQuery();
spreadsheetQuery.Title = txtSpreadSheetName.Text;
spreadsheetFeed = spreadsheetsService.Query(spreadsheetQuery);
if (spreadsheetFeed.Entries.Count != 1)
{
MessageBox.Show("Invallid Spreadsheet Name!");
btnConnectJenkins.Enabled = true;
return;
}
AtomLink link = spreadsheetFeed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery worksheetQuery = new WorksheetQuery(link.HRef.ToString());
//Passing Worksheet Name
worksheetQuery.Title = txtWorkSheetName.Text;
worksheetFeed = spreadsheetsService.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1)
{
MessageBox.Show("Invallid Worksheet Name!");
btnConnectJenkins.Enabled = true;
return;
}
UpdateListFeed();
// define the table's schema
dtJenkinsTestDetails = new DataTable();
dtJenkinsTestDetails.Columns.Add(new DataColumn("TestName", typeof(string)));
dtJenkinsTestDetails.Columns.Add(new DataColumn("Result", typeof(string)));
dtJenkinsTestDetails.Columns.Add(new DataColumn("JenkinsLog", typeof(string)));
dtJenkinsTestDetails.Columns.Add(new DataColumn("ALM", typeof(string)));
foreach (ListEntry worksheetRow in listFeed.Entries)
{
string serverName = worksheetRow.Elements[0].Value.ToString();
if (serverName != string.Empty && serverName.ToLower() != "server" && !JenkinServers.ContainsKey(serverName))
{
JenkinServers.Add(serverName, GetInnerJobs(serverName, listFeed.Entries));
}
DataRow dr = dtJenkinsTestDetails.NewRow();
dr["TestName"] = worksheetRow.Elements[3].Value.ToString();
dr["Result"] = worksheetRow.Elements[4].Value.ToString();
dr["JenkinsLog"] = worksheetRow.Elements[6].Value.ToString();
dr["ALM"] = worksheetRow.Elements[7].Value.ToString();
dtJenkinsTestDetails.Rows.Add(dr);
}
progressBar2.Maximum = JenkinServers.Count;
progressBar2.PerformStep();
BuildJenkinsTree();
dgvJenkins.DataSource = dtJenkinsTestDetails;
lblTestsCount.Text = dtJenkinsTestDetails.Rows.Count.ToString();
lblJobTitle.Text = string.Empty;
DgvFilterManager filterManager = new DgvFilterManager(dgvJenkins, true);
btnConnectJenkins.Enabled = true;
Cursor.Current = Cursors.Default;
}
private static void AddManualWorkbookByUrl(string in_manualUrl, Google2uData in_instance)
{
WorkbookBase info;
if (string.IsNullOrEmpty(in_manualUrl))
{
Debug.LogError(LocalizationInfo.Localize(Localization.rowIds.ID_ERROR_EMPTY_URL));
return;
}
var refreshManualWorkbookCache = false;
try
{
var key =
in_manualUrl.Substring(in_manualUrl.IndexOf("key=", StringComparison.InvariantCultureIgnoreCase) + 4);
key = key.Split('&')[0];
var singleQuery = new WorksheetQuery(key, "public", "values");
if (in_instance.ManualService == null && !SetupManualService(in_instance))
{
return;
}
var feed = in_instance.ManualService.Query(singleQuery);
var finalUrl = in_manualUrl.Split('&')[0];
if (feed != null)
{
info =
in_instance.ManualWorkbooks.Find(in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) ??
in_instance.AccountWorkbooks.Find(in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl))
as WorkbookBase;
if (info == null)
{
var newWorkbook = new Google2uManualWorkbook(feed, finalUrl, feed.Title.Text,
in_instance.ManualService);
in_instance.ManualWorkbooks.Add(newWorkbook);
refreshManualWorkbookCache = true;
}
}
}
catch
{
try
{
var key =
in_manualUrl.Substring(
in_manualUrl.IndexOf("spreadsheets/d/", StringComparison.InvariantCultureIgnoreCase) + 15);
key = key.Split('/')[0];
if (in_instance.ManualService == null && !SetupManualService(in_instance))
{
return;
}
var singleQuery = new WorksheetQuery(key, "public", "values");
var feed = in_instance.ManualService.Query(singleQuery);
var urlParts = in_manualUrl.Split('/');
var finalUrl = "";
var urlBuild = 0;
string urlPart;
do
{
urlPart = urlParts[urlBuild];
finalUrl += urlPart + '/';
urlBuild++;
} while (urlPart != key);
if (feed != null)
{
info =
in_instance.ManualWorkbooks.Find(
in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) ??
in_instance.AccountWorkbooks.Find(
in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) as WorkbookBase;
if (info == null)
{
var newWorkbook = new Google2uManualWorkbook(feed, finalUrl, feed.Title.Text,
in_instance.ManualService);
in_instance.ManualWorkbooks.Add(newWorkbook);
refreshManualWorkbookCache = true;
}
}
}
catch
{
Debug.LogError(LocalizationInfo.Localize(Localization.rowIds.ID_ERROR_INVALID_URL));
}
}
if (refreshManualWorkbookCache)
{
in_instance.ManualWorkbookCache = string.Empty;
foreach (var Google2uSpreadsheet in in_instance.ManualWorkbooks)
{
in_instance.ManualWorkbookCache += Google2uSpreadsheet.WorkbookUrl + "|";
}
}
}
private void DownloadToJson()
{
//Validate input
if (string.IsNullOrEmpty(spreadSheetKey))
{
Debug.LogError("spreadSheetKey can not be null!");
return;
}
PlayerPrefs.SetString(PREF_SHEET_KEY, spreadSheetKey);
PlayerPrefs.Save();
if (ACCESS_TOKEN == "" && REFRESH_TOKEN == "")
{
EditorUtility.ClearProgressBar();
GetAccessCode();
return;
}
//Authenticate
progressMessage = "Authenticating...";
GOAuth2RequestFactory requestFactory = RefreshAuthenticate();
SpreadsheetsService service = new SpreadsheetsService(appName);
service.RequestFactory = requestFactory;
progress = 5;
EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
//Get the list of spreadsheets by the key
SpreadsheetQuery query = new SpreadsheetQuery();
query.Uri = new System.Uri(urlRoot + spreadSheetKey);
progressMessage = "Get list of spreadsheets...";
EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
SpreadsheetFeed feed = service.Query(query);
if ((feed == null) || (feed.Entries.Count <= 0))
{
Debug.LogError("Not found any data!");
progress = 100;
EditorUtility.ClearProgressBar();
return;
}
progress = 10;
//Get the first spreadsheet in result
AtomEntry mySpreadSheet = feed.Entries[0];
//Get data from that spreadsheet
progressMessage = "Get entries from spreadsheets...";
EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
AtomLink link = mySpreadSheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery sheetsQuery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed sheetsFeed = service.Query(sheetsQuery);
progress = 15;
//For each sheet in received data, check the sheet name. If that sheet is the wanted sheet, create a json file
foreach (WorksheetEntry sheet in sheetsFeed.Entries)
{
if ((wantedSheetNames.Count <= 0) || (wantedSheetNames.Contains(sheet.Title.Text)))
{
progressMessage = string.Format("Processing {0}...", sheet.Title.Text);
EditorUtility.DisplayCancelableProgressBar("Processing", progressMessage, progress / 100);
//Get all cell data in sheet
AtomLink cellsLink = sheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellsQuery = new CellQuery(cellsLink.HRef.ToString());
CellFeed cellsFeed = service.Query(cellsQuery);
//Create json file
CreateJsonFile(sheet.Title.Text, outputDir, cellsFeed.Entries);
if (wantedSheetNames.Count <= 0)
{
progress += 85 / (sheetsFeed.Entries.Count);
}
else
{
progress += 85 / wantedSheetNames.Count;
}
}
}
progress = 100;
AssetDatabase.Refresh();
}
public override void Log(LoggingEntery LE)
{
IniFile Ini = new IniFile(Path.Combine(Environment.CurrentDirectory, "GoogleDocs.ini"));
// nir start
////////////////////////////////////////////////////////////////////////////
// STEP 1: Configure how to perform OAuth 2.0
////////////////////////////////////////////////////////////////////////////
// TODO: Update the following information with that obtained from
// https://code.google.com/apis/console. After registering
// your application, these will be provided for you.
//string CLIENT_ID = "339569043085-6k0io9kdubi7a3g3jes4m76t614fkccr.apps.googleusercontent.com";
// This is the OAuth 2.0 Client Secret retrieved
// above. Be sure to store this value securely. Leaking this
// value would enable others to act on behalf of your application!
//string CLIENT_SECRET = "wWC4Wcb12RbQg4YuGWJtkh4j";
// Space separated list of scopes for which to request access.
//string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";
// This is the Redirect URI for installed applications.
// If you are building a web application, you have to set your
// Redirect URI at https://code.google.com/apis/console.
//tring REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";
////////////////////////////////////////////////////////////////////////////
// STEP 2: Set up the OAuth 2.0 object
////////////////////////////////////////////////////////////////////////////
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
OAuth2Parameters parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = Ini.IniReadValue(ConnectSection, "ClientID");
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = Ini.IniReadValue(ConnectSection, "ClientSecret");
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = Ini.IniReadValue(ConnectSection, "RedirectURI");
// Set your refresh token
parameters.RefreshToken = Ini.IniReadValue(ConnectSection, "RefreshToken");
parameters.AccessToken = Ini.IniReadValue(ConnectSection, "LastAccessToken");
// Set the scope for this particular service.
parameters.Scope = Ini.IniReadValue(ConnectSection, "Scope");
// Get the authorization url. The user of your application must visit
// this url in order to authorize with Google. If you are building a
// browser-based application, you can redirect the user to the authorization
// url.
//string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
//Console.WriteLine(authorizationUrl);
//Console.WriteLine("Please visit the URL above to authorize your OAuth "
// + "request token. Once that is complete, type in your access code to "
// + "continue...");
////////////////////////////////////////////////////////////////////////////
// STEP 4: Get the Access Token
////////////////////////////////////////////////////////////////////////////
// Once the user authorizes with Google, the request token can be exchanged
// for a long-lived access token. If you are building a browser-based
// application, you should parse the incoming request token from the url and
// set it in OAuthParameters before calling GetAccessToken().
OAuthUtil.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection, "LastAccessToken", parameters.AccessToken);
// Console.WriteLine("OAuth Access Token: " + accessToken);
////////////////////////////////////////////////////////////////////////////
// STEP 5: Make an OAuth authorized request to Google
////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
SpreadsheetsService myService = new SpreadsheetsService("OctoTipPlus");
myService.RequestFactory = requestFactory;
// nir end
string User = Ini.IniReadValue("UserLogin", "User");
string Password = Ini.IniReadValue("UserLogin", "Password");
// SpreadsheetsService myService = new SpreadsheetsService("MySpreadsheetIntegration-v1");
//myService.setUserCredentials(User,Password);
SpreadsheetQuery Squery = new SpreadsheetQuery();
string Sender = LE.Sender;
Squery.Title = Sender;
Squery.Exact = true;
SpreadsheetFeed Sfeed;
try
{
Sfeed = myService.Query(Squery);
}
catch (Google.GData.Client.InvalidCredentialsException e)
{
throw(new Exception(string.Format("Credentials error in google acount for user:{0}", User), e));
}
if (Sfeed.Entries.Count == 0)
{
//DriveService service1 = new DriveService();
//service.SetAuthenticationToken(parameters.AccessToken);//.setUserCredentials(User,Password);
//Google.GData.Client.GOAuth2RequestFactory requestf = new Google.GData.Client.GOAuth2RequestFactory(null, "OctoTipPlus",parameters);
OAuthUtil.RefreshAccessToken(parameters); //parameters.AccessToken;
Ini.IniWriteValue(ConnectSection, "LastAccessToken", parameters.AccessToken);
Google.GData.Documents.DocumentsService service = new Google.GData.Documents.DocumentsService("OctoTipPlus");
GOAuth2RequestFactory requestFactory2 =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
service.RequestFactory = requestFactory2;
//service.RequestFactory=requestf;
// Instantiate a DocumentEntry object to be inserted.
Google.GData.Documents.DocumentEntry entry = new Google.GData.Documents.DocumentEntry();
// Set the document title
entry.Title.Text = LE.Sender;
// Add the document category
entry.Categories.Add(Google.GData.Documents.DocumentEntry.SPREADSHEET_CATEGORY);
// Make a request to the API and create the document.
Google.GData.Documents.DocumentEntry newEntry = service.Insert(
Google.GData.Documents.DocumentsListQuery.documentsBaseUri, entry);
Squery = new SpreadsheetQuery();
Squery.Title = Sender;
Squery.Exact = true;
Sfeed = myService.Query(Squery);
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)Sfeed.Entries[0];
WorksheetEntry ProtocolWorksheetEntry = null;
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery Wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed Wfeed = myService.Query(Wquery);
foreach (WorksheetEntry worksheet in Wfeed.Entries)
{
if (worksheet.Title.Text == LE.SubSender)
{
ProtocolWorksheetEntry = worksheet;
}
}
if (ProtocolWorksheetEntry == null)
{
// cteate new worksheet
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = LE.SubSender;
worksheet.Cols = 3;
worksheet.Rows = 5;
// Send the local representation of the worksheet to the API for
// creation. The URL to use here is the worksheet feed URL of our
// spreadsheet.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
ProtocolWorksheetEntry = myService.Insert(wsFeed, worksheet);
CellFeed cellFeed = ProtocolWorksheetEntry.QueryCellFeed();
CellEntry cellEntry = new CellEntry(1, 1, DateHeader);
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 2, MessageHeader);
cellFeed.Insert(cellEntry);
}
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = ProtocolWorksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = myService.Query(listQuery);
string Message = string.Format("{0}\n{1}", LE.Title, LE.Message);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom()
{
LocalName = DateHeader, Value = DateTime.Now.ToString()
});
row.Elements.Add(new ListEntry.Custom()
{
LocalName = MessageHeader, Value = Message
});
// Send the new row to the API for insertion.
myService.Insert(listFeed, row);
}
public static Task <WorksheetFeed> GetFeedAsync(this SpreadsheetsService service, WorksheetQuery query, IAggregateProgress progress = null, CancellationToken cancellationToken = default(CancellationToken))
{
return(GetFeedAsync <WorksheetFeed, WorksheetQuery>(service, query, progress, cancellationToken));
}
// TODO: all callers of this method should handle exceptions (InvalidCredentialsException etc.)
public static List <List <string> > GetSpreadsheet(SpreadsheetsService zSpreadsheetService, string sSpreadsheetName, string sSheetName)
{
var listLines = new List <List <string> >();
// get all spreadsheets
var query = new SpreadsheetQuery
{
// only ask for the spreadsheet by the given name
Title = sSpreadsheetName
};
var feed = zSpreadsheetService.Query(query);
var bFoundSpreadsheet = false;
foreach (var entry in feed.Entries)
{
if (entry.Title.Text != sSpreadsheetName)
{
continue;
}
bFoundSpreadsheet = true;
Logger.AddLogLine("Google: Found spreadsheet: " + sSpreadsheetName);
var link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString())
{
Title = sSheetName
};
var wsfeed = zSpreadsheetService.Query(wsquery);
var bFoundSheet = false;
foreach (var worksheet in wsfeed.Entries)
{
//System.Diagnostics.Trace.WriteLine(worksheet.Title.Text);
if (worksheet.Title.Text != sSheetName)
{
continue;
}
bFoundSheet = true;
Logger.AddLogLine("Google: Found sheet: " + sSheetName);
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cquery = new CellQuery(cellFeedLink.HRef.ToString());
var cfeed = zSpreadsheetService.Query(cquery);
//System.Diagnostics.Trace.WriteLine("Cells in this worksheet:");
uint uRow = 1;
uint uCol = 1;
var listRow = new List <string>();
foreach (var curCell in cfeed.Entries.OfType <CellEntry>())
{
// NOTE: This completely ignores blank lines in the spreadsheet
if (uRow != curCell.Cell.Row)
{
// new row, flush the previous
listLines.Add(listRow);
listRow = new List <string>();
uRow = curCell.Cell.Row;
uCol = 1;
}
// fill in any missing columns with empty strings
if (uCol != curCell.Cell.Column)
{
while (uCol < curCell.Cell.Column)
{
listRow.Add(string.Empty);
uCol++;
}
}
listRow.Add(curCell.Cell.Value ?? string.Empty);
uCol++;
}
// always flush the last line
listLines.Add(listRow);
}
if (bFoundSheet)
{
break;
}
Logger.AddLogLine("Google: Failed to find sheet: " + sSheetName);
}
if (!bFoundSpreadsheet)
{
Logger.AddLogLine("Google: Failed to find spreadsheet: " + sSpreadsheetName);
}
processNewLines(listLines);
return(listLines);
}
public static List <Tuple <string, string> > GetAirBnbPasswordsFromSpreadsheet()
{
string ServiceAccountEmail = Config.I.GoogleServiceAccountEmail;
string x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer =
new ServiceAccountCredential.Initializer(ServiceAccountEmail)
{
Scopes = new[] { @"https://spreadsheets.google.com/feeds", @"https://docs.google.com/feeds" }
}.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
//var service = new SpreadsheetsService(null) { RequestFactory = requestFactory };
SpreadsheetsService service = new SpreadsheetsService("senstay");
service.RequestFactory = requestFactory;
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
//Console.WriteLine("Your spreadsheets:");
SpreadsheetEntry AirPasswords = null;
foreach (SpreadsheetEntry entry in feed.Entries)
{
var TitleStripted = entry.Title.Text + "";
if (TitleStripted.Contains("Airbnb Passwords for Nikita"))
{
AirPasswords = entry;
break;
}
//Console.WriteLine(entry.Title.Text);
}
AtomLink link = AirPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery queryW = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feedW = service.Query(queryW);
WorksheetEntry Worksheet = null;
foreach (WorksheetEntry worksheet in feedW.Entries)
{
Worksheet = worksheet;
break;
//Console.WriteLine(worksheet.Title.Text);
}
AtomLink cellFeedLink = Worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery queryC = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feedC = service.Query(queryC);
//Console.WriteLine("Cells in this worksheet:");
var Passwords = new Dictionary <uint, Tuple <string, string> >();
foreach (CellEntry curCell in feedC.Entries)
{
//Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);
var col = curCell.Cell.Column;
var row = curCell.Cell.Row;
var value = curCell.Cell.Value;
Tuple <string, string> EmailAndPassword = null;
if (Passwords.ContainsKey(row))
{
EmailAndPassword = Passwords[row];
}
else
{
EmailAndPassword = new Tuple <string, string>("", "");
}
if (col == 1)
{
EmailAndPassword = new Tuple <string, string>(value, EmailAndPassword.Item2);
}
else if (col == 2)
{
EmailAndPassword = new Tuple <string, string>(EmailAndPassword.Item1, value);
}
if (!EmailAndPassword.Item1.Contains("@"))
{
continue;
}
Passwords[row] = EmailAndPassword;
}
/*
* foreach (var i in Passwords)
* {
* Console.WriteLine(i.Key + ") " + i.Value.Item1 + " = " + i.Value.Item2);
* }
* //*/
//Console.WriteLine(Passwords.Values);
//Console.ReadLine();
return(GetList(Passwords));
}
public static List <AirBnbAccountCredentials> GetAirBnbPasswordsFromSpreadsheet()
{
var serviceAccountEmail = Config.I.GoogleServiceAccountEmail;
var x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential
.Initializer(serviceAccountEmail)
{
Scopes = new[] {
@"https://spreadsheets.google.com/feeds",
@"https://docs.google.com/feeds"
}
}
.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("senstay")
{
RequestFactory = requestFactory
};
var query = new SpreadsheetQuery();
var feed = service.Query(query);
SpreadsheetEntry airPasswords = null;
foreach (var atomEntry in feed.Entries)
{
var entry = (SpreadsheetEntry)atomEntry;
var titleStripted = entry.Title.Text + "";
if (!titleStripted.Contains("Airbnb Passwords for Nikita"))
{
continue;
}
airPasswords = entry;
break;
}
if (airPasswords == null)
{
return(new List <AirBnbAccountCredentials>());
}
var link = airPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var queryW = new WorksheetQuery(link.HRef.ToString());
var feedW = service.Query(queryW);
var worksheet = feedW.Entries.Cast <WorksheetEntry>().FirstOrDefault(x => x.Title.Text == Config.I.ActualPasswordsSheetTitle);
if (worksheet == null)
{
return(new List <AirBnbAccountCredentials>());
}
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var queryC = new CellQuery(cellFeedLink.HRef.ToString());
var feedC = service.Query(queryC);
var passwords = new Dictionary <uint, AirBnbAccountCredentials>();
foreach (var atomEntry in feedC.Entries)
{
var curCell = (CellEntry)atomEntry;
var col = curCell.Cell.Column;
var row = curCell.Cell.Row;
var value = curCell.Cell.Value;
var credentials = passwords.ContainsKey(row) ? passwords[row] : new AirBnbAccountCredentials();
switch (col)
{
case 1:
credentials.Email = value;
break;
case 2:
credentials.Password = value;
break;
case 4:
case 5:
if (!string.IsNullOrEmpty(value))
{
credentials.Proxies.Add(value);
}
break;
}
if (credentials.Email == null || !credentials.Email.Contains("@"))
{
continue;
}
passwords[row] = credentials;
}
return(GetList(passwords));
}
private static ListFeed GetListFeed()
{
var serviceAccountEmail = Config.I.GoogleServiceAccountEmail;
var x509File = Config.I.X509;
var certificate = new X509Certificate2(x509File, "notasecret", X509KeyStorageFlags.Exportable);
var serviceAccountCredentialInitializer = new ServiceAccountCredential
.Initializer(serviceAccountEmail)
{
Scopes = new[] {
@"https://spreadsheets.google.com/feeds",
@"https://docs.google.com/feeds"
}
}
.FromCertificate(certificate);
var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);
if (!credential.RequestAccessTokenAsync(CancellationToken.None).Result)
{
throw new InvalidOperationException("Access token request failed.");
}
var requestFactory = new GDataRequestFactory(null);
requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);
var service = new SpreadsheetsService("senstay")
{
RequestFactory = requestFactory
};
var query = new SpreadsheetQuery();
var feed = service.Query(query);
SpreadsheetEntry airPasswords = null;
foreach (var atomEntry in feed.Entries)
{
var entry = (SpreadsheetEntry)atomEntry;
var titleStripted = entry.Title.Text + "";
if (!titleStripted.Contains("Airbnb Passwords for Nikita"))
{
continue;
}
airPasswords = entry;
break;
}
var link = airPasswords.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var queryW = new WorksheetQuery(link.HRef.ToString());
var feedW = service.Query(queryW);
var worksheet = feedW.Entries.Cast <WorksheetEntry>().FirstOrDefault();
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
var queryC = new ListQuery(cellFeedLink.HRef.ToString());
return(service.Query(queryC));
}
