Airtable Insert, Update or Upsert by APIs

The challenge we were facing is the need to import thousands of records from an Excel file into Airtable. However, the issue arises because Airtable already contains thousands of records, requiring us to insert or update them based on matching the primary column. So we have implemented a fast and optimized way first.

• Retrieve all data from Airtable initially
• Match existing data with new data from the Excel file
• Split dataset for insertion or updating
• Utilize Airtable APIs to insert and update records in chunks of 10 records each
• Implement chunking because Airtable supports a maximum of 10 records per API request
• Account for potential duplicate records due to other users or scripts inserting duplicates

We're encountering lingering performance challenges, such as delays in script execution, which may result in missing records from other sources if Airtable receives additional data in the meantime.

We recently discovered an informative article highlighting Airtable's introduction of upsert APIs, enabling us to seamlessly insert or update records using primary keys. This resource provides a promising solution to our current issues.

Here is the example -

        
           
url = f"https://api.airtable.com/v0/{base_id}/{table_name}"
headers = {
	f"Bearer {api_key}",
    "Content-Type": "application/json"
}
data = {
	"performUpsert": {
    	"fieldsToMergeOn": ["Name"]
	},
	"records": records
}
response = requests.patch(url, headers=headers, json=data)
print(response.json())
        

#airtable #automation