Update Range

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

}