Edits history of script submission #23509 for ' Update 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 }))
  }
  
  /**
   * Update Range
   * Write cells in a worksheet. `address` is A1 notation and `values` a 2D array of the same shape, one inner array per row (`[[1, "a"], [2, "b"]]` for `A1:B2`; `[["x"]]` for one cell). A string starting with `=` is written as a formula; `null` leaves a cell untouched. `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,
    values: any[][],
    drive_id: string | undefined
  ) {
    const wb = workbookUrl(workbook, drive_id)
    const session = await workbookSession(auth, wb)
    const response = await fetch(
      `${wb}/worksheets/${encodeURIComponent(worksheet)}/range(address='${address}')`,
      {
        method: "PATCH",
        headers: {
          Authorization: `Bearer ${auth.token}`,
          "Content-Type": "application/json",
          Accept: "application/json",
          "workbook-session-id": session,
        },
        body: JSON.stringify({ values }),
      }
    )
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    return await response.json()
  }
  

  Submitted by hugo989 5 hours ago