Help

Re: Create Recurring Dates/Records with a Script or Automation

3480 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dean_Arnold
7 - App Architect
7 - App Architect

I operate a booking system in Airtable and I have to generate months of recurring dates/times (one per record) in advance.

Click, hold, dragging date sequences is very helpful in this regard (see:
Quickly filling cells using fill handle – Airtable Support)

However, I’m left wanting more.

I’d like to create a script or automation to generate the recurring dates/records for me.

As an example, let’s say I was teaching a yoga class every Mon, Tue, Thurs, and Sat at 1 pm and 4 pm.

Instead of having to continually check my dates, then drag out hundreds of dates/records manually, I’d like to write a script or automation that would do it for me, thereby ensuring that the timeslots/records for the said class were always created several months in advance.

Has anyone solved a similar problem to this one? Does anyone have any thoughts as to how I should approach a solution?

I could imagine an Airtable app that had similar options to the “create recurring event” modal used by google calendars.

12 Replies 12
Dean_Arnold
7 - App Architect
7 - App Architect

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!

Christine_Bento
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Tom_Jeffrey
4 - Data Explorer
4 - Data Explorer

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?)
Andrew_Heine
5 - Automation Enthusiast
5 - Automation Enthusiast

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 –');
Alex_Whitton
6 - Interface Innovator
6 - Interface Innovator

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

image

Alex_Whitton
6 - Interface Innovator
6 - Interface Innovator

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:

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?