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).
To qualify for participating in this content, you should submit:
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.
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.
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.
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 …
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:
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.
An “Import Rejects” table. This has 2 single line text fields - “Record” and “Reason”
Is not empty
Must be an email
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!
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.
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