Add Row

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

}

// 1 -> A, 27 -> AA

function columnLetter(n: number): string {

  return n <= 0

    ? ""

    : columnLetter(Math.floor((n - 1) / 26)) +

        String.fromCharCode(65 + ((n - 1) % 26))

}

/**

 * Add Row

 * Append one row below the last used row of a worksheet, starting in column A. `values` is the row's cells in order, e.g. `["Ada", 36, "=B2*2"]`. For a worksheet formatted as a table, Add Table Rows is the safer choice. `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,

  values: any[],

  drive_id: string | undefined

) {

  const wb = workbookUrl(workbook, drive_id)

  const session = await workbookSession(auth, wb)

  if (!values?.length) throw new Error("values needs at least one cell")

  const usedResponse = await fetch(

    `${wb}/worksheets/${encodeURIComponent(worksheet)}/usedRange(valuesOnly=true)?$select=address,values`,

    {

      headers: {

        Authorization: `Bearer ${auth.token}`,

        Accept: "application/json",

        "workbook-session-id": session,

      },

    }

  )

  if (!usedResponse.ok) {

    throw new Error(`${usedResponse.status} ${await usedResponse.text()}`)

  }

  // address is like Sheet1!A1:C5, or Sheet1!A1 for a single cell

  const used = (await usedResponse.json()) as {

    address: string

    values: any[][]

  }

  const isEmpty =

    used.values.length === 1 &&

    used.values[0].length === 1 &&

    used.values[0][0] === ""

  const row = isEmpty ? 1 : Number(used.address.match(/(\d+)$/)![1]) + 1

  const address = `A${row}:${columnLetter(values.length)}${row}`

  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: [values] }),

    }

  )

  if (!response.ok) {

    throw new Error(`${response.status} ${await response.text()}`)

  }

  return await response.json()

}