SharePoint Analytics Webpart using SPFX

1. Add External Reference in Config.json file

"externals": {"google": {

"path": "https://www.gstatic.com/charts/loader.js",

"globalName": "google"

}},

2. Create a file inside webpart google.d.ts
3.  Add below code inside google.d.ts

declare module "google" {

interface IGoogle {

charts: any;

visualization: any;

}

var google: IGoogle;

export = google;

}

4. add below code in webpart ts file
import { Version } from '@microsoft/sp-core-library';

import { BaseClientSideWebPart } from '@microsoft/sp-webpart-base';

import {

IPropertyPaneConfiguration,

PropertyPaneTextField

} from '@microsoft/sp-property-pane';

import { escape } from '@microsoft/sp-lodash-subset';

import { SPComponentLoader } from '@microsoft/sp-loader';

import styles from './SpfxSiteAnalyticsWebPart.module.scss';

import * as strings from 'SpfxSiteAnalyticsWebPartStrings';

import pnp, { GraphHttpClient, SortDirection, Web } from 'sp-pnp-js';

export interface ISpfxSiteAnalyticsWebPartProps {

topHeadBG: string;

graphBG:string;

tableBG:string;

tableStyle:string;

tableHeader:string;

}

//declare var google:any;

import * as google from 'google';

import { PnPClientStorage } from 'sp-pnp-js';

import * as jQuery from 'jquery';

import 'jqueryui';

import * as bootstrap from 'bootstrap';

import 'jquery/dist/jquery.min.js';

import 'bootstrap/dist/css/bootstrap.css';

import 'bootstrap/dist/js/bootstrap.js';

let webUrlResult =[];

