Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 02, 2021 11:41 PM
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.
Mar 02, 2021 11:50 PM
I’ve found and commented in this thread: Repeating events in calendar view - #7 by juliedeily
No ideal solutions there.
Mar 02, 2021 11:59 PM
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.
Apr 11, 2021 06:35 PM
… No better solutions as yet. I hope one of the regulars can crack this one!
Apr 12, 2021 10:48 AM
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.
Apr 14, 2021 02:32 PM
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:
May 04, 2021 10:38 AM
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 –
May 04, 2021 02:44 PM
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!
May 04, 2021 03:23 PM
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 –');
Aug 14, 2022 07:45 AM
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