New $1,000 scripting challenge using input.fileAsync

Thank you for having clear rules regarding intellectual property rights for this contest.

By the way, there are some typos in “Airtable Contest Terms and Conditions.pdf”, paragraph 3, sentence 1. There word hereby is misspelled as herby: The verb maintains also does not have a clear subject (grammatically maintains agrees with Submission, but that doesn’t make logical sense).

Here is the original sentence. (Bolding is mine)

By submitting an entry, you herby acknowledge and agree that the Submission is provided under the Apache 2.0 and/or Creative Commons CC BY 4.0 (or successor licenses), as applicable, and maintains the right to use them in conjunction with the Contest without restriction.

I think you want

… you hereby acknowledge and agree that … and maintain the right …

Great catch @kuovonne!

My submission for the scripting challenge is a custom validation import process. If I use the input.fileAsync method to import a file, Airtable gives me some standard validations out of the box. For example, you can’t import a string into a number field. My script takes this further by allowing you to configure some custom validations for fields within the import file - have a look at the video below to see it in action:

The script also takes rejected records (either custom validations or Airtable validations) and puts them into a rejects table so that these can be viewed later without the need to sort through the original file to find out what was accepted and what was rejected.

The script can be found here:

Your base needs to be set up in a specific way to get it to work:

1. Your “Import” table needs to have fields that match your file, e.g. same field header names and the field types you expect to receive from the file.

2. An “Import Rejects” table. This has 2 single line text fields - “Record” and “Reason”

3. Finally, a “Conditions” table with a single field “Condition”. This has 3 rows:

Is not empty
Must be an email
Must contain

The Script

The script has a few comments which explain how it works, but to add to this, the logic behind each of the conditions is coded into the script. So…you can’t just add a new condition without the code to match :slightly_smiling_face: . In that sense, the script is very much a work in progress - I am sure there are other validation conditions that could be added, which would add to the usefulness.

The 10 second version is that the script asks the user to define validation conditions and these are “built up” as a set of validations prior to the import. As the file is imported, each record is validated against the set conditions (and also, by default, against Airtable standard validations).

Side point: I used records in the Conditions table for the user to select from. I started off setting these conditions as fields rather than records as, arguably, the user experience of picking a condition was better if you are choosing a field, rather than a record (a select dropdown, as opposed to a record pop-up), but this didn’t feel right (as in a bad use of Airtable fields), so I switched to records. Possibly one for the roadmap is a way to define a dropdown with some arbitrary selections.

If you’ve got any thoughts on how this could be improved, please let me know. One thing I thought about was putting the conditions logic in the “Conditions” table, but left this out for now (no idea if this is possible, but would be great if you could).

Hope you like it, feedback appreciated!

JB

Thanks @JonathanBowen! And submitted with four days to spare :winking_face:

My submission is a Shapefile importer. To use the Map Block you need geospatial data, the Shapefile importer makes it super easy to import data like states, counties, or cities and join it to your data using relational tables in Airtable.

In the video I show an example map of COVID-19 cases by state in the US.

Features

• Automatically populates a Location field compatible with the Map Block
• Allows you to pick which field to use as the name field.

Limitations

• Using the script requires importing several open source JavaScript libraries from a table in the base. You’ll need a copy of this table in your base for the script to work.
• I haven’t tested this with extremely large/complex data, the processing happens in the browser so it may slow down with larger data.
• It doesn’t load all the attribution information from the Shapefile. The API doesn’t yet support creating new columns so this can’t be automated. You could customize the script to include additional columns as long as you have them set ahead of time in your table. As a work around for now I put JSON formatted data into the Notes column.

This script also shows how to open a zip file which could be useful for other things too.

• COVID-19 data from https://en.wikipedia.org/wiki/COVID-19_pandemic_in_the_United_States
• States data from https://www.naturalearthdata.com/downloads/110m-cultural-vectors/

The base with the script code is available here (https://airtable.com/shr8geIos1J47BRXM) and just the script code at https://gist.github.com/kriscarle/15da1f0d38406589dc0b1bf140f79dd3

Thanks @KrisCarle! Looks especially useful right now with pulling from the COVID-19 data.

Thank you for your amazing submissions! Our team is going to review them and announce the winner by May 15, 2020.

Hello! :drum: :drum: :drum:

And the winner is… @JonathanBowen for his custom validation importer!

We’re looking forward to unlocking even more functionality though this block. Stay tuned for the next contest :slightly_smiling_face:

@Kasra Thank you!!

Jonathan