Edits history of script submission #23503 for ' Find Row (ms_excel)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //native
  
  const graph = "https://graph.microsoft.com/v1.0"
  
  // A workbook is addressed by its drive item id, or by a path from the drive root starting with "/"
  function workbookUrl(workbook: string, drive_id: string | undefined) {
    const drive =
      drive_id != null && drive_id !== ""
        ? `${graph}/drives/${encodeURIComponent(drive_id)}`
        : `${graph}/me/drive`
    const item = workbook.startsWith("/")
      ? `root:${workbook.split("/").map(encodeURIComponent).join("/")}:`
      : `items/${encodeURIComponent(workbook)}`
    return `${drive}/${item}/workbook`
  }
  
  // Requests without a session can read a copy that lags a minute behind the last write;
  // a new session always opens the current workbook
  async function workbookSession(auth: RT.MsExcel, wb: string) {
    const response = await fetch(`${wb}/createSession`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${auth.token}`,
        "Content-Type": "application/json",
        Accept: "application/json",
      },
      body: JSON.stringify({ persistChanges: true }),
    })
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    return ((await response.json()) as { id: string }).id
  }
  
  export type DynSelect_workbook = string
  
  export async function workbook(
    auth: RT.MsExcel,
    drive_id: string | undefined
  ) {
    const drive =
      drive_id != null && drive_id !== ""
        ? `${graph}/drives/${encodeURIComponent(drive_id)}`
        : `${graph}/me/drive`
    const response = await fetch(
      `${drive}/root/search(q='.xlsx')?$select=id,name&$top=200`,
      {
        headers: {
          Authorization: `Bearer ${auth.token}`,
          Accept: "application/json",
        },
      }
    )
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    const { value } = (await response.json()) as {
      value: { id: string; name: string }[]
    }
    return value
      .filter((f) => f.name.toLowerCase().endsWith(".xlsx"))
      .map((f) => ({ value: f.id, label: f.name }))
  }
  
  export type DynSelect_worksheet = string
  
  export async function worksheet(
    auth: RT.MsExcel,
    workbook: string,
    drive_id: string | undefined
  ) {
    const wb = workbookUrl(workbook, drive_id)
    const session = await workbookSession(auth, wb)
    const response = await fetch(`${wb}/worksheets?$select=name`, {
      headers: {
        Authorization: `Bearer ${auth.token}`,
        Accept: "application/json",
        "workbook-session-id": session,
      },
    })
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    const { value } = (await response.json()) as { value: { name: string }[] }
    return value.map((w) => ({ value: w.name, label: w.name }))
  }
  
  // A -> 1, AA -> 27
  function columnNumber(letters: string) {
    return [...letters.toUpperCase()].reduce(
      (n, c) => n * 26 + c.charCodeAt(0) - 64,
      0
    )
  }
  
  /**
   * Find Row
   * 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.
   */
  export async function main(
    auth: RT.MsExcel,
    workbook: DynSelect_workbook,
    worksheet: DynSelect_worksheet,
    column: string,
    value: string,
    drive_id: string | undefined
  ) {
    const wb = workbookUrl(workbook, drive_id)
    const session = await workbookSession(auth, wb)
    const response = await fetch(
      `${wb}/worksheets/${encodeURIComponent(worksheet)}/usedRange(valuesOnly=true)?$select=address,values`,
      {
        headers: {
          Authorization: `Bearer ${auth.token}`,
          Accept: "application/json",
          "workbook-session-id": session,
        },
      }
    )
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    // address is like Sheet1!B2:D9: the used range need not start at A1
    const used = (await response.json()) as { address: string; values: any[][] }
    const [, firstColumn, firstRow] = used.address
      .split("!")
      .pop()!
      .match(/^([A-Z]+)(\d+)/)!
    const index = columnNumber(column) - columnNumber(firstColumn)
    return used.values
      .map((values, i) => ({ row: Number(firstRow) + i, values }))
      .filter((r) => String(r.values[index] ?? "") === value)
  }
  

  Submitted by hugo989 5 hours ago