Edits history of script submission #23038 for ' New Row (gsheets)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //native
  
  import * as wmill from "windmill-client"
  
  export type DynSelect_sheet_name = string
  
  // Dropdown of the spreadsheet's worksheet (tab) names.
  export async function sheet_name(auth: RT.Gsheets, spreadsheet_id: string) {
    const response = await fetch(
      `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties.title`,
      {
        headers: {
          Authorization: `Bearer ${auth.token}`,
          Accept: "application/json",
        },
      }
    )
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    const { sheets } = (await response.json()) as {
      sheets: { properties: { title: string } }[]
    }
    return sheets.map((s) => ({
      value: s.properties.title,
      label: s.properties.title,
    }))
  }
  
  /**
   * New Row
   * Polls for rows appended below the last row seen on the previous poll (polling because Google has no row-level push: Drive's push notification for a spreadsheet only says the file changed). Emits each new row with its 1-based `row_number` and values keyed by the header row (row 1). The first run records the current row count and emits nothing.
   */
  export async function main(
    auth: RT.Gsheets,
    spreadsheet_id: string,
    sheet_name: DynSelect_sheet_name
  ) {
    // ponytail: reads the whole sheet each poll; fine up to tens of thousands of rows.
    const range = encodeURIComponent(`'${sheet_name.replaceAll("'", "''")}'`)
    const response = await fetch(
      `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/${range}`,
      {
        headers: {
          Authorization: `Bearer ${auth.token}`,
          Accept: "application/json",
        },
      }
    )
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    // `values` stops at the last non-empty row, so its length is the row count.
    const { values = [] } = (await response.json()) as { values?: string[][] }
    const seen: number | undefined = await wmill.getState()
    await wmill.setState(values.length)
  
    // First run, or rows were deleted: re-baseline without emitting.
    if (seen === undefined || seen === null || values.length <= seen) return []
  
    const headers = values[0] ?? []
    return values.slice(Math.max(seen, 1)).map((row, i) => ({
      row_number: Math.max(seen, 1) + i + 1,
      values: Object.fromEntries(headers.map((h, j) => [h, row[j] ?? ""])),
    }))
  }
  

  Submitted by hugo989 1 hour ago