logo Windmill Hub
Login
Back to Airtable integration
0

Search Records

by
Published today

Find records with an Airtable formula, or by matching a value against one field.

Script airtable Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 2 hours ago
All edits
Permalink
1 
//native
2
3 
export type DynSelect_base_id = string
4 
export type DynSelect_table_id = string
5 
export type DynSelect_search_field = string
6
7 
// Dropdown of the bases the token can access.
8 
export async function base_id(auth: RT.Airtable) {
9 
  const options: { value: string; label: string }[] = []
10 
  let offset: string | undefined = undefined
11 
  do {
12 
    const url = new URL("https://api.airtable.com/v0/meta/bases")
13 
    if (offset) {
14 
      url.searchParams.append("offset", offset)
15 
    }
16 
    const response = await fetch(url, {
17 
      headers: {
18 
        Authorization: `Bearer ${auth.apiKey}`,
19 
        Accept: "application/json",
20 
      },
21 
    })
22 
    if (!response.ok) {
23 
      throw new Error(`${response.status} ${await response.text()}`)
24 
    }
25 
    const page = (await response.json()) as {
26 
      bases: { id: string; name: string }[]
27 
      offset?: string
28 
    }
29 
    options.push(...page.bases.map((b) => ({ value: b.id, label: b.name })))
30 
    offset = page.offset
31 
  } while (offset)
32 
  return options
33 
}
34
35 
async function listTables(auth: RT.Airtable, base_id: string) {
36 
  if (!base_id) {
37 
    return []
38 
  }
39 
  const response = await fetch(
40 
    `https://api.airtable.com/v0/meta/bases/${base_id}/tables`,
41 
    {
42 
      headers: {
43 
        Authorization: `Bearer ${auth.apiKey}`,
44 
        Accept: "application/json",
45 
      },
46 
    }
47 
  )
48 
  if (!response.ok) {
49 
    throw new Error(`${response.status} ${await response.text()}`)
50 
  }
51 
  const { tables } = (await response.json()) as {
52 
    tables: {
53 
      id: string
54 
      name: string
55 
      fields: { id: string; name: string; type: string }[]
56 
      views: { id: string; name: string }[]
57 
    }[]
58 
  }
59 
  return tables
60 
}
61
62 
// Dropdown of the tables in the chosen base.
63 
export async function table_id(auth: RT.Airtable, base_id: DynSelect_base_id) {
64 
  return (await listTables(auth, base_id)).map((t) => ({
65 
    value: t.id,
66 
    label: t.name,
67 
  }))
68 
}
69
70 
// Dropdown of the fields in the chosen table.
71 
export async function search_field(
72 
  auth: RT.Airtable,
73 
  base_id: DynSelect_base_id,
74 
  table_id: DynSelect_table_id
75 
) {
76 
  const table = (await listTables(auth, base_id)).find((t) => t.id === table_id)
77 
  return (table?.fields ?? []).map((f) => ({
78 
    value: f.name,
79 
    label: `${f.name} (${f.type})`,
80 
  }))
81 
}
82
83 
/**
84 
 * Search Records
85 
 * Find records with an Airtable formula, or by matching search_value against search_field (substring match on text fields, exact match on number and checkbox fields). The formula wins when both are given. Returns every matching record. The field + value form reads the table schema, so it requires the schema.bases:read scope.
86 
 */
87 
export async function main(
88 
  auth: RT.Airtable,
89 
  base_id: DynSelect_base_id,
90 
  table_id: DynSelect_table_id,
91 
  search_formula: string | undefined,
92 
  search_field: DynSelect_search_field | undefined,
93 
  search_value: string | undefined,
94 
  return_fields_by_field_id: boolean | undefined
95 
) {
96 
  let formula = search_formula
97 
  if (formula == null || formula === "") {
98 
    if (!search_field || search_value == null || search_value === "") {
99 
      throw new Error(
100 
        "Provide either search_formula, or both search_field and search_value."
101 
      )
102 
    }
103 
    const table = (await listTables(auth, base_id)).find(
104 
      (t) => t.id === table_id || t.name === table_id
105 
    )
106 
    const type = table?.fields.find((f) => f.name === search_field)?.type
107 
    const quoted = `"${search_value.replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`
108 
    if (type === "checkbox") {
109 
      formula = `{${search_field}} = ${search_value.toLowerCase() === "true" ? 1 : 0}`
110 
    } else if (
111 
      [
112 
        "number",
113 
        "percent",
114 
        "currency",
115 
        "rating",
116 
        "duration",
117 
        "count",
118 
        "autoNumber",
119 
      ].includes(type ?? "")
120 
    ) {
121 
      if (!Number.isFinite(Number(search_value))) {
122 
        throw new Error(
123 
          `"${search_value}" is not a number (field "${search_field}" is ${type}).`
124 
        )
125 
      }
126 
      formula = `{${search_field}} = ${Number(search_value)}`
127 
    } else {
128 
      formula = `FIND(${quoted}, {${search_field}} & "")`
129 
    }
130 
  }
131
132 
  const records: any[] = []
133 
  let offset: string | undefined = undefined
134
135 
  do {
136 
    const url = new URL(`https://api.airtable.com/v0/${base_id}/${table_id}`)
137 
    url.searchParams.append("filterByFormula", formula)
138 
    if (return_fields_by_field_id === true) {
139 
      url.searchParams.append("returnFieldsByFieldId", "true")
140 
    }
141 
    if (offset) {
142 
      url.searchParams.append("offset", offset)
143 
    }
144
145 
    const response = await fetch(url, {
146 
      headers: {
147 
        Authorization: `Bearer ${auth.apiKey}`,
148 
        Accept: "application/json",
149 
      },
150 
    })
151 
    if (!response.ok) {
152 
      throw new Error(`${response.status} ${await response.text()}`)
153 
    }
154
155 
    const page = await response.json()
156 
    records.push(...(page.records ?? []))
157 
    offset = page.offset
158 
  } while (offset)
159
160 
  return { records }
161 
}
162