Get Table Rows

//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 }))

}

/**

 * Get Table Rows

 * List the data rows of a table, each with its 0-based `index` and `values`; the header row is not included (see Get Table). Page with `top` and `skip`. `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,

  top: number = 100,

  skip: number = 0,

  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?$top=${top}&$skip=${skip}`,

    {

      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()

}