Edits history of script submission #23036 for ' Find Rows (gsheets)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //native
  
  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,
    }))
  }
  
  /**
   * Find Rows
   * Find the rows whose value in a column equals the given value (exact match on the displayed value). Row 1 is read as the header row; `column` is a header name or a column letter. Returns each match's 1-based `row_number` and its values keyed by header.
   */
  export async function main(
    auth: RT.Gsheets,
    spreadsheet_id: string,
    sheet_name: DynSelect_sheet_name,
    column: string,
    value: string
  ) {
    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()}`)
    }
  
    const { values = [] } = (await response.json()) as { values?: string[][] }
    const [headers = [], ...rows] = values
  
    // Header name first, then column letter (A, B, …, AA).
    let index = headers.indexOf(column)
    if (index === -1 && /^[A-Za-z]{1,3}$/.test(column)) {
      index =
        [...column.toUpperCase()].reduce(
          (n, c) => n * 26 + c.charCodeAt(0) - 64,
          0
        ) - 1
    }
    if (index === -1) {
      throw new Error(
        `Column "${column}" not found in header row: ${headers.join(", ")}`
      )
    }
  
    return rows.flatMap((row, i) =>
      (row[index] ?? "") === value
        ? [
            {
              row_number: i + 2,
              values: Object.fromEntries(
                headers.map((h, j) => [h, row[j] ?? ""])
              ),
            },
          ]
        : []
    )
  }
  

  Submitted by hugo989 1 hour ago