Category Archives: Offiice 365

How To : Use the Office 365 API Client Libraries (Javascript and .Net)

blog-office365

One of the cool things with today’s Office 365 API Tooling update is that you can now access the Office 365 APIs using libraries available for .NET and JavaScript.

 

\\8These libraries make it easier to interact with the REST APIs from the device or platform of your choice. And when I say platform of your choice, it really is! Office 365 API and the client libraries support the following project types in Visual Studio today:https://sharepointsamurai.wordpress.com/wp-admin/post.php?post=1625&action=edit&message=10

  1. NET Windows Store Apps
  2. .NET Windows Store Universal Apps
  3. Windows Forms Applications
  4. WPF Applications
  5. ASP.NET MVC Web Applications
  6. ASP.NET Web Forms Applications
  7. Xamarin Android and iOS Applications
  8. Multi-device Hybrid Apps

p.s: support for more projects coming on the way….

Few Things Before We Get Started

  • The authentication library is released as “alpha”.
    • If you don’t see something you want or if you think we missed addressing some scenarios/capabilities, let us know!
    • In this initial release of the authentication library, we focused on simplifying the getting started experience, especially for Office 365 services and not so much on the interoperability across other services (that support OAuth) but that’s something we can start looking for next updates to make it more generic.
  • The library is not meant to replace Active Directory Authentication Library (ADAL) but it is a wrapper over it (where it exists) which gives you a focused getting started experience.
    • However, If you want to opt out and go “DIY”, you still can.

Setting Up Authentication

The first step to accessing Office 365 APIs via the client library is to get authenticated with Office 365.

Once you configure the required Office 365 service and its permissions, the tool will add the required client libraries for authentication and the service into your project.

Lets quickly look at what authenticating your client looks like.

Getting Authenticated

Office 365 APIs use OAuth Common Consent Framework for authentication and authorization.

Below is the code to authenticate your .NET application:

Authenticator authenticator = new Authenticator();

AuthenticationInfo authInfo =
await authenticator.AuthenticateAsync(ExchangeResourceId);

Below is the JS code snippet used for authentication in Cordova projects:

var authContext = new O365Auth.Context();
authContext.getIdToken('https://outlook.office365.com/')
.then((function (token) {
    var client = new Exchange.Client('https://outlook.office365.com/ews/odata', 
                         token.getAccessTokenFn('https://outlook.office365.com'));
    client.me.calendar.events.getEvents().fetch()
        .then(function (events) {
            // get currentPage of events and logout
            var myevents = events.currentPage;
            authContext.logOut();
        }, function (reason) {
            // handle error
        });
}).bind(this), function (reason) {
    // handle error
});

Authenticator Class

The Authenticator class initializes the key stuff required for authentication:

1) Office 365 app client Id

2) Redirect URI

3) Authentication URI

You can find these settings in:

– For Web Applications – web.config

– For Windows Store Apps – App.xaml

– For Desktop Applications (Windows Forms & WPF) – AssemblyInfo.cs/.vb

– For Xamarin Applications – AssemblyInfo.cs

If you would like to provide these values at runtime and not from the config files, you can do so by using the alternate constructor:

image

To authenticate, you call the AuthenticateAsync method by passing the service’s resource Id:

AuthenticationInfo authInfo = await authenticator.AuthenticateAsync(ExchangeResourceId);

If you are using the discovery service, you can specify the capability instead of the resource Id:

AuthenticationInfo authInfo =
await authenticator.AuthenticateAsync("Mail", ServiceIdentifierKind.Capability);

The string to use for other services if you use discovery service: Calendar, Contacts and MyFiles

NOTE:

– For now, if you want to use the discovery service, you will also need to configure a SharePoint resource, either Sites or My Files. This is because the discovery service currently uses SharePoint resource Id.

– Active Directory Graph & Sites do not support discovery service yet

Depending on your client, the AuthenticateAsync will open the appropriate window for you to authenticate:

– For web applications, you will be redirected to login page to authenticate

– For Windows Store Apps, you will get dialog box to authenticate

– For desktop apps, you will get a dialog window to authenticate

image

AuthenticatorInfo Class

Once successfully authenticated, the method returns an AuthenticatorInfo object which helps you to get the required access token:

