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

Integrate bootstrap and jquery in spfx

1. Create your solution with "No JavaScript Framework"

2. Install below bootsrap and jquery using npm command

npm install jquery --save
npm install @types/jquery --save
npm install bootstrap --save
npm install @types/bootstrap --save
npm install @types/jqueryui --save

3. add reference on webpart where you want to call bootstrap and jquery

import * as jQuery from 'jquery';
import * as bootstrap from 'bootstrap';
import 'jquery/dist/jquery.min.js';
import 'bootstrap/dist/css/bootstrap.css';
import 'bootstrap/dist/js/bootstrap.js';

4. suppose that we have to use bootstap modal  then we have paste below HTML inside  render method

<button type="button" id="btnModel" class="${styles.button}">Open Modal</button>
<div class="modal fade" id="myModal" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal">&times;</button>
                <h4 class="modal-title">Modal Header</h4>
            </div>
            <div class="modal-body">
                <p>Welcome bootstrap model popup in sharepoint framework client side webpart</p>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>



5. Create a funcntion to open the modal popup on button click event

private PageLoad():void{
jQuery("#btnModel").click(f=>{
  jQuery("#myModal").modal("show");
});

6. PageLoad() Function inside Render method after HTML binding

this.PageLoad();