New Row Added

//native

import * as wmill from "windmill-client"

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

}

/**

 * New Row Added

 * Emits the rows added at the bottom of a worksheet since the last poll, as `{ row, values }`. The first run records the current row count and emits nothing. It compares row counts, so a row inserted in the middle or a deletion paired with an addition is not seen. Polls because Microsoft Graph has no change notification for cell contents, only for the file as a whole. `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,

  drive_id: string | undefined

) {

  const wb = workbookUrl(workbook, drive_id)

  const session = await workbookSession(auth, wb)

  const previous: number | undefined = await wmill.getState()

  const response = 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 (!response.ok) {

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

  }

  // address is like Sheet1!A1:C5; the last number is the last used row

  const used = (await response.json()) as { address: string; values: any[][] }

  const isEmpty =

    used.values.length === 1 &&

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

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

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

  await wmill.setState(lastRow)

  // First run, or rows were removed: only record the count

  if (previous == null || lastRow <= previous) return []

  const firstRow = lastRow - used.values.length + 1

  return used.values

    .map((values, i) => ({ row: firstRow + i, values }))

    .filter((r) => r.row > previous)

}