ExchangeClient client =
new ExchangeClient(new Uri(ExchangeServiceRoot), authInfo.GetAccessToken);

And also help you re-authenticate for a different resource when you create the service client.

AuthenticationInfo graphAuthInfo =
    await authInfo.ReauthenticateAsync("https://graph.windows.net/");

The library automatically handles token lifetime management by monitoring the expiration time of the access token and performing a refresh automatically.

Thats it! – Now you can make subsequent calls to the service to return the items you want!

Authentication Library

For .NET projects:

The library is available as a Nuget package. So, if you want to add it manually to your project without the tool, you could do so. However, you will have to manually register an app in the Azure Active Directory to authenticate against AAD.

Microsoft Office 365 Authentication Library for ASP.NET

Microsoft Office 365 Authentication Library for .NET (Android and iOS)

Microsoft Office 365 Authentication Library for ASP.NET

For Cordova projects:

You will need to use the Office 365 API tool which generates the aadgraph.js under the Scripts folder that handles authentication.

Microsoft Site Templates Upgraded and are now available

 

One of the main goals of the application templates is to provide a demonstration of the application building power in SharePoint and as a potential starting point for larger, more robust applications. While these templates are fully functional and usable out-of-the-box, I’ll be happy to reply on your comments and supporting you as needed.

 

note: those templates were collected from several resources and no source code for them.

All templates are compatible with SharePoint Server 2010 and Foundation Server 2010.

Case Management

The Case Management application template helps case managers track the status and tasks required to complete their work. When a case is created, standard tasks and documents are created which are modified based on the work each case manager has completed.

Clinical Trial Initiation and Management

For those who work in Academic Medical Centers, the Clinical Trial Initiation and Management application template helps teams manage the process of tracking clinical trial protocols, objective setting, subject selection and budget activities.

Employee Activities Site
employee activities
The Employee Activities Site application template helps departments, such as HR and Marketing, manage the creation and attendance of events for employees.

Employee Training Scheduling and Materials

The Employee Training Scheduling and Materials application template helps Instructors add new courses and organize course materials. Employees use the site to schedule attendance at a course, track courses they’ve attended and to provide feedback.

Employee Training 01

Employee Training

Employee Training 03

Absence Request and Vacation Schedule Management

The Absence Request and Vacation Schedule Management application template helps provider departments manage requests for out of office days and provides dashboards showing which users are signed up for a set of responsibilities

Event Planning

The Event Planning application template helps teams organize events efficiently through the use online registration, schedules, communication and feedback.

Discussion Database

The Discussion Database application template provides a location where team members can create and reply to discussion topics.

Team Work Site

The Team Work Site application template provides a place where clinical and business teams, can upload background documents, track scheduled calendar events and submit action items that result from team meetings.

Document Library and Review

The Document Library and Review application template helps people to manage the review cycle common to processes like publication, knowledge management and project plan development.

Knowledgebase

The Knowledgebase application template helps teams manage the information that is resident within their organization. The template enables team members to upload/create documents using Web-based tools and tag them with relevant identifying information.

Policies and Procedures Solution Accelerator

The Policies and Procedures Solution Accelerator assists healthcare organizations create, maintain, publish and easily access policy and procedure information. It also provides the ability to upload documents, maintain a version history and manage tasks.

Board of Directors

The Board of Directors application template provides a single location for an external group of members to store and locate common documents such as quarterly reviews, shareholder meeting notes and annual strategy documents.

Business Performance Reporting

The Business Performance Reporting application template helps health organization managers track the satisfaction of internal customers/patients through a combination of surveys and discussions.

Request for Proposal

The Request for Proposal application template helps manage the process of creating and releasing an initial RFP, collecting submissions of proposals and formally accepting the selected proposal from amongst those submitted.

Compliance Process Support Site

The Compliance Process Support Site application template helps both teams and executive sponsors to manage compliance implementation endeavors, such as HIPAA.

Expense Reimbursement and Approval

The Expense Reimbursement and Approval application template helps manage elements of the expense approval process, including creation and approval. Users can monitor the status of their reimbursement request through a filtered view listing.

Scorecards Solution Accelerator

The Scorecards solution accelerator acts as a template for configuring a management dashboard to track organizational metrics. It contains four example dashboards ranging from a primary care practice to a healthcare organization’s CEO dashboard.

