Create Recurring Dates/Records with a Script or Automation

I’ve found and commented in this thread: Repeating events in calendar view - #7 by juliedeily

No ideal solutions there.

Schedule by Zapier is a good solution if you have only one or a few sets of recurring dates. Imagine if you have 10 different class schedules and you’re adding new ones all the time. Zapier would not be workable. I’m going to run some tests on Zapier and anyway will report back here if I can stitch something together that is better than the handle dragging solution.

… No better solutions as yet. I hope one of the regulars can crack this one!

I’m working on a similar problem: Recurring Horse Riding Lessons

Basically the answer was for me to learn scripting. I’m working on it and will report back when I figure this out.

Hopping on this thread to say I have a similar issue trying to create recurring dates/records for a direct cash payment program.

In my use case, I want to answer 3 basic questions and then automate the generation of records with populated dates:

• When is the first payment going out?
• Will payments be delivered on the 1st or the 15th of each month?
• How many payments will be delivered? (how many records need to be generated?)

I made a script that can do this. This is my first time on a forum like this. What’s the best practice for sharing code? The script in it’s current state requires entering the date/time slots manually in the code itself, but I’m currently working on a v2 that will read all the time slots from a separate “Recurring events” table. Let me know how I can help –

Maybe you could copy and paste what you have here? Or put it in a Google Doc? I’d love to see it as I haven’t made it far in learning scripting. Thank you!

Here’s what I have. You have to go into the code to update the name of the table you want to add records to (line 37), dates/times of the time slots (which are in arrays starting at line 39 where the ‘deepDiveDays’ array uses days of the week in numbers. i.e. if you had Mon, Tue, Wed, it would be [1,2,3]), and there is a spot for a field you’d want to customize on line 105 as well. Sorry it isn’t more user friendly at the moment. I’m working on a version that doesn’t rely on updating the code to make it work for other tables.

// Author: Andrew Heine

// SETTINGS ———————

const config = input.config({
    title: 'Calendar signup slot generator',
    description: 'A script to automatically generate preset weekly time slots for people to sign up for.',
    // items: [
    //     input.config.select('deepDiveDaysConfig', {
    //         label: 'Days available',
    //         description: 'Days that we will have Deep Dive slots available',
    //         options: [
    //             {label: 'Mon', value: '1'},
    //             {label: 'Tues', value: '2'},
    //             {label: 'Weds', value: '3'},
    //             {label: 'Thurs', value: '4'},
    //             {label: 'Fri', value: '5'},
    //         ]
    //     })
    // ]
});
const country = config.country;


// UTILITY ———————

// This function adds leading zeros for date formatting
Number.prototype.pad = function(size) {
  var s = String(this);
  while (s.length < (size || 2)) {s = "0" + s;}
  return s;
}


// CONFIG ———————

let table = base.getTable('Shares/reviews'); // Pick what table to pull data from

var deepDiveDays = [4];  // need to automate some of this, but currently it's on the honor system that all these arrays will be the same legth
var deepDiveTimes = [
    ["08:35","08:55","09:15"]
];

var deepDiveTimesEnd = [
    ["08:55","09:15","09:45"]
];

let earliestDateToAdd = await input.textAsync('What is the earliest date you want to add records for? (YYYY-MM-DD)');
earliestDateToAdd = Date.parse(`${earliestDateToAdd}`);   // Date format: YYYY-MM-DD

let weeksToAdd = await input.textAsync('How many weeks worth of review slots do you want to add?');



// INIT —————————

var foundStartDate = 0;
var recordsAddedCount = 0;

var today = new Date();
today = Date.now();

timeDifference = (earliestDateToAdd - today);

var startDate = new Date(Date.now() + timeDifference + 25200000);

var thisDate = new Date();
thisDate = startDate;
thisDay = thisDate.getDay();


for (i = 0; i < weeksToAdd * 7; i++) {   

    thisDay = thisDate.getDay();

    for (j = 0; j < deepDiveDays.length; j++) {

        if (thisDay == deepDiveDays[j]) {

            thisDateNumber = thisDate.getDate().pad(2);

            thisMonthNumber = thisDate.getMonth() + 1; // getMonth() starts with Jan being 0
            thisMonthNumber = thisMonthNumber.pad(2);

            thisYearNumber = thisDate.getFullYear();

            thisDateFormatted = thisYearNumber + "-" + thisMonthNumber + "-" + thisDateNumber;

            thisDaySlots = deepDiveTimes[j];
            thisDaySlotsEnd = deepDiveTimesEnd[j];

            for (l = 0; l < thisDaySlots.length; l++) {

                thisTimeSlot = thisDaySlots[l];
                thisTimeSlotEnd = thisDaySlotsEnd[l];

                thisTimeSlotFormatted = thisDateFormatted + " "+thisTimeSlot;
                thisTimeSlotEndFormatted = thisDateFormatted + " " + thisTimeSlotEnd;                    

                await table.createRecordsAsync([
                    {
                        fields: {
                            'Date/Time': thisTimeSlotFormatted,
                            'End Date/Time': thisTimeSlotEndFormatted,
                            'Event type': { name: 'Team mtg - OPEN SLOT'},
                        },
                    },
                ]) 
                console.log("Added: "+thisTimeSlotFormatted);
                recordsAddedCount++;
            }
            break;
        }
    }
    thisDate.setDate(thisDate.getDate() + 1);
}

