Excel-Powered CRUD Operations in SharePoint List using PnP JS and Excel (XLSX) File

Overview

Utilizing Excel as an information source, PnP JS for SharePoint operations, and the XLSX library for parsing Exceed expectations records, I will walk you how to perform out CRUD (Create, Read, Update, Delete) method in a SharePoint list. 

We’ll cover two ways to handle the data: 

• Static Approach – Hardcoded fields for quick implementation. 
  
• Dynamic Approach – Automatically adapts to any list structure by reading Excel headers. 

Whether you're a beginner or a seasoned SharePoint developer, this guide will help you integrate Excel uploads with your SharePoint list seamlessly! 

Prerequisites

Sometime recently we start, guarantee you’ve got the desired bundles installed. Execute the following command in your terminal: 

npm install @pnp/sp @pnp/spfx @pnp/odata xlsx --save

Set Up Your SharePoint Framework (SPFx)

Configure the WebPart.ts File

import { SPFI, spfi } from "@pnp/sp";

import { SPFx } from "@pnp/sp";  

 

export let sp: SPFI;

 

protected async onInit(): Promise<void> {

 sp = spfi().using(SPFx(this.context));

 try {

   const message = await this._getEnvironmentMessage();

   this._environmentMessage = message;

   return super.onInit();

 } catch (error) {

   console.error("Error fetching environment message:", error);

 }

}

Create a Simple State Interface 

If you don't already have a state file, create one: 

export interface IExcelThroughUploadDataToSharePointState {

  fileName: string | null;

}

Handling Excel Upload in .tsx File

Import Required Modules

import * as XLSX from 'xlsx';

import { sp } from '../ExcelThroughUploadDataToSharePointWebPart';

import "@pnp/sp/webs";

import "@pnp/sp/lists";

import "@pnp/sp/items";  

Add Constructor

constructor(props: any) {

  super(props);

  this.state = {

    fileName: null

  };

}  

Handling File Upload and Data Processing

Upload & Process Excel File

private handleFileUpload = async (event: React.ChangeEvent<HTMLInputElement>) => {

  this.setState({ fileName: null });

  const file = event.target.files?.[0];

  if (!file) return;

  this.setState({ fileName: file.name });

 

  const reader = new FileReader();

  reader.onload = async (e: ProgressEvent<FileReader>) => {

    const data = e.target?.result;

    const workbook = XLSX.read(data, { type: 'binary' });

    const worksheet = workbook.Sheets[workbook.SheetNames[0]];

    const jsonData = XLSX.utils.sheet_to_json(worksheet) as any[];

    /*

      We are using as any[] with XLSX.utils.sheet_to_json(worksheet) to quickly

      convert the Excel sheet into JSON format without TypeScript errors or

      warnings. This is a fast and flexible mthod, easpecially useful during

      early development stages when typing isn't necessary. Alternatively,

      for better type safety and IntelliSense support, you can define

      a TypeScript interface like this:

      interfacee ITask {

        Title: string;

        Description: string;

        Status: string;

        DueDate: date;

      }

   

      Then use the generic version of sheet_to_json like this:

      const jsonData = XLSX.utils.sheet_to_json<ITask>(worksheet);

    */

 

    /* To fetch existing items from the SharePoint list named "ProjectTasks",

    we use the following PnPjs method: */

    const existingItems = await sp.web.lists.getByTitle("ProjectTasks").items

    .select("Id", "Title", "Description", "Status")();

    /*  

      In this code:

        - () is important - in the newer SPFI-based version of PnPjs

        (@pnp/sp version 2.x and above), instead of calling .get() at the

        end of the chain, you invoke the entire chain as a function using ().

      This returns the array of results directly.

    */

    /*  

      Create a map for easy lookup of Excel data.

      This helps in checking whether a specific item (based on Title)

      already exists.

        - If the item exists, it can be updated.

        - If it doesn't exist, it can be added.

        - If an existing item is no longer present in the Excel data,

        it can be removed.

    */

    const excelMap = new Map<string, any>();

    jsonData.forEach(item => {

      if (item.Title) excelMap.set(item.Title.trim(), item);

    });

 

    const spMap = new Map<string, any>();

    existingItems.forEach(item => {

      if (item.Title) spMap.set(item.Title.trim(), item);

    });

 

    /*  

      Perform update or create a new entry:

        - Check in the event that the thing from Excel exists within the SharePoint list.

      This ensures the Excel information, and the SharePoint list remains in at this point.

    */

    for (const [title, excelItem] of excelMap) {

      /* update items */

      if (spMap.has(title)) {

        const spItem = spMap.get(title);

        await await sp.web.lists.getByTitle('ProjectTasks').items

        .getById(spItem.Id).update({

             Title: excelItem.Title,

             Description: excelItem.Description,

             Status: excelItem.Status,

             DueDate: this.convertExcelSerialToDate(excelItem.DueDate).toISOString()

           });

        spMap.delete(title);

      } else {

        await sp.web.lists.getByTitle('ProjectTasks').items.add({

          Title: excelItem.Title,

          Description: excelItem.Description,

          Status: excelItem.Status,

          DueDate: this.convertExcelSerialToDate(excelItem.DueDate).toISOString()

        });

      }

    }

 

    /*

     Delete remaining SharePoint items not found in Excel  

    */

    for (const [title, spItem] of spMap) {

      await sp.web.lists.getByTitle("ProjectTasks").items

      .getById(spItem.Id).recycle(); // Use .delete() if permanent removal is needed

    }

    alert('Excel data synced with SharePoint list successfully!');

  };

  reader.readAsBinaryString(file);

}