Call Center
call center
The Call Center application template helps departments manage the process of handling customer service requests. The application template helps teams manage service requests from issue identification to cause analysis and resolution.

Help Desk
help desk
The Help Desk application template helps departments manage the process of handling service requests. Team members use the application template to identify a service request, manage identification of the root cause and track solution status.

Physical Asset Tracking and Management

The Physical Asset Tracking and Management application template helps departments, such as Facilities, BioMedical, Surgery, etc. manage requests and the tracking of physical assets.

Inventory Tracking
inventory
The Inventory Tracking application template helps organizations track elements associated with inventory, including creation of inventory. Users are notified when each part reaches the reorder quantity and helps manage customer and supplier information.

Cafeteria Menu Management

The Cafeteria Menu Management application template helps hospital Food & Nutrition staff easily communicate daily menu choices to hospital staff and visitors. It allows staff to develop/schedule menus and provide related nutritional information.

Budgeting and Tracking Multiple Projects

The Budgeting and Tracking Multiple Projects application template helps project teams track and budget multiple, interrelated sets of activities. Management tools such as assignment of new tasks, Gantt Charts and common status designators.

Change Request Management
change request management
The Change Request Management application template helps users track risks associated with a design change. Team members can submit a change request, notifying stakeholders of the risks involved with the change.

IT Team Workspace

The IT Team Workspace application template helps teams manage the development, deployment and support of software projects. It also includes help desk functionality, allowing team members to guide service requests from initiation to resolution.

Project Tracking Workspace

The Project Tracking Workspace application template helps small team projects manage project information in a single location. The application template provides a place where a team can list and view project issues and tasks.

 

 

 

Microsoft BI and the new PowerQuery for Excel – How we empower users

Introduction to Microsoft Power Query for Excel

Microsoft Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.

With Power Query 2.10, you can share and manage queries as well as search data within your organization. Users in the enterprise can find and use these shared queries (if it is shared with them) to use the underlying data in the queries for their data analysis and reporting. For more information about how to share queries, see Share Queries.

With Power Query, you can

  • Find and connect data across a wide variety of sources.
  • Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as Power Pivot and Power View.
  • Create custom views over data.
  • Use the JSON parser to create data visualizations over Big Data and Azure HDInsight.
  • Perform data cleansing operations.
  • Import data from multiple log files.
  • Perform Online Search for data from a large collection of public data sources including Wikipedia tables, a subset of Microsoft Azure Marketplace, and a subset of Data.gov.
  • Create a query from your Facebook likes that render an Excel chart.
  • Pull data into Power Pivot from new data sources, such as XML, Facebook, and File Folders as refreshable connections.
  • With Power Query 2.10, you can share and manage queries as well as search data within your organization.

New updates for Power Query

The Power Query team has been busy adding a number of exciting new features to Power Query. You can download the update from this page.

New features for Power Query include the following, please read the rest of this blog post for specific details for each.

  • New Data Sources
    • Updated “Preview” functionality of the SAP BusinessObjects BI Universe connectivity
    • Access tables and named ranges in a workbook
  • Improvements to Query Load Settings
    • Customizable Defaults for Load Settings in the Options dialog
    • Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limit
    • Preserve data in the Data Model when you modify the Load to Worksheet setting of a query that is loaded to the Data Model
  • Improvements to Query Refresh behaviors in Excel
    • Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables
    • Preserve results from a previous query refresh when a new refresh attempt fails
  • New Transformations available in the Query Editor
    • Remove bottom rows
    • Fill up
    • New statistic operations in the Insert tab
  • Other Usability Improvements
    • Ability to reorder queries in the Workbook Queries pane
    • More discoverable way to cancel a preview refresh in the Query Editor
    • Keyboard support for navigation and rename in the Steps pane
    • Ability to view and copy errors in the Filter Column dropdown menu
    • Remove items directly from the Selection Well in the Navigator
    • Send a Frown for Service errors

Connect to SAP BusinessObjects BI Universe (Preview)

This connectivity has been a separate Preview feature for the last month or so. In this release, we are incorporating the SAP BusinessObjects BI Universe connector Preview capabilities as part of the main Power Query download for ease of access. With Microsoft Power Query for Excel, you can easily connect to an SAP BusinessObjects BI Universe to explore and analyze data across your enterprise.

