Realizing Custom Applications With SeaTable Scripts
SeaTable is the next-generation online collaboration and information management tool. It supports rich data types such as files, images, single select fields, collaborator references, and many more. All your business information can be stored, organized, shared, and managed with SeaTable. As a lightweight database with an intuitive spreadsheet UI, SeaTable also lends itself to applications far beyond the normal use cases of online spreadsheets. SeaTable is not just an ocean of functionalities, but it also takes you on waves and lets you feel the fresh breeze of software innovation.
Every once in a while, you will encounter situations in which SeaTable’s standard features do not provide an easy solution. Don’t worry — SeaTable’s got you covered! For example, you may want to randomly pick some rows, automatically send an e-mail or generate a file. To meet these special demands, SeaTable Scripts, a scripting engine, has been integrated in SeaTable 1.3. If you want to realize a special data manipulation, just write your own little programm!
In this article, we’ll walk you through the process of a.) setting up a customer survey with SeaTable Forms and b.) running a random prize draw from these survey’s participants using SeaTable Scripts. By the end of this article, we’ll have developed a mini custom application in SeaTable.
There’s only one requirement for following this example: You need to login to SeaTable. If you haven’t registered, you can sign up for a free account on SeaTable.io.
Collect answers with SeaTable Forms
SeaTable integrates online forms as a standard feature. So when the table layout has been defined, the corresponding web form is already ready to use. You can fine-tune the form by excluding fields and by choosing whether or not the included ones are mandatory. SeaTable also provides the possibility to set up conditional questions. Assume the question ‘Would you recommend our product to your friends?’ is set to have three possible answers: Yes, no, maybe. Wouldn’t it be interesting to learn more from the unhappy customers? Here is what you can do: You create two additional columns ‘Why wouldn’t you recommend us?’ and ‘You are not sure. What can we improve?’ and then, in the form settings, you set these two questions to only appear, when the previous answer was no or maybe, respectively. Of course, the happy participants won’t see either question.
Enough theory, let’s get into practice.
Create your base and word the questions
Create a base ‘Customer Survey’ and start to add columns to the table. The names of the columns you create are the questions your customers will see on the web form.
When creating the columns, remember to choose the right column types. When you ask the name, use the text type; for Industry and Career, use the single select type. You may want to hear more from your participants about their ideas, so use the long text type for the ‘Suggestions’ column. This way, they can include text, images, links, and even tables in their answers.
Put up the form with several mouse clicks
In the top right corner of the table view, click on the ‘Forms’ button and give your new form a descriptive name. In the form editor, which will pop up next, you can decide which questions to show on the form, if notifications should be sent upon new entries, and what access permissions apply. Also, remember to set up the conditional questions. You can also add a note to appear on the form for either instructions, a “thank you” to the participants, or data privacy rules.
When you are done, click on the ‘Form Page’ button in the top right corner to show a preview of your survey. Test it and see how an answer will be automatically added to your table. If you are happy with the output, the form is ready to be shared.
Share your form with a link or a QR-Code
Click on the ‘Share Form’ button and a link as well as a QR-code are presented to you. You can either send the link via email or put up the QR-code that can be scanned with one’s mobile device.
And yes — of course — a SeaTable form is mobile friendly!
When the participants then open the link and fill in the form, their submissions will automatically be filled into your table ‘Customer Survey’ in real-time. Each answer is represented by one row in your table.
Survey is over? Choose the winners with SeaTable Scripts
To sweeten the deal, you have promised to your customers that five participants will be rewarded with a prize. Now that the survey is over, it is time to keep good on your word!
In order for the choice of the winners to be truly random, and thus fair, we’ll delegate the draw to a SeaTable script.
Add a new script
To prepare for the script, add a new table named ‘Winners’ within the base ‘Customer Survey’. This table will later list off the randomly chosen winning participants. There is no need to create any column in this table manually. The script will do that for us. In this table, click on the ‘Scripts’ icon in the top right corner and add a new script. Let’s call it ‘Random prize draw’. The following script will just do that:
const winningTableName = 'Winners';
const winningColumnName = 'Name';
const alternativeTableName = 'Customer Survey';
const alternativeViewName = 'Default view';
const alternativeColumnName = 'Your name?';
const winningCount = 5;
const table = base.getTableByName(alternativeTableName);
const view = base.getViewByName(table, alternativeViewName);
const rows = base.getRows(table, view);
const winningTable = base.getTableByName(winningTableName);for (let i = 0; i < winningCount; i++) {
let index = Math.floor(Math.random() * rows.length);
selectedRow = rows[index];
const selectedUser = selectedRow[alternativeColumnName];
rows.splice(index, 1);
base.addRow(winningTable, {[winningColumnName]: selectedUser});
}
And the winners are…
In the lower right corner, click on the ‘Run script’ button to run the script. And voilà — five participants have been randomly chosen from the table ‘Customer Survey’ and listed in the ‘Winners’ table. Note: When you rerun the code by hitting ‘Execute’, the script will choose five new winners and replace the previous ones.
To better understand and possibly adjust the script, consult the SeaTable Scripts Programming Manual. The manual is just one click away by clicking on the “i” icon in the lower left corner of the script editor.
Everyone wins with SeaTable
So that’s how you build a custom application with SeaTable! The survey was carried out efficiently and automatically through a web form and a script then randomly chose the winners. Granted, this custom application is miniature, but it highlights the capabilities of SeaTable and, with little effort, can be expanded and customized. By exporting, you can reuse the script and share it with others.
For reference, we have created a template base for you. It contains the column definitions, the form and the script. The single select columns “In which industry do you work?” and “What is your occupation?” are already equipped with SIC and SOC 2-digit codes. Feel free to tweak and use it as you see fit.
Feel inspired? Try it for yourself! Questions, comments, suggestions, or feature requests — let us know by starting a discussion on the SeaTable Forum.
