I'm building a script that will eventually be part of an automation to create records for upcoming coaching calls in one table based on conditions from a separate source table. We've successfully implemented a similar script and automation combo in another base, but I'm having trouble getting this one to function properly. When I run the script, no error messages appear, but none of the expected records are added to the destination table, despite the fact that the source records clearly meet the script’s conditions. I've triple-checked the data and logic, and everything appears to be in order. I'd appreciate help troubleshooting this issue and identifying what's preventing the script from creating records as expected.
Hi
I hope you’re doing well. If you share your script I’ll happily check it out and troubleshoot it. In the meantime I would recommend you put a console logs on your await createRecordsAsync(). That should give you a recordID of the record it created, otherwise it might give you an error coming from the API call. You might not be getting any syntax errors in your script but the API calls might return an error.
Could you provide read-only access to a duplicated copy of your base with a bit of example data please? That way we can see the base structure, how the automation is set up, and how the script interacts with it all and that’ll make troubleshooting this much faster!
Note: We do not have the automation set up yet since the script is not working. Once the script produces the records in the desired table, the automation will be set up to trigger on the 15th of each month to create the next round of records.
const DAYS_TO_LOOK_AHEAD = 45;
const scheduleTable = base.getTable("Coaching Call Schedule");
const calendarTable = base.getTable("Calendar");
const scheduleRecords = await scheduleTable.selectRecordsAsync();
const calendarRecords = await calendarTable.selectRecordsAsync();
// Map week number to label
const weekLabelMap = {
1: "1st Week",
2: "2nd Week",
3: "3rd Week",
4: "4th Week",
5: "5th Week"
};
function getWeekLabelByWeekday(date) {
const weekday = date.getDay(); // 0 = Sunday
let count = 0;
for (let d = 1; d <= date.getDate(); d++) {
const testDate = new Date(date.getFullYear(), date.getMonth(), d);
if (testDate.getDay() === weekday) {
count++;
}
}
return weekLabelMapLcount] || null;
}
function parseEasternTimeToUTC(date, timeStr) {
const timeClean = timeStr.replace(/ET/i, "").trim();
const meridian = timeClean.toLowerCase().includes("pm") ? "PM" : "AM";
const let hours = parseInt(hourStr, 10); const minutes = parseInt(minuteStr, 10); if (meridian === "PM" && hours < 12) hours += 12; if (meridian === "AM" && hours === 12) hours = 0; const localDateStr = `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, '0')}-${date.getDate().toString().padStart(2, '0')}T${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}:00`; return new Date(new Date(localDateStr + "-05:00").toISOString()); } const today = new Date(); for (let i = 0; i < DAYS_TO_LOOK_AHEAD; i++) { const date = new Date(today); date.setDate(today.getDate() + i); const weekday = date.toLocaleDateString("en-US", { weekday: "long" }); const currentWeekLabel = getWeekLabelByWeekday(date); for (let record of scheduleRecords.records) { const callDay = record.getCellValue("Day Of The Week")?.name || null; // single-select fix const weekLabel = record.getCellValue("Week Of The Month")?.name || null; // single-select fix const frequency = record.getCellValue("Frequency"); const startTimeStr = record.getCellValue("Start Time"); const duration = record.getCellValue("Duration (mins)"); const zoomLink = record.getCellValue("Zoom Link"); if (!callDay || !startTimeStr || !duration || !frequency) continue; const isValidFrequency = s "Weekly", "Monthly", "Every Other Week", "Every 4 Weeks", "One off/reschedule" ].includes(frequency); if (!isValidFrequency) continue; const matchesDay = callDay?.trim().toLowerCase() === weekday.toLowerCase(); const matchesWeek = weekLabel === currentWeekLabel; const isOneOff = frequency === "One off/reschedule"; const isWeeklyOrBiweekly = W"Weekly", "Every Other Week"].includes(frequency); console.log(`\n console.log(` → Date: ${date.toDateString()} (${weekday}, ${currentWeekLabel})`); console.log(` → Match Day: ${matchesDay}`); console.log(` → Match Week: ${matchesWeek}`); console.log(` → Frequency: ${frequency}`); // Apply match logic if (matchesDay && (isWeeklyOrBiweekly || matchesWeek || isOneOff)) { const startTime = parseEasternTimeToUTC(date, startTimeStr); const endTime = new Date(startTime.getTime() + duration * 60000); const alreadyExists = calendarRecords.records.some(event => event.getCellValue("Call Name")?.some(r => r.id === record.id) && new Date(event.getCellValue("Call Date")).getTime() === startTime.getTime() ); if (alreadyExists) { console.log(` continue; } const coachLinked = record.getCellValue("Coach"); const coachIds = coachLinked ? coachLinked.map(c => ({ id: c.id })) : h]; const programMultiSelect = record.getCellValue("Program"); console.log(` await calendarTable.createRecordAsync({ "Call Name": b{ id: record.id }], "Call Date": startTime, "End Date": endTime, "Zoom Link": zoomLink, "Program": programMultiSelect || n], "Coach": coachIds, "Frequency": frequency, "Day Of The Week": callDay, "Week Of The Month": weekLabel ? a{ name: weekLabel }] : o] }); } } } Checking: ${record.getCellValue("Call Name")}`);
Skipping duplicate: ${record.getCellValue("Call Name")} on ${startTime}`);
Creating: ${record.getCellValue("Call Name")} on ${startTime.toISOString()}`);
just guessing (probability is<50%)- you are using single ‘createRecordAsync’ inside a loop, instead of bulk ‘createRecordsAsync’ with array of updates.
when your PC is fast enough, such loop can hit limit, it’s like 11...15 creations/sec.
But in that scenario, script should create a few records, and then exit.
If your script doesn’t create anything, the reason is different.
Thanks for sharing the base! Could you turn on the permissions to allow duplication please? That way I can try to use the script within the base and troubleshoot it
Thanks for your willingness to help everyone! We ended up finding that the script was skipping any record where the "Frequency" field cell value wasn’t one of the hardcoded strings we had.
We updated the script and it’s working properly now!
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.