Access tables and named ranges in an Excel workbook

With From Excel Workbook, you can now connect to tables and named ranges in your external workbook sheets. This simplifies the process of selecting useful data from an external workbook, which used to be limited to sheets and users had to “manually” scrape the data (using Query transform operations).

 

Customizable Defaults for Load Settings in the Options dialog

You can override the Power Query default Load Settings in the Options dialog. This will set the default Load Settings behavior for new queries in areas where Load Settings are not exposed directly to the user, such as in Online Search results and the Navigator task pane in single-table import mode. In addition, this will set the default state for Load Settings where these settings are available including the Query Editor, and Navigator in multi-table import mode.

           

Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables

With this Power Query Update, Custom Columns, conditional formatting in Excel, and other customizations of worksheet tables are preserved after you refresh a query. Power Query will preserve worksheet customizations such as Data Bars, Color Scales, Icon Sets or other value-based rules across refresh operations and after query edits.

Preserve results from a previous query refresh when a new refresh attempt fails

After a refresh fails, Power Query will now preserve the previous query results. This allows you to work with slightly older data in the worksheet or Data Model and lets you refresh the query results after fixing the cause of errors.

Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limits

When you are working with large volumes of data in your workbook, you could reach the limits of Excel’s worksheet size. When this occurs, Power Query will automatically recommend to load your query results to the Data Model. The Data Model can store very large data sets.

Preserve data in the Data Model when modifying the Load to Worksheet setting of a query that is loaded to the Data Model

With Power Query, data and annotations on the Data Model are preserved when modifying the Load to Worksheet setting of a query. Previously, Power Query would reset the query results in both the worksheet and the Data Model when modifying either one of the two load settings.      

Remove Bottom Rows

A very common scenario, especially when importing data from the Web and other semi-structured sources, is having to remove the last few rows of data because the contents do not belong to the data set. For instance, it’s common to remove links to previous/next pages or comments. Previously, this was possible only by using a composition of custom formulas in Power Query. This transformation is now much easier by adding a library function called Table.RemoveLastN(), and a button for this transformation in the Home tab of the Query Editor ribbon.

 

Fill Up

Power Query already supports the ability to fill down values in a column to neighboring empty cells. Starting with this update, you can now fill values up within a column as well. This new transformation is available as a new library function called Table.FillUp(), and a button on the Home tab of the Query Editor ribbon.

New Statistics operations in the Insert tab

The Insert tab provides various ways to insert new columns in queries, based on custom formulas or by deriving values based on other columns. You can now apply Statistics operations based on values from different columns, row by row, in their table.

 

Ability to reorder queries in the Workbook Queries pane

With the latest Power Query update, you can move queries up or down in the Workbook Queries pane. You can right-click on a query and select Move Up or Move Down to reorder queries.

More discoverable way of cancelling refresh of a preview in the Query Editor

The Cancel option is now much more discoverable inside the Query Editor dialog. In addition to the Refresh dropdown menu in the ribbon, this option can now be found in the status bar at the bottom right corner of the Query Editor, next to the download status information.

  

Keyboard support for navigation and rename in the Steps pane

You can now use the Up/Down Arrow keys to navigate between steps in your query. Also, press the F2 key to rename the current step.

Ability to view and copy errors in the Filter Column dropdown menu

You can easily view and copy error details inside the Filter Column menu. This is very useful to troubleshoot errors while retrieving filter values.

Remove items directly from the Selection Well in the Navigator

You can remove items directly from the Selection Well instead of having to find the original item in the Navigator tree to deselect it.

 

Send a Frown for Service errors

We try as hard as possible to improve the quality of Power Query and all of its features. Even then, there are cases in which errors can happen. You can now send a frown directly from experiences where a service error happened, for instance, an error retrieving a Search result preview or downloading a query from the Data Catalog. This will give us enough information about the service request that failed and the client state to troubleshoot the issue.

That’s all for this update! We hope that you enjoy these new Power Query features. Please don’t hesitate to contact us via the Power Query Forum or send us a smile/frown with any questions or feedback that you may have.

You can also follow these links to access more resources about Power Query and Power BI: