Edits history of script submission #23510 for ' Update Table 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_table = string
  
  export async function table(
    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}/tables?$select=id,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: { id: string; name: string }[]
    }
    return value.map((t) => ({ value: t.name, label: t.name }))
  }
  
  /**
   * Update Table Row
   * Replace the cells of one table row, addressed by its 0-based `row_index` among the data rows (the `index` returned by Get Table Rows). `values` is the row's cells in column order; `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,
    table: DynSelect_table,
    row_index: number,
    values: any[],
    drive_id: string | undefined
  ) {
    const wb = workbookUrl(workbook, drive_id)
    const session = await workbookSession(auth, wb)
    const response = await fetch(
      `${wb}/tables/${encodeURIComponent(table)}/rows/itemAt(index=${row_index})`,
      {
        method: "PATCH",
        headers: {
          Authorization: `Bearer ${auth.token}`,
          "Content-Type": "application/json",
          Accept: "application/json",
          "workbook-session-id": session,
        },
        body: JSON.stringify({ values: [values] }),
      }
    )
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    return await response.json()
  }
  

  Submitted by hugo989 5 hours ago