logo Windmill Hub
Login
Back to Airtable integration
0

Upsert Records

by
Published 4 days ago

Create or update records by matching on one to three fields, sent in batches of 10.

Script airtable Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 5 days ago
All edits
Permalink
1 
//native
2
3 
export type DynSelect_base_id = string
4 
export type DynSelect_table_id = string
5 
export type DynMultiselect_fields_to_merge_on = 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 
// Checklist of the fields in the chosen table.
71 
export async function fields_to_merge_on(
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.id,
79 
    label: `${f.name} (${f.type})`,
80 
  }))
81 
}
82
83 
/**
84 
 * Upsert Records
85 
 * Create or update records by matching on one to three fields: a record whose merge-field values match an existing record updates it, otherwise a new record is created. Every record must include the merge fields, which cannot be computed fields. Sent in batches of 10 (the API maximum).
86 
 */
87 
export async function main(
88 
  auth: RT.Airtable,
89 
  base_id: DynSelect_base_id,
90 
  table_id: DynSelect_table_id,
91 
  records: { [key: string]: any }[],
92 
  fields_to_merge_on: DynMultiselect_fields_to_merge_on,
93 
  typecast: boolean | undefined,
94 
  return_fields_by_field_id: boolean | undefined
95 
) {
96 
  const result = {
97 
    records: [] as any[],
98 
    createdRecords: [] as string[],
99 
    updatedRecords: [] as string[],
100 
  }
101 
  // The API accepts at most 10 records per request.
102 
  for (let i = 0; i < records.length; i += 10) {
103 
    const response = await fetch(
104 
      `https://api.airtable.com/v0/${base_id}/${table_id}`,
105 
      {
106 
        method: "PATCH",
107 
        headers: {
108 
          Authorization: `Bearer ${auth.apiKey}`,
109 
          "Content-Type": "application/json",
110 
          Accept: "application/json",
111 
        },
112 
        body: JSON.stringify({
113 
          performUpsert: { fieldsToMergeOn: fields_to_merge_on },
114 
          records: records.slice(i, i + 10).map((fields) => ({ fields })),
115 
          typecast: typecast === true,
116 
          returnFieldsByFieldId: return_fields_by_field_id === true,
117 
        }),
118 
      }
119 
    )
120 
    if (!response.ok) {
121 
      throw new Error(`${response.status} ${await response.text()}`)
122 
    }
123 
    const page = await response.json()
124 
    result.records.push(...page.records)
125 
    result.createdRecords.push(...page.createdRecords)
126 
    result.updatedRecords.push(...page.updatedRecords)
127 
  }
128
129 
  return result
130 
}
131