export default class SpfxSiteAnalyticsWebPart extends BaseClientSideWebPart<ISpfxSiteAnalyticsWebPartProps> {

public render(): void {

this.domElement.innerHTML = `

<div class="${ styles.spfxSiteAnalytics }">

<div class="${ styles.container }">

<div class="${ styles.row }" style="background-color:${this.properties.topHeadBG}">

<div class="row">

<div class="col-lg-3">

<label class="${styles.label}">View</label>

<select id="ddlViews" class="form-control">

<option>ViewsRecent</option>

<option>ViewsLifeTime</option>

<option>ViewsLifeTimeUniqueUsers</option>

</select>

</div>

<div class="col-lg-3">

<label class="${styles.label}">Top</label>

<select id="ddlTop" class="form-control">

<option>10</option>

<option>20</option>

<option>30</option>

<option>50</option>

<option>60</option>

<option>100</option>

</select>

</div>

<div class="col-lg-3">

<label class="${styles.label}">Site URL</label>

<select id="ddlsitesUrl" class="form-control"></select>

</div>

<div class="col-lg-3">

<label class="${styles.label}"></label>

</div>

</div>

<div class="row">

<div class="col-lg-3">

<label class="${styles.label}">Refine Search</label>

<select id="ddlRefine" class="form-control">

<option>All</option>

<option>Custom</option>

<option>Select</option>

</select>

</div>

<div class="col-lg-3">

<label class="${styles.label}">Extension</label>

<select id="ddlExtension" class="form-control">

<option>All</option>

<option>ppt</option>

<option>pptx</option>

<option>doc</option>

<option>docx</option>

<option>aspx</option>

<option>msg</option>

<option>xlx</option>

<option>xlsx</option>

<option>png</option>

<option>jpg</option>

<option>jpeg</option>

</select>

</div>

<div class="col-lg-3">

<label class="${styles.label}">Custom Extension</label>

<input type="text" class="form-control" id="txtExtension">

</div>

<div class="col-lg-3">

<label class="${styles.label}"></label>

<input id="btnLoad" type="button" style="margin-top: 25px;" value="Load" class="${styles.button}">

</div>

</div>

</div>

<div class="${ styles.row }" style="background-color:${this.properties.graphBG}">

<div class="${ styles.column }">

<div id="graphChart" style="width: 550px; height: 400px; margin: 0 auto"></div>

</div>

</div>

<div class="${ styles.row }" style="background-color:${this.properties.tableBG}">

<div class="${ styles.column }">

<div>

<table border="1" style="${this.properties.tableStyle}">

<thead style="${this.properties.tableHeader}">

<th>Id</th>

<th>Page URL</th>

<th>Views</th>

</thead>

<tbody id="HTMLtable"></tbody>

</table>

</div>

</div>

</div>

</div>

</div>

`;

this.PageLoad();

}

protected get dataVersion(): Version {

return Version.parse('1.0');

}

private PageLoad()

{

const webpart:SpfxSiteAnalyticsWebPart=this;

jQuery("#btnLoad").click(check=>

{

webpart.CheckSiteAudiance("path:"+jQuery("#ddlsitesUrl").val());

});

jQuery("#ddlRefine").change(chk=>{

let refineValue=jQuery("#ddlRefine").val();

if(refineValue=="All")

{

jQuery("#txtExtension").val('');

jQuery("#ddlExtension").val('All');

}

else if(refineValue=="Custom")

{

jQuery("#ddlExtension").val('All');

}

else if(refineValue=="Select")

{

jQuery("#txtExtension").val('');

}

});

jQuery("#ddlsitesUrl").append(jQuery("<option></option>").attr("value", "*").text("All"));

webpart.LoadAllSites(this.context.pageContext.web.absoluteUrl);

}

private LoadAllSites(webUrl:string)

{

const webpart:SpfxSiteAnalyticsWebPart=this;

let b = pnp.sp.createBatch();

jQuery("#ddlsitesUrl").append(jQuery("<option></option>").attr("value",this.context.pageContext.web.absoluteUrl).text("Current Site"));

// Get current site call recursive

pnp.sp.web.expand("Webs").get().then( w => {

if(w.Webs.length > 0)

{

w.Webs.forEach(subsite=> {

// Add subsites to array

webUrlResult.push({

title: subsite.Title,

url: subsite.Url

});

jQuery("#ddlsitesUrl").append(jQuery("<option></option>").attr("value", subsite.Url).text(subsite.Title));

});

}

});

}

public onInit(): Promise<void>

{

SPComponentLoader.loadCss("https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css");

google.charts.load('current', {packages: ['corechart']});

return super.onInit();

}

private LoadGoogleChart(arrayDataTable:any)

{

arrayDataTable.splice(0, 0, ['Page', 'Page Count',{ role: 'annotation'}]);

let data =google.visualization.arrayToDataTable(arrayDataTable);

let options = {title: 'Top Pages',

hAxis: {

title: 'Page Number',

},

vAxis: {

title: 'Page Count'

}

};

let chart =new google.visualization.ColumnChart(document.getElementById('graphChart'));

chart.draw(data, options);

}

private CheckSiteAudiance(tragetSiteURL:string)

{

const webpart:SpfxSiteAnalyticsWebPart=this;

let ddlViews=jQuery("#ddlViews").val();

let ddlTop:number=Number(jQuery("#ddlTop").val());

let refineValue=jQuery("#ddlRefine").val();

let refineSearch:string;

if(refineValue=="All")

{

jQuery("#txtExtension").val('');

jQuery("#ddlExtension").val('All');

}

else if(refineValue=="Custom")

{

refineSearch=jQuery("#txtExtension").val().trim();

jQuery("#ddlExtension").val('All');

if(refineSearch.length==0)

{

alert("Please enter custom value");

return;

}

}

else if(refineValue=="Select")

{

refineSearch=jQuery("#ddlExtension").val();

jQuery("#txtExtension").val('');

if(refineSearch=="All")

{

alert("Please select any extension");

return;

}

}

let searchFilters = {

Querytext:tragetSiteURL,

RowLimit: ddlTop,

SelectProperties: ['Title','OriginalPath','ViewsLifeTime','ViewsRecent','ViewsLifeTimeUniqueUsers'],

TrimDuplicates: false,

SortList: [{Property:ddlViews,Direction:SortDirection.Descending}],

RefinementFilters : ["fileExtension:equals('"+refineSearch+"')"]

};

if(refineValue=="All")

{

delete searchFilters.RefinementFilters;

}

pnp.sp.search(searchFilters).then(result=>{

let items=result.PrimarySearchResults;

jQuery("#graphChart").html("");

jQuery("#HTMLtable").html("");

if(items.length>0)

{

let graphDataArray=[];

let serialNumber=1;

items.forEach(item=>

{

jQuery("#HTMLtable").append("<tr><td>"+serialNumber+"</td><td style='word-break: break-all;'>"+item.OriginalPath+"</td><td>"+Number(item[ddlViews])+"</td></tr>");

graphDataArray.push([String(serialNumber++),Number(item[ddlViews]),Number(item[ddlViews])]);

});

google.charts.setOnLoadCallback(webpart.LoadGoogleChart(graphDataArray));

}

}).catch(error=>{

console.log(error);

});

}

protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {

return {

pages: [

{

header: {

description: strings.PropertyPaneDescription

},

groups: [

{

groupName: strings.BasicGroupName,

groupFields: [

PropertyPaneTextField('topHeadBG', {

label: "Page Header Background"

}),

PropertyPaneTextField('graphBG', {

label: "Graph Area Background"

}),

PropertyPaneTextField('tableBG', {

label: "Table Area Background"

}),

PropertyPaneTextField('tableStyle', {

label: "Table Style",value:'border: 1px solid black;width:600px;color:black;background:white;'

}),

PropertyPaneTextField('tableHeader', {

label: "Table Header Style",value:'background: #005a9e; color: white; width: 600px;padding: 5px;;'

})

]

}

]

}

]

};

}

}