output.text('Done! '+recordsAddedCount+' records added –');

Hi Dean

Doing this now for recurring bills, tasks, meetings - did you find a solution?

This post is suitable for me - I would have a separate record for each ‘yoga class’ (it would be 3 tasks for me, recurring weekly on the date with a planned hr value (plan start and plan end)

Airtable automations solve this nicely - but alternatively I’m generating recurring bills in google-cal or outlook (I have work meetings in outlook, I use google cal with diffeerent ‘types’ of calendars for areas of life or areas of responsibility) - and when the record creates it’s sends to Airtable

This formula suits my needs for all manner of dates: Auto Create Bills with next Bill Date

Hi,

I think that such kind of tasks - to prepare a table filled with some recurring dates - does not require scripting at all. I would use date formulas (dateadd) in connection with autonumber and a lot of copypasting

Hi Alexey

I agree also - but have you found use in the airtable automations?

I’m using date add also, and for some just google calendar recurring tasks, synced to airtable (with automations I update task name, some fields per calendar it is coming from, eg ‘Meeting Calendar’ all are type ‘meeting’ for example, or ‘unplanned’, or ‘bills’)

I’d created a large project management base and used copy pasting and helper columns but now getting into automations finally and they are incredible! Change a status and an automation generates a ‘task’ - approve a ‘request’ and a project template is created with all generic tasks needed for the project (including which will be recurring project control tasks, eg setup weekly sprint, and so forth)

A lot of fun also :slightly_smiling_face:

// Author: Andrew Heine

// SETTINGS ———————

const config = input.config({
    title: 'Calendar signup slot generator',
    description: 'A script to automatically generate preset weekly time slots for people to sign up for.',
    // items: [
    //     input.config.select('deepDiveDaysConfig', {
    //         label: 'Days available',
    //         description: 'Days that we will have Deep Dive slots available',
    //         options: [
    //             {label: 'Mon', value: '1'},
    //             {label: 'Tues', value: '2'},
    //             {label: 'Weds', value: '3'},
    //             {label: 'Thurs', value: '4'},
    //             {label: 'Fri', value: '5'},
    //         ]
    //     })
    // ]
});
const country = config.country;


// UTILITY ———————

// This function adds leading zeros for date formatting
Number.prototype.pad = function(size) {
  var s = String(this);
  while (s.length < (size || 2)) {s = "0" + s;}
  return s;
}


// CONFIG ———————

let table = base.getTable('Shares/reviews'); // Pick what table to pull data from

var deepDiveDays = [4];  // need to automate some of this, but currently it's on the honor system that all these arrays will be the same legth
var deepDiveTimes = [
    ["08:35","08:55","09:15"]
];

var deepDiveTimesEnd = [
    ["08:55","09:15","09:45"]
];

let earliestDateToAdd = await input.textAsync('What is the earliest date you want to add records for? (YYYY-MM-DD)');
earliestDateToAdd = Date.parse(`${earliestDateToAdd}`);   // Date format: YYYY-MM-DD

let weeksToAdd = await input.textAsync('How many weeks worth of review slots do you want to add?');



// INIT —————————

var foundStartDate = 0;
var recordsAddedCount = 0;

var today = new Date();
today = Date.now();

timeDifference = (earliestDateToAdd - today);

var startDate = new Date(Date.now() + timeDifference + 25200000);

var thisDate = new Date();
thisDate = startDate;
thisDay = thisDate.getDay();


for (i = 0; i < weeksToAdd * 7; i++) {   

    thisDay = thisDate.getDay();

    for (j = 0; j < deepDiveDays.length; j++) {

        if (thisDay == deepDiveDays[j]) {

            thisDateNumber = thisDate.getDate().pad(2);

            thisMonthNumber = thisDate.getMonth() + 1; // getMonth() starts with Jan being 0
            thisMonthNumber = thisMonthNumber.pad(2);

            thisYearNumber = thisDate.getFullYear();

            thisDateFormatted = thisYearNumber + "-" + thisMonthNumber + "-" + thisDateNumber;

            thisDaySlots = deepDiveTimes[j];
            thisDaySlotsEnd = deepDiveTimesEnd[j];

            for (l = 0; l < thisDaySlots.length; l++) {

                thisTimeSlot = thisDaySlots[l];
                thisTimeSlotEnd = thisDaySlotsEnd[l];

                thisTimeSlotFormatted = thisDateFormatted + " "+thisTimeSlot;
                thisTimeSlotEndFormatted = thisDateFormatted + " " + thisTimeSlotEnd;                    

                await table.createRecordsAsync([
                    {
                        fields: {
                            'Date/Time': thisTimeSlotFormatted,
                            'End Date/Time': thisTimeSlotEndFormatted,
                            'Event type': { name: 'Team mtg - OPEN SLOT'},
                        },
                    },
                ]) 
                console.log("Added: "+thisTimeSlotFormatted);
                recordsAddedCount++;
            }
            break;
        }
    }
    thisDate.setDate(thisDate.getDate() + 1);
}

output.text('Done! '+recordsAddedCount+' records added –');

Hely Andrew

This is a god sent ! :slightly_smiling_face:

2 things I am trying to get my head around, is there a way to get it to get the start date, number of weeks and the days to generate the events from a record response in another base?