Set Sequential Event Times

[Justin_Barrett]

Early this year I wrote a script¹ to help a user organize some sequential events, where one event would start when the previous event ended. Rather than continue to post updates to that original thread, I felt it would be best to start a new one.

---

This script lets you go from this:

…to this:

• Change durations and re-run the script to re-calculate the relevant start and end times (no need to clear fields first!)
• Choose as many fixed start times as you wish, and those times will remain untouched while the others calculate based on the end time of the previous record
• Tip: use record coloring to mark records with fixed start times, then hide the checkbox field for a cleaner view

Download the latest version of the script here:

gist.github.com

https://gist.github.com/justinsbarrett/2ebae3bd1c0a1e3956bfb3338ac6aeda

setSequentialEventTimes-scriptApp.js

// Allow user to select the table and view to use, and which fields will be affected
const config = input.config({
    title: "Set Sequential Event Times",
    description: `Update a collection of events that occur in sequence so that each new event starts when the previous event ends.

* Use the fixed start marker field (a checkbox field) to mark all records that have fixed start times.
* The first record in the selected view **must** have a fixed start time.
* Any record without a fixed start time will have its start time calculated based on the end time of the previous record.`,
    items: [
        input.config.table("eventsTable", {

This file has been truncated. show original

A version that will work in an Airtable automation is coming soon… :winking_face:

---

¹ While I used to develop scripts like this for free from time to time when I was becoming acquainted with Airtable’s development environment, custom development now comes with a price tag. I’ll continue to offer free support for this script as best I can, but if you’re looking for any kind of custom script development, please message me directly to discuss my current rate.

[Liz_Morrison]

WOWOWOWOWOW!!! This is a GAME CHANGER for planning events. Thank you so so so much @Justin_Barrett!! I just put this into my run of show base, and hours of tedious/error-prone work just got reduced to a press of a button. I am so grateful!

[Niruban]

THANK YOU SO MUCH!

I event went to the extent of getting something like this done on Fiverr. And found zero luck.

I wanted to ask you if there’s a way, the script could auto-execute evertime a row is added or event times are changed?

[Justin_Barrett]

Welcome to the community, @Niruban! :grinning_face_with_big_eyes: Thanks for reaching out! I saw that you also pinged me directly via my website, so I’ll reserve the more direct part of my reply to an email, while also trying to cover your general question:

Niruban:

I wanted to ask you if there’s a way, the script could auto-execute evertime a row is added or event times are changed?

There are two main ways that the script could auto-execute:

• Trigger an automation to run a variation of the script
• Develop a custom app that responds to data changes and updates the relevant records.

The latter is definitely possible, but would take some significant development time.

The former is also possible, but aside from the requirement that your base must be in a Pro-plan workspace or higher (required to run automation script actions), the tricky part is the trigger mechanism. You mentioned two options for triggering a refresh, both of which have their own unique problems:

• Every time a row (record) is added. This is the “When record created” trigger, and the issue here is that it fires immediately when the record is created. In other words, it doesn’t care whether the record has any data or not. If a record is added, that new record instantly triggers the automation, which runs on whatever data may (or may not) be present in the new record. If you can guarantee that new records are always created by a user submitting a form, this will work fine because the form submission adds all field data immediately during creation, and the automation won’t have any problems. However, if you (or your users) manually add new records in a grid view, this won’t work.
• When event times are changed. This would correspond to the “When record is updated” trigger. Similar to the other trigger I mentioned, this is quite literally true to its name. The instant that any change is made to a field, the trigger fires. That often means that the very first character typed when adding or changing field data will trigger the automation. Again, it’s not “When the user is finished editing a field”, it’s “When record is updated,” and that includes the smallest of updates. To work around this, I’ve developed a method that will wait for the user to finish editing (sorta-kinda), but it’s not going to be ideal for every situation. I’m planning on sharing this workaround in a separate thread (probably in the next week or so) if you want to consider that.

[JohnathanVR]

Better late than never, but did the 'When even times are changed' workaround ever surface?

[beauletai]

Hi Justin,

I just came across your script and it's actually very helpful for something similar that I want to do except for one very small thing: my start and end fields are also configured as Durations, not Dates. Is there any way that you could help me modify the script so that I can still use Durations? 🙂
Thanks in advance,

Bea