connect to SharePoint library using Azure Function with TypeScript

Question

Hi,

I am trying to connect to SharePoint online document library using Azure Function to read excel file using TypeScript.

do you have any TypeScript example similar to this? Any guidance would be appreciated.

Thank you,

Siva

Sangeetha S · Answer

import { AzureFunction, Context, HttpRequest } from "@azure/functions"; import { sp } from "@pnp/sp"; import { NodeFetchClient } from "@pnp/nodejs-authentication-adapter"; import * as ExcelJS from "exceljs"; const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise { const siteUrl = process.env.SharePointSiteUrl; const clientId = process.env.ClientId; const clientSecret = process.env.ClientSecret; sp.setup({ sp: { fetchClientFactory: () => new NodeFetchClient(clientId, clientSecret), baseUrl: siteUrl, }, }); try { const file = await sp.web.getFileByServerRelativeUrl("/sites/yoursite/Shared Documents/yourfile.xlsx").getBuffer(); const workbook = new ExcelJS.Workbook(); await workbook.xlsx.load(file); const worksheet = workbook.getWorksheet(1); const data = worksheet.getRow(1).values; context.res = { status: 200, body: data, }; } catch (error) { context.res = { status: 500, body: error.message, }; } }; export default httpTrigger;

Abhijeet Jadhav · Answer

Please check below snippet for guidance: import { AzureFunction, Context, HttpRequest } from "@azure/functions"; import axios from "axios"; import * as XLSX from "xlsx"; // Configuration - Replace with your own values const tenantId = "YOUR_TENANT_ID"; const clientId = "YOUR_CLIENT_ID"; const clientSecret = "YOUR_CLIENT_SECRET"; const siteId = "YOUR_SHAREPOINT_SITE_ID"; // e.g., "contoso.sharepoint.com,xxxx,yyyy" const driveId = "YOUR_DRIVE_ID"; // Document library ID const filePath = "PATH/TO/YOUR/EXCEL_FILE.xlsx"; // Relative path in the document library // HTTP Trigger Azure Function const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise { try { // Step 1: Get Access Token const tokenResponse = await axios.post( `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`, `grant_type=client_credentials&client_id=${clientId}&client_secret=${clientSecret}&scope=https://graph.microsoft.com/.default`, { headers: { "Content-Type": "application/x-www-form-urlencoded" }, } ); const accessToken = tokenResponse.data.access_token; // Step 2: Download Excel File from SharePoint const fileUrl = `https://graph.microsoft.com/v1.0/sites/${siteId}/drives/${driveId}/root:/${filePath}:/content`; const fileResponse = await axios.get(fileUrl, { headers: { Authorization: `Bearer ${accessToken}` }, responseType: "arraybuffer", // Get file as binary data }); // Step 3: Parse Excel File const buffer = fileResponse.data; const workbook = XLSX.read(buffer, { type: "buffer" }); const sheetName = workbook.SheetNames[0]; // Assuming first sheet const worksheet = workbook.Sheets[sheetName]; const jsonData = XLSX.utils.sheet_to_json(worksheet); // Step 4: Return or process the data context.res = { status: 200, body: JSON.stringify(jsonData), }; } catch (error) { context.log.error("Error occurred:", error); context.res = { status: 500, body: `Error: ${error.message}`, }; } }; export default httpTrigger;

connect to SharePoint library using Azure Function with TypeScript

Answers (2)