5. Run gulp bundle command
6. run gulp serve command
7. open workbench and add SPFXAnalyticWebpart on page
8. See the below result full customization report

Read Excel Data Using ODATA Excel Service

Use below code to read data from excel, stored excel in SharePoint library


function GetExcelContent()
{
    var TableName = "Table1";
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_vti_bin/ExcelRest.aspx/Documents/test.xlsx/OData/" + TableName + "?$inlinecount=allpages&$select=Column1,Column2,Column3,Column4,Column5",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        dataType: "json",
        async: false,
        success: function (data)
        {
            var items = data.d.results;
            console.log(items);
            for (var i = 0; i < items.length; i++)
            {
                console.log(items[i].Column1);
            }
        },
        error: function (data) {
            console.log(data);
        }
    });

}


Below is reference from Microsoft site https://docs.microsoft.com/en-us/sharepoint/dev/general-development/requesting-excel-workbook-data-from-sharepoint-server-using-odata
  

Enable Site Collection level app catalog in sharepoit oniline

1. Open SharePoint Online Management Shell

2. Type below command and hit enter
Connect-SPOService

3. enter  sharepoint admin site url like below
 https://tenant-admin.sharepoint.com

4. run below command to enable site collection app catalog
$site = Get-SPOSite https://tenant.sharepoint.com/sites/siteCollection

Hide Site Content and other Layout pages using spfx

Hide Site Content using SPFX extension

1. Create a SharePoint group "HideApplicationPages" .
2. Deploy package in App catalog "hide-application-pages-with-site-content.sppkg".
3. Add App on SharePoint site where you  want to hide the site content.


Download package from here Click here

add external js reference on every page using spfx

 //define a property
 private _externalJsUrl: string = "https://extrenalSP.azurewebsites.net/scripts/SPFxGlobalScript.js";

 //add below code snippet on onInit() method
  @override
  public onInit(): Promise<void> {
    console.log(`IHideSiteContentApplicationCustomizerProperties.onInit(): Entered.`);
    let scriptTag: HTMLScriptElement = document.createElement("script");
    scriptTag.src = this._externalJsUrl;
    scriptTag.type = "text/javascript";
    document.getElementsByTagName("head")[0].appendChild(scriptTag);
    console.log(`IHideSiteContentApplicationCustomizerProperties.onInit(): Added script link.`);
    console.log(`IHideSiteContentApplicationCustomizerProperties.onInit(): Leaving.`);
    return Promise.resolve();
}

Classic People picker in Spfx

// add sp component loader
import { SPComponentLoader } from '@microsoft/sp-loader';


//Add below people picker html

<div id="peoplePickerUsers"></div>

// add the below function to OnInit methos

 public onInit(): Promise<void>
  {
  SPComponentLoader.loadCss("/_layouts/15/1033/styles/Themable/corev15.css");
   SPComponentLoader.loadScript('/_layouts/15/init.js', {
      globalExportsName: '$_global_init'
    }) .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/1033/strings.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/clienttemplates.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/MicrosoftAjax.js', {
        globalExportsName: 'Sys'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/SP.Runtime.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/SP.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/sp.core.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/clientforms.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/clientpeoplepicker.js', {
        globalExportsName: 'SP'
      });
    }).then((): Promise<{}> => {
      return SPComponentLoader.loadScript('/_layouts/15/autofill.js', {
        globalExportsName: 'SP'
      });
    })
    .then((): void =>
    {
     //bind People picker control
     this.initializePeoplePicker("peoplePickerUsers");
    });

   // return Promise.resolve<void>();
return Promise.resolve();
  }

// declare three variable to intialize the control

declare var SP: any;
declare var SPClientPeoplePicker: any;
declare var SPClientPeoplePicker_InitStandaloneControlWrapper: any;

// Add function to intialize the people picker control

