Help

Re: New $1,000 scripting challenge using input.fileAsync

1357 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason
Airtable Employee
Airtable Employee

A few weeks ago we added the ability to upload files in the scripting block using input.fileAsync, and wanted to open up another challenge to the community around this feature.

For this challenge, submit a script that uses the input.fileAsync API for a specific workflow. We’d love to see creative and useful ways for this new input type to be used!

Maybe it’s a smarter importer, for example with fuzzy matching to update existing records, or a way to import Excel files with multiple sheets, or importers for other common apps that export CSV, JSON, or XML files. Or maybe it’s something totally different!

The winner of this contest will win $1,000 :tada: . All submissions are due by May 8, 2020 at 6:00pm (PST).

What exactly should be submitted

To qualify for participating in this content, you should submit:

  • A script using input.fileAsync in the scripting block
  • A short video demo of the script in action
  • A description of what the script does

Guidelines on submission format

Submit your entry by replying to this thread. Include a description of what your script does, the code for the script, and a link to the video demo. Since your description of what the script does is posted in this thread, the demo video should be concise enough to only demonstrate the script in action (ideally no more than 2 to 3 minutes); we won’t be judging submissions based on video length or production quality.

What rubric we will use for judging submissions

We’ll ask a panel of five of our customer success managers to vote on the script that demonstrate the most creativity and usefulness with the input.fileAsync API.

Terms and Conditions

All submissions are due by May 8, 2020 at 6:00pm (PST). The content winner will be announced within a week after the submission deadline. Please see our full Terms and Conditions for this contest here.

9 Replies 9

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”

Screenshot 2020-05-04 at 16.45.41

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

Is not empty
Must be an email
Must contain

Screenshot 2020-05-04 at 16.45.09

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

Jason
Airtable Employee
Airtable Employee

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

KrisCarle
4 - Data Explorer
4 - Data Explorer

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.

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

Jason
Airtable Employee
Airtable Employee

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: