Edits history of script submission #23504 for ' Get Range (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 }))
  }
  
  /**
   * Get Range
   * Read cells from a worksheet: `values` (a row-by-row 2D array), `formulas`, `text` and the range's address and size. `address` is A1 notation (`A1:D20`, `B:B` for a column, `C7` for a cell); empty returns the used range, the smallest rectangle holding every non-empty cell. `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,
    address: string | undefined,
    select: string | undefined,
    drive_id: string | undefined
  ) {
    const wb = workbookUrl(workbook, drive_id)
    const session = await workbookSession(auth, wb)
    const url = new URL(
      address != null && address !== ""
        ? `${wb}/worksheets/${encodeURIComponent(worksheet)}/range(address='${address}')`
        : `${wb}/worksheets/${encodeURIComponent(worksheet)}/usedRange(valuesOnly=true)`
    )
    if (select != null && select !== "") {
      url.searchParams.append("$select", select)
    }
  
    const response = await fetch(url, {
      headers: {
        Authorization: `Bearer ${auth.token}`,
        Accept: "application/json",
        "workbook-session-id": session,
      },
    })
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    return await response.json()
  }
  

  Submitted by hugo989 5 hours ago