Dynamic Approach

  /*  

    Dynamic field mapping:

      - This approach allows you to add or remove columns in the Excel file,

      and those changes will automatically reflect in the SharePoint List items

      without needing to change the code.

   */

  /* #### How it works: */

  const itemToAdd: any = {};

  Object.keys(excelItem).forEach(key => {

    itemToAdd[key] = excelItem[key];

  });

  /* #### add item  */

  await sp.web.lists.getByTitle("ProjectTasks").items.add(itemToAdd);

  /* #### update item */

  await sp.web.lists.getByTitle("ProjectTasks").items.getById(spItem.Id).update(itemToAdd);  

/*

  Explanation:

    - `Object.keys(excelItem)` retrieves all column names (as key) from the Excel row.

    - The loop dynamically builds a SharePoint item using those keys and values.

    - This ensures that any number of columns (2 or more) in Exceel will be correctly handled.

    - You do not need to hard-code field names - updates to Excel structure will still work.

*/

Converting Excel Date to SharePoint-Compatible Format

public convertExcelSerialToDate = (serial: number): Date => {

  return new Date((serial - 25569) * 86400 * 1000);

};

Explanation: 

Excel stores dates as serial numbers. This method converts it into a valid JavaScript Date object. 

/*  

  Convert Excel date to Unix Epoch (in milliseconds):

    - Excel stores dates as serial numbers, starting from Jan 1, 1970.

    - To convert Excel serial date to Unix epoch time (starts from Jan 1, 1970):

  Substract 25569 from the Excel serial date

    - because 25569 is the number of days between Jan 1, 1900 and Jan 1, 1970.

  Convert the result to seconds:

    - (serial - 25569) * 86400

    - 86400 is the number of seconds in a day.

  Convert to milliseconds:

    - seconds * 1000

    - Final result will be a JavaScript-compatible timestamp.

*/

Render Upload UI

public render(): React.ReactElement<IExcelThroughUploadDataToSharePointProps> {

  return (

    <div>

      <input type='file' accept=".xlsx, .xls" onChange={this.handleFileUpload} />

      {this.state.fileName && <p>Uploading: {this.state.fileName}</p>}

    </div>

  );

}

Tips for Using Excel Effectively

• Ensure your Excel column headers match the internal field names in SharePoint. 
  
• If you're using dates, convert them into text format in Excel before uploading. 
  
• Dynamic field handling allows you to change list columns without editing the code. 

What You’ve Achieved

By the end of this tutorial, you now know how to: 

• Upload an Excel file in SPFx. 
  
• Parse the file using XLSX. 
  
• Perform Create, Update, and Delete operations powerfully on a SharePoint list. 
  
• Handle dates and dynamic columns gracefully. 

Final Thoughts

Using Excel as a front-facing CRUD interface makes SharePoint even more accessible for end-users. With PnP JS and XLSX to make the syncing handle makes it dynamic and future-proof!

If you have any questions you can reach out our SharePoint Consulting team here.

Get Members of SharePoint Groups in Excel using Power Query

Challenge:

We have several business units. And in that, for each business unit, we have Approvers, Owners, Members and Viewers. The challenge is to get the members of all business units'  "Approvers" SharePoint group in Excel.

Resolution: 

In the previous blog, we have explained about Power Query in Excel and connecting the SharePoint Lists from SharePoint Online.

Now, using Power Query function we would be getting the list members of SharePoint groups. For this, we would be first creating a function which would get the members of any SharePoint group.

• For this, we would go to Data > Get Data > From Other Source(s) > From OData Feed. Then, as per the requirement, we need members of the Approvers groups for all business units. So, in this query, we would be fetching the names of the SharePoint groups having "Approvers" in their names and renaming this as "SPGroups".

https://<domain>/_api/web/SiteGroups?$filter=substringof('Approvers',Title)&$select=Title

• We have the list of SharePoint groups, and now, we would be fetching the members of each SharePoint group, by creating a Power Query function. For this, we would be adding a new blank query and rename it as "GetMemberByGroupName". We know the REST call to get group members is as below:

https://<SiteURL>/_api/web/sitegroups/getbyname('<GroupName>')/users

• So, we would add a parameter "GrpName" as text, which would make a REST Call for each group, and get all the members. The function (in the Advanced Editor) would be like below:

(GrpName as text) as table =>         
let
    Source= OData.Feed("https://<domain>/_api/web/sitegroups/getbyname('"&Text.From(GrpName)&"')/users?$select=Title")
in
    Source

• Now, we would go back to SPGroups (1st query) and in the ribbon Add Column > Invoke Custom function. We would select the function and the column name as a parameter and expand the table we get in Custom column.

• So, here we have the members as per the SharePoint group names.

• Expand "GetMemberByGroupName" column.
  
  
• So, members for the SharePoint group will be extracted here!
  

This way, we can have all the members of the selected SharePoint groups in the Excel file using Power Query.

If you have any questions you can reach out our SharePoint Consulting team here.

Generate test data using GenerateData.com

Introduction:
There are times when you need test data in order to test how your application scales. GenerateData.com helps you generate test data in many popular formats like html, xml, csv. excel etc. Let's see how we can use it.
How to:

• Goto generatedata.com
• Enter column name in column title and select Datatype from drop down.
• For ID AutoIncrement Datatype is selected and in options (Start at ) 1 is entered because from which value user wants to start his id number.
• And in increment it is given 1 because user wants to increase its id by 1.
• Second field is inserted (Functional Group) whose DataType is custom list . The item of custom list is inserted in Enter values Seperated textbox and items should be separated by “|”.
• If user has more rows then shown than user can add more values by entering exact figure and click on Row(s) textbox.​
• After entering all data user has to select Result Type, For Result type option is given on top (HTML,Excel,XML,CSV,SQL), Select the desired Result Type. 
• And finally click on generate button and if user has selected Excel for result type and an excel will generate and then user can import excel to Database.

Conclusion:

It is really easy and straight forward to genreate test data for almost any kind.

If you have any questions you can reach out our SharePoint Consulting team here.