Randomizing a daily task list with a weekly automation

[JennJ]

Hello! 

I'm hoping someone here is great with automations/scripting/something to help. 

I'm trying to run an automation every Sunday to randomize work tasks for my team and then send an email or Teams message. I want it to refresh the randomization when this runs at it's scheduled time. I have 6 members, and already have their off days input. The Airtable randomize values extension works for what I'm wanting (and avoids the cells that are already filled with "Off"), but I can't run it on an automation and honestly, I don't know enough about JavaScript to change the code. Their code requires inputs from the extension panel with the min/max bounds (mine would be set to 1,1, which is how it avoids the "Off" cells) AND the specific field to update. 

Here are my data references:

Automation should run every Sunday at 3am (before anyone is logged in.. the time is somewhat arbitrary)
Randomize each task excluding "Off"
6 members in the first column
Each Day field is a multi select with the same 6 options (Off + 5 task types)
Should assign one random task type per person anywhere that "Off" is not already filled in.

[TheTimeSavingCo]

Think you'll need a script for this I'm afraid.  You're on a paid plan that can run scripts in automations I assume?

[JennJ · Accepted solution]

I am! I ended up going to our IT team and borrowed someone to help me get the script corrected. I think I spent a good couple of hours trying to research/learn more about JS, asyncs, and all the good stuff.

Here's what we came up with in case anyone else is trying to do something similar. Everything from //Sunday Records to the end of the script would get copied, pasted and updated for each day, and this let me set filters of sorts by day to avoid over-writing "Off" using "Find Records" in previous automation steps. I did 7 individual find records based on criteria, and then used those records in my input configuration (named rlistday), so even if I change the criteria in the previous step I won't need to re-code the script. I did go a step farther and add a few more repeat automations with slight adjustments to overwrite in the case of having too many repeats. Those aren't pretty, but they work. 

let inputConfig = input.config()
let reclistsun = inputConfig.rlistsun;
let reclistmon = inputConfig.rlistmon;
let reclisttue = inputConfig.rlisttue;
let reclistwed = inputConfig.rlistwed;
let reclistthu = inputConfig.rlistthu;
let reclistfri = inputConfig.rlistfri;
let reclistsat = inputConfig.rlistsat;
let table = base.getTable("tablename");

let tasks = [
    "Task 1",
    "Task 2",
    "Task 3",
    "Task 4"
];


randomTask = () => tasks[Math.floor(Math.random()*tasks.length)];

//Sunday Records
let querysun = await table.selectRecordsAsync({recordIds:reclistsun});
let recordssun = querysun.records;

await table.updateRecordsAsync(recordssun.map((record) => (
    {
        id: record.id,
        fields: {
            "Sunday": randomTask()
        }
    }
)));