logo Windmill Hub
Login
Back to Microsoft Excel integration
0

Find Row

by
Published today

Find the rows of a worksheet whose cell in `column` (a letter, e.g. `B`) equals `value`, compared as text. Returns `[{ row, values }]` with the 1-based row number and that row's cells across the used range; empty when nothing matches. `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 5 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 
export type DynSelect_worksheet = string
66
67 
export async function worksheet(
68 
  auth: RT.MsExcel,
69 
  workbook: string,
70 
  drive_id: string | undefined
71 
) {
72 
  const wb = workbookUrl(workbook, drive_id)
73 
  const session = await workbookSession(auth, wb)
74 
  const response = await fetch(`${wb}/worksheets?$select=name`, {
75 
    headers: {
76 
      Authorization: `Bearer ${auth.token}`,
77 
      Accept: "application/json",
78 
      "workbook-session-id": session,
79 
    },
80 
  })
81 
  if (!response.ok) {
82 
    throw new Error(`${response.status} ${await response.text()}`)
83 
  }
84 
  const { value } = (await response.json()) as { value: { name: string }[] }
85 
  return value.map((w) => ({ value: w.name, label: w.name }))
86 
}
87
88 
// A -> 1, AA -> 27
89 
function columnNumber(letters: string) {
90 
  return [...letters.toUpperCase()].reduce(
91 
    (n, c) => n * 26 + c.charCodeAt(0) - 64,
92 
    0
93 
  )
94 
}
95
96 
/**
97 
 * Find Row
98 
 * Find the rows of a worksheet whose cell in `column` (a letter, e.g. `B`) equals `value`, compared as text. Returns `[{ row, values }]` with the 1-based row number and that row's cells across the used range; empty when nothing matches. `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.
99 
 */
100 
export async function main(
101 
  auth: RT.MsExcel,
102 
  workbook: DynSelect_workbook,
103 
  worksheet: DynSelect_worksheet,
104 
  column: string,
105 
  value: string,
106 
  drive_id: string | undefined
107 
) {
108 
  const wb = workbookUrl(workbook, drive_id)
109 
  const session = await workbookSession(auth, wb)
110 
  const response = await fetch(
111 
    `${wb}/worksheets/${encodeURIComponent(worksheet)}/usedRange(valuesOnly=true)?$select=address,values`,
112 
    {
113 
      headers: {
114 
        Authorization: `Bearer ${auth.token}`,
115 
        Accept: "application/json",
116 
        "workbook-session-id": session,
117 
      },
118 
    }
119 
  )
120
121 
  if (!response.ok) {
122 
    throw new Error(`${response.status} ${await response.text()}`)
123 
  }
124
125 
  // address is like Sheet1!B2:D9: the used range need not start at A1
126 
  const used = (await response.json()) as { address: string; values: any[][] }
127 
  const [, firstColumn, firstRow] = used.address
128 
    .split("!")
129 
    .pop()!
130 
    .match(/^([A-Z]+)(\d+)/)!
131 
  const index = columnNumber(column) - columnNumber(firstColumn)
132 
  return used.values
133 
    .map((values, i) => ({ row: Number(firstRow) + i, values }))
134 
    .filter((r) => String(r.values[index] ?? "") === value)
135 
}
136