Edits history of script submission #23511 for ' New Row Added (ms_excel)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

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

  Submitted by hugo989 5 hours ago