Edits history of script submission #23497 for ' Upsert Records (airtable)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //native
  
  export type DynSelect_base_id = string
  export type DynSelect_table_id = string
  export type DynMultiselect_fields_to_merge_on = string[]
  
  // Dropdown of the bases the token can access.
  export async function base_id(auth: RT.Airtable) {
    const options: { value: string; label: string }[] = []
    let offset: string | undefined = undefined
    do {
      const url = new URL("https://api.airtable.com/v0/meta/bases")
      if (offset) {
        url.searchParams.append("offset", offset)
      }
      const response = await fetch(url, {
        headers: {
          Authorization: `Bearer ${auth.apiKey}`,
          Accept: "application/json",
        },
      })
      if (!response.ok) {
        throw new Error(`${response.status} ${await response.text()}`)
      }
      const page = (await response.json()) as {
        bases: { id: string; name: string }[]
        offset?: string
      }
      options.push(...page.bases.map((b) => ({ value: b.id, label: b.name })))
      offset = page.offset
    } while (offset)
    return options
  }
  
  async function listTables(auth: RT.Airtable, base_id: string) {
    if (!base_id) {
      return []
    }
    const response = await fetch(
      `https://api.airtable.com/v0/meta/bases/${base_id}/tables`,
      {
        headers: {
          Authorization: `Bearer ${auth.apiKey}`,
          Accept: "application/json",
        },
      }
    )
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    const { tables } = (await response.json()) as {
      tables: {
        id: string
        name: string
        fields: { id: string; name: string; type: string }[]
        views: { id: string; name: string }[]
      }[]
    }
    return tables
  }
  
  // Dropdown of the tables in the chosen base.
  export async function table_id(auth: RT.Airtable, base_id: DynSelect_base_id) {
    return (await listTables(auth, base_id)).map((t) => ({
      value: t.id,
      label: t.name,
    }))
  }
  
  // Checklist of the fields in the chosen table.
  export async function fields_to_merge_on(
    auth: RT.Airtable,
    base_id: DynSelect_base_id,
    table_id: DynSelect_table_id
  ) {
    const table = (await listTables(auth, base_id)).find((t) => t.id === table_id)
    return (table?.fields ?? []).map((f) => ({
      value: f.id,
      label: `${f.name} (${f.type})`,
    }))
  }
  
  /**
   * Upsert Records
   * 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).
   */
  export async function main(
    auth: RT.Airtable,
    base_id: DynSelect_base_id,
    table_id: DynSelect_table_id,
    records: { [key: string]: any }[],
    fields_to_merge_on: DynMultiselect_fields_to_merge_on,
    typecast: boolean | undefined,
    return_fields_by_field_id: boolean | undefined
  ) {
    const result = {
      records: [] as any[],
      createdRecords: [] as string[],
      updatedRecords: [] as string[],
    }
    // The API accepts at most 10 records per request.
    for (let i = 0; i < records.length; i += 10) {
      const response = await fetch(
        `https://api.airtable.com/v0/${base_id}/${table_id}`,
        {
          method: "PATCH",
          headers: {
            Authorization: `Bearer ${auth.apiKey}`,
            "Content-Type": "application/json",
            Accept: "application/json",
          },
          body: JSON.stringify({
            performUpsert: { fieldsToMergeOn: fields_to_merge_on },
            records: records.slice(i, i + 10).map((fields) => ({ fields })),
            typecast: typecast === true,
            returnFieldsByFieldId: return_fields_by_field_id === true,
          }),
        }
      )
      if (!response.ok) {
        throw new Error(`${response.status} ${await response.text()}`)
      }
      const page = await response.json()
      result.records.push(...page.records)
      result.createdRecords.push(...page.createdRecords)
      result.updatedRecords.push(...page.updatedRecords)
    }
  
    return result
  }
  

  Submitted by hugo989 3 hours ago