logo Windmill Hub
Login
Back to Microsoft Excel integration
0

List Worksheets

by
Published today

List the worksheets of an Excel workbook stored in OneDrive or SharePoint. `workbook` is the .xlsx file's id, or its path from the drive root (e.g. `/Reports/budget.xlsx`). Requires Files.ReadWrite (a connection default); a workbook in a drive that is not your own needs Files.ReadWrite.All.

Script ms_excel Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 4 hours ago
All edits
Permalink
1 
//native
2
3 
const graph = "https://graph.microsoft.com/v1.0"
4
5 
// A workbook is addressed by its drive item id, or by a path from the drive root starting with "/"
6 
function workbookUrl(workbook: string, drive_id: string | undefined) {
7 
  const drive =
8 
    drive_id != null && drive_id !== ""
9 
      ? `${graph}/drives/${encodeURIComponent(drive_id)}`
10 
      : `${graph}/me/drive`
11 
  const item = workbook.startsWith("/")
12 
    ? `root:${workbook.split("/").map(encodeURIComponent).join("/")}:`
13 
    : `items/${encodeURIComponent(workbook)}`
14 
  return `${drive}/${item}/workbook`
15 
}
16
17 
// Requests without a session can read a copy that lags a minute behind the last write;
18 
// a new session always opens the current workbook
19 
async function workbookSession(auth: RT.MsExcel, wb: string) {
20 
  const response = await fetch(`${wb}/createSession`, {
21 
    method: "POST",
22 
    headers: {
23 
      Authorization: `Bearer ${auth.token}`,
24 
      "Content-Type": "application/json",
25 
      Accept: "application/json",
26 
    },
27 
    body: JSON.stringify({ persistChanges: true }),
28 
  })
29 
  if (!response.ok) {
30 
    throw new Error(`${response.status} ${await response.text()}`)
31 
  }
32 
  return ((await response.json()) as { id: string }).id
33 
}
34
35 
export type DynSelect_workbook = string
36
37 
export async function workbook(
38 
  auth: RT.MsExcel,
39 
  drive_id: string | undefined
40 
) {
41 
  const drive =
42 
    drive_id != null && drive_id !== ""
43 
      ? `${graph}/drives/${encodeURIComponent(drive_id)}`
44 
      : `${graph}/me/drive`
45 
  const response = await fetch(
46 
    `${drive}/root/search(q='.xlsx')?$select=id,name&$top=200`,
47 
    {
48 
      headers: {
49 
        Authorization: `Bearer ${auth.token}`,
50 
        Accept: "application/json",
51 
      },
52 
    }
53 
  )
54 
  if (!response.ok) {
55 
    throw new Error(`${response.status} ${await response.text()}`)
56 
  }
57 
  const { value } = (await response.json()) as {
58 
    value: { id: string; name: string }[]
59 
  }
60 
  return value
61 
    .filter((f) => f.name.toLowerCase().endsWith(".xlsx"))
62 
    .map((f) => ({ value: f.id, label: f.name }))
63 
}
64
65 
/**
66 
 * List Worksheets
67 
 * List the worksheets of an Excel workbook stored in OneDrive or SharePoint. `workbook` is the .xlsx file's id, or its path from the drive root (e.g. `/Reports/budget.xlsx`). Requires Files.ReadWrite (a connection default); a workbook in a drive that is not your own needs Files.ReadWrite.All.
68 
 */
69 
export async function main(
70 
  auth: RT.MsExcel,
71 
  workbook: DynSelect_workbook,
72 
  drive_id: string | undefined
73 
) {
74 
  const wb = workbookUrl(workbook, drive_id)
75 
  const session = await workbookSession(auth, wb)
76 
  const response = await fetch(`${wb}/worksheets`, {
77 
    headers: {
78 
      Authorization: `Bearer ${auth.token}`,
79 
      Accept: "application/json",
80 
      "workbook-session-id": session,
81 
    },
82 
  })
83
84 
  if (!response.ok) {
85 
    throw new Error(`${response.status} ${await response.text()}`)
86 
  }
87
88 
  return await response.json()
89 
}
90