private initializePeoplePicker(peoplePickerElementId:string):void
{
    let schema = {};
    schema['PrincipalAccountType'] = 'User,DL,SecGroup,SPGroup';
    schema['SearchPrincipalSource'] = 15;
    schema['ResolvePrincipalSource'] = 15;
    schema['AllowMultipleValues'] = false;
    schema['MaximumEntitySuggestions'] = 50;
    schema['Width'] = '280px';

    SPClientPeoplePicker_InitStandaloneControlWrapper(peoplePickerElementId, null, schema);
}

//To Get The People Picker Value

private getPeopleUserInfoGroups(pickerPickerControlId:string): void

{

this.peoplePickerLoginName="";

//var pickerDiv = $("[id^='" + pickerPickerControlId + "']");

let pickerDiv:any =document.querySelectorAll("[id^='" + pickerPickerControlId + "']");

var peoplePicker = SPClientPeoplePicker.SPClientPeoplePickerDict[pickerDiv[1].id];

let users:any = peoplePicker.GetAllUserInfo();

if (users.length > 0)

{

for (let i:number = 0; i < users.length; i++)

{

console.log(users[i].Key);

this.peoplePickerLoginName=users[i].Key;

this.GetUserIdByLoginName(this.peoplePickerLoginName);

}

}

}

private async GetUserIdByLoginName(loginName:string)

{

let tempUserInformation:any;

tempUserInformation= await pnp.sp.web.siteUsers.getByLoginName(loginName).get();

return tempUserInformation;

}

// To set the people picker value

private setPeoplePickerValue(controlNameID:string, LoginNameOrEmail:string) {

//var fieldName = id + '_TopSpan';

let peoplePickerDiv:any =document.querySelectorAll("[id^='" + controlNameID + "']");

// Get the people picker object from the page.

let peoplePicker:any = SPClientPeoplePicker.SPClientPeoplePickerDict[peoplePickerDiv[1].id];

peoplePicker.AddUserKeys(LoginNameOrEmail, false);

}

spfx provisioning

Deploy files like image,js,css,html and aspx files using Module in SPFX.

1.       Add elements.xml file Inside assets folder  under SharePoint and below xml inside elements.xml file

2.  <?xml version="1.0" encoding="utf-8"?>

3.  <Elements xmlns="http://schemas.microsoft.com/sharepoint/">

4.   

5.     <!-- Upload files to the SPFxDocumentLibrary -->

6.      <Module Name="Files" Url="Doc">

7.          <!-- ISSUE: For some reason `gulp package-solution` breaks the .jpg, .png images.

8.           Workaround is to open the .sppkg with winrar and add the images manually. -->

9.          <File Path="10.jpg" Url="NewFolder/10.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE" >

10.         </File>

11.         <File Path="3.jpg" Url="NewFolder/3.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE" >

12.         </File>

13.         <File Path="4.jpg" Url="NewFolder/4.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE" >

14.         </File>

15.         <File Path="5.jpg" Url="NewFolder/5.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE" >

16.         </File>

17.         <File Path="6.jpg" Url="NewFolder/6.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE" >

18.         </File>

19.         <File Path="11.jpg" Url="CustomFolder/11.jpg" Type="GhostableInLibrary" ReplaceContent="TRUE">

20.         </File>

21.     </Module>

22. </Elements>

23.  

2.  Add files or images inside Assetes folder

3. Open  package-solution.json file and add features inside json properties .

{

  "$schema": "https://developer.microsoft.com/json-schemas/spfx-build/package-solution.schema.json",

  "solution": {

    "name": "spfx-solution-client-side-solution",

    "id": "ac493257-69d8-4c9e-a712-063b9afeba56",

    "version": "1.0.0.4",

    "features": [{

      "title": "spfx-solution-client-side-solution",

      "description": "spfx-solution-client-side-solution",

      "id": "94017C10-F387-43A6-9736-F50CFE8663EF",

      "version": "1.0.0.4",

      "assets": {

        "elementManifests": [

          "elements.xml"

        ],

        "elementFiles":[

          "10.JPG",

          "11.JPG",

          "3.JPG",

          "4.JPG",

          "5.JPG",

          "6.JPG"

        ]

      }

    }],

    "includeClientSideAssets": true,

    "isDomainIsolated": false

  },

  "paths": {

    "zippedPackage": "solution/spfx-solution.sppkg"

  }

}

4. Run the command gulp bundle --ship

5. Run the command gulp package-solution --ship

6. Deploy solution in app catalog

7. add app on site

8. check the library where your assets has been added