Challenge: how to update Excel files on OneDrive based on dynamic data sources?
Solution: use the Microsoft Graph API to automate file updates on OneDrive directly
Part 1 of the series: “Power of Integration: Excel, OneDrive, and CI with Node.js”
The ability to programmatically access and manipulate data within Excel files on OneDrive can be invaluable. Whether you’re managing a content calendar, tracking metrics, or updating inventory data, automating these tasks can save time and reduce errors.
Microsoft Graph is a unified API endpoint that can be used to access a wide range of Microsoft 365 services. In our context, it provides an elegant way to interact with files stored on OneDrive.
Node.js is a runtime that lets you execute JavaScript on the server-side. It excels in I/O-bound operations and can be used to craft powerful tools and APIs.
Combining these two, our script serves as a bridge, fetching data from an external source and updating an Excel file stored in OneDrive.
OneDrive for business
For the script to function correctly, it is imperative to use OneDrive for Business and not the regular OneDrive. OneDrive for Business is closely integrated with SharePoint, allowing for seamless sharing and collaboration on files stored on SharePoint sites. Our script specifically targets SharePoint document libraries, making this integration crucial.
Automation
Manual data entry is not only tedious but also prone to errors. Automating this process ensures consistency and accuracy. This script can integrate OneDrive Excel files with other data sources seamlessly. With slight modifications, this tool can be set to run at specific intervals, ensuring that the data in the Excel file is always up-to-date. It can be adjusted to work with different APIs or databases.
Example usage scenarios
Automating Excel file updates on OneDrive offers a myriad of applications across different business domains. Here are a few practical scenarios where such automation can be invaluable:
Inventory Management: E-commerce platforms can utilize the script to automatically adjust stock levels within an Excel file on OneDrive in real-time following each purchase transaction.
CRM Updates: Businesses can integrate the script to extract customer data from their CRM systems and routinely update an associated Excel report stored on OneDrive, ensuring data consistency.
Metrics Tracking: Companies employing multiple marketing platforms, such as Google Ads and Facebook Ads, can consolidate data from these distinct sources. Using the script, they can amalgamate these metrics into a singular Excel report hosted on OneDrive for streamlined analysis.
Technical insights
The script’s functionality is based on several technical components:
Authentication: This is the initial step where the script communicates with Azure using the client credentials flow. This flow is designed for interactions between servers. After successful authentication, an access token is generated, and this token is used for all subsequent Microsoft Graph API requests.
Data fetching: The script currently fetches data from a mock API to serve as a demonstration. However, it’s designed in a way that allows for the integration with other data sources or APIs, if necessary.
Excel manipulation: The script uses the ExcelJS library to process and handle the data within the Excel file. It identifies specific rows based on set criteria and updates them. If no matching rows are found, it will append a new one to the document.
Handling OneDrive file locks: OneDrive has a feature where it can lock files that are currently in use. The script is designed to detect these locks. Depending on the lock status, the script decides to either overwrite the existing file or generate a new one.
Updating Excel
Let’s see the function update_excel as it encompasses the core logic of updating the Excel file.
async function update_excel(items) {
const accessToken = await getAccessToken();
const fileData = await downloadFileFromOneDrive(accessToken);
if (!fileData || fileData.length === 0) {
console.log("Error. Something went wrong in node script...");
return;
}
console.log("OpenDrive data fetched!");
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.load(fileData);
const worksheet = workbook.getWorksheet(1);
for (const elem of items) {
const row = findRowByID(worksheet, elem.id);
if (row) {
console.log("..updating row");
setCellValueAndColor(row, 1, elem.id);
// ... Other cell updates ...
} else {
console.log("..adding row");
// ... Logic for adding a new row ...
}
}
const buffer = await workbook.xlsx.writeBuffer();
await uploadFileToOneDrive(accessToken, buffer);
}
How update_excel Works:
- Fetching access token: The function starts by obtaining an access token from Azure using the getAccessToken() function. This token will be used to authorize our interactions with the Excel file stored on OneDrive.
- Downloading the Excel file: With a valid access token in hand, the function calls downloadFileFromOneDrive(accessToken) to fetch the binary data of the Excel file from OneDrive.
- Loading the Excel data: The binary data of the Excel file is then loaded into an ExcelJS Workbook object. This allows us to manipulate the contents of the Excel file programmatically.
- Iterating over items: The function receives an array of items (which could be data from an API or another source). It iterates over each item and tries to locate a corresponding row in the Excel sheet using the findRowByID function.
- Updating existing rows: If a matching row is found in the Excel sheet (i.e., a row with the same ID as the current item), the script updates the cells of that row with the data from the current item. The setCellValueAndColor function is used to set the value of a cell and give it a specific background color.
- Adding new rows: If no matching row is found in the Excel sheet for the current item, the script appends a new row to the sheet with the item’s data.
- Uploading the updated Excel file: After processing all the items, the function writes the updated data back into an Excel file (in memory) using workbook.xlsx.writeBuffer(). This updated Excel file is then uploaded back to OneDrive using the uploadFileToOneDrive function.
uploadFileToOneDrive
The uploadFileToOneDrive method is integral to the functionality of our script, as it ensures that the updated Excel data is saved back to OneDrive. Using the HTTP PUT method, the function attempts to upload the binary data (Excel file contents) to the designated OneDrive location.
async function uploadFileToOneDrive(accessToken, fileData) {
const headers = {
'Authorization': `Bearer ${accessToken}`,
'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Prefer': 'bypass-shared-lock'
};
const originalEndpoint = `${GRAPH_BASE_URL}/sites/${SITE_ID}/drive/root:/${FILE_NAME}:/content`;
try {
await axios.put(originalEndpoint, fileData, { headers: headers });
} catch (error) {
if (error.response && error.response.data && error.response.data.error && error.response.data.error.message.includes("locked")) {
console.log("Current file is locked. Deleting and creating a new one.");
const deleteEndpoint = `${GRAPH_BASE_URL}/sites/${SITE_ID}/drive/root:/${FILE_NAME}`;
try {
await axios.delete(deleteEndpoint, { headers: headers });
} catch (deleteError) {
console.error('Error deleting the locked file:', deleteError.response.data);
return;
}
try {
await axios.put(originalEndpoint, fileData, { headers: headers });
} catch (uploadError) {
console.error('Error creating new file:', uploadError.response.data);
return;
}
}
}
}
Output
Example script output:
λ node src\index.js
OpenDrive data fetched!
..updating row
..updating row
..updating row
..updating row
..updating row
Current file is locked. Deleting and creating a new one.
Successfully saved to OneDrive
Azure’s role
Azure is Microsoft’s cloud computing service. Within Azure, it’s possible to set up applications with specific permissions, making it a cornerstone of our setup.
To start with, when setting up the Azure application, ensure it’s in “Application Permissions” mode. The reason? Our script demands one key permission:
Sites.Selected
But why Sites.Selected and not something broader? The beauty of Sites.Selected lies in its precision. Instead of an all-encompassing permission that indiscriminately grants access to all SharePoint sites, Sites.Selected adopts a more security-conscious approach. It only permits access to certain specified sites or document libraries. This specificity aligns with the principle of least privilege, ensuring that applications access only what they absolutely need.
SharePoint permissions
Azure’s permissions, although crucial, are just one part of the puzzle. The other equally vital piece is SharePoint. Remember, our Excel files reside on SharePoint document libraries within OneDrive for Business.
SharePoint site with document library needs to be in sync with our script’s intentions. This means giving the script “write” permissions. Specifically, the application_id must have the rights to make modifications.
Granting these permissions can be achieved through:
${GRAPH_BASE_URL}/sites/${siteId}/permissions
A SharePoint administrator would typically handle this, ensuring that the script can access and modify files without a hitch.
Handling file locks
OneDrive files can be locked if they are in use or due to other reasons. If the file is locked, a specific error message is returned. The function checks for this “locked” error message. If detected, it takes an additional step:
– deletes the locked file using its endpoint.
– attempts to re-upload the new Excel file.
Collaborative Excel on OneDrive
Leveraging the capabilities of OneDrive for Business, Excel files can be effortlessly shared with team members, fostering a collaborative environment. While certain columns remain open for edits and updates by the team, other data gets populated automatically, ensuring a dynamic yet controlled data environment.
The Node.js script plays a pivotal role here. Set to run at 24-hour intervals, it fetches the necessary data, ensuring that your Excel file is always updated with the most recent information. This provides a dual advantage: team members can make necessary manual adjustments while being confident that other data points in the Excel are current and automated.
The outcome? A continuously evolving, real-time data sheet where human insights and automated data coexist, driving informed decision-making.
Conclusion
The ability to programmatically update Excel files on OneDrive can greatly enhance productivity and ensure data consistency across platforms. While our tool is a starting point, the principles can be applied to various scenarios, making it a valuable asset for any developer or business leveraging Microsoft 365 and OneDrive.
Source code
For those keen to dive deeper into our solution, we’ve made our source code available on GitHub. The repository is complemented by a comprehensive README, detailing the entire setup process. Github repo: https://github.com/createit-dev/311-excel-onedrive-autoupdate
Power of integration
This is the first part of the series “Power of Integration: Excel, OneDrive, and CI with Node.js”:
1. Updating Excel Files on OneDrive with Node.js and Microsoft Graph: Dive into the core technique of programmatically accessing and updating Excel files on OneDrive.
2. Configuring Azure Application and SharePoint Permissions: This installment offers a detailed guide on configuring Azure and SharePoint. With the appropriate permissions, your script will operate seamlessly and securely.
3. Integrating Node.js with GitLab CI for Daily Data Refreshes: Learn how the fusion of Node.js with GitLab Continuous Integration (CI) facilitates daily data updates, ensuring your Excel files remain up-to-date.
Are you looking for talented developers? Contact us!
Comments
0 response