How to Export Excel Data to SharePoint List from Excel using JSOM?

In today's digital world, companies need to handle their data efficiently. SharePoint, which is a tool from Microsoft that helps people work together, is great for keeping information organized and sharing it with others in a team or across different parts of a company. At the same time, Excel is a very common tool for looking at data and making reports. When you put SharePoint and Excel together, it can make managing data a lot easier.

There are different ways to make this happen, but one way that works well is by using something called JavaScript Object Model (JSOM) to talk to SharePoint in a programmatic way.

In this article, we're going to show you how to move data from Excel into a SharePoint list using JSOM. We'll go through each step you need to take, give you examples of code, and share some tips to make it all go smoothly.

How does JSOM interact with SharePoint?

The JavaScript Object Model (JSOM) is a part of SharePoint’s client-side object model that allows you to interact with SharePoint data using JavaScript.

Here’s how JSOM interacts with SharePoint:

Asynchronous Communication: JSOM asynchronously communicates with SharePoint. This means that the JSOM code sends a request to the SharePoint server and then continues executing without waiting for the response. Once the response is received, a callback function is executed.
Context: JSOM operates within the context of the current SharePoint site. The context includes information about the current user, the current web, and other information that is needed to interact with SharePoint.
Data Retrieval and Manipulation: JSOM provides a set of classes and methods that you can use to retrieve and manipulate SharePoint data. For example, you can use JSOM to get a reference to a SharePoint list, retrieve items from the list, and update the items.
Batch Processing: JSOM supports batch processing, which allows you to bundle multiple operations into a single request to the server. This can improve performance by reducing the number of round trips to the server.
Error Handling: JSOM provides mechanisms for error handling. You can use try-catch blocks to catch exceptions, and you can use the onFail callback function to handle errors that occur when the query is executed.

While JSOM is a powerful tool for interacting with SharePoint, it is considered a legacy option for operating data in SharePoint Online. The recommended path now is to use the SharePoint REST API or Microsoft Graph APIs either directly or through the Microsoft Graph SDKs or with the PnPjs JavaScript Library.

Steps to Export Excel Data to SharePoint List from Excel using JSOM?

Here’s a step-by-step guide on how to export Excel data to a SharePoint list using the JavaScript Object Model (JSOM):

STEP 1: Setup the SharePoint Context

The first step is to setup the SharePoint context. This context is used to interact with SharePoint.

var context = SP.ClientContext.get_current(); // Get the current context
var web = context.get_web(); // Get the current web

In this code, SP.ClientContext.get_current() gets the current context, which is essentially the current SharePoint site you are working with. context.get_web() gets the current web, which is a specific site within the SharePoint site collection.

STEP 2: Get a Reference to the List

The get_list() method is a part of SharePoint’s JavaScript Object Model (JSOM) and it is used to get a reference to a specific list in SharePoint. You can get a reference to the list using the get_list() method. You need to pass the title of the list as a parameter to this method.

var list = web.get_lists().getByTitle('Your List Title'); 
// Get the specific list

context.load(list); 
// Load the list into the context

context.executeQueryAsync(onSuccess, onFail); 
// Execute the query asynchronously

In this code, web.get_lists().getByTitle('Your List Title') gets the specific list with the title ‘Your List Title’. The context.load(list) line loads the list into the context, which means it prepares SharePoint to get this list’s data when a query is executed.

context.executeQueryAsync(onSuccess, onFail) executes the query asynchronously, meaning it sends a request to the server to get the list’s data and then proceeds to the next line of code without waiting for the server’s response.

STEP 3: Read Excel Data

You need to read the data from your Excel file. This can be done using various methods such as using the Excel JS API if your Excel file is stored in OneDrive or SharePoint, or using a library like xlsx if your Excel file is stored locally. This step involves opening the Excel file, reading its contents, and storing the data in a format that can be used to add items to the SharePoint list.

STEP 4: Add Items to the List

Once you have read the data from the Excel file and have a reference to the SharePoint list, you can add items to the list using the addItem() method.

var listItemCreationInformation = new SP.ListItemCreationInformation(); 
// Create a new list item

var listItem = list.addItem(listItemCreationInformation); 
// Add the list item to the list

listItem.set_item('Title', 'Your Item Title'); 
// Set the properties of the list item

listItem.update(); 
// Update the list item

context.load(listItem); 
// Load the list item into the context

context.executeQueryAsync(onSuccess, onFail); 
// Execute the query asynchronously

In this code, new SP.ListItemCreationInformation() creates a new list item, list.addItem(listItemCreationInformation) adds the list item to the list, listItem.set_item('Title', 'Your Item Title') sets the properties of the list item, listItem.update() updates the list item, context.load(listItem) loads the list item into the context, and context.executeQueryAsync(onSuccess, onFail) executes the query asynchronously.

Error Handling

Here are some errors that might occur when exporting Excel data to a SharePoint list using JSOM, and how to handle them:

Unsupported File Format: If you’re trying to export the list to a file format that’s not supported by SharePoint, such as .xls, you might encounter issues. To handle this, ensure that your Excel file is in a format that is supported by SharePoint, such as .xlsx.
Incorrect List or View Name: If the list or view name specified in the code does not exist or is incorrect, the code will fail. To handle this, double-check the list and view names in your code and ensure they match the ones in SharePoint.
Large Data Sets: If you’re dealing with large data sets, you might encounter issues when trying to export the data to Excel. To handle this, consider breaking down your data into smaller sets or using batch operations.
Data Type Mismatch: The issue might occur because the Generate from sample creates a schema where all the values are considered “string”. The problem is that when running the flow some of those values may be missing (null). To handle this, ensure that your data types in Excel match the data types in the SharePoint list.