Create recurring orders

This script will go through recurring orders and create a delivery record for each day that an order should repeat.

• Now if you change the “End date” of any recurring orders and run the script again, it will create new deliveries as needed. Previously it would skip recurring orders that had been processed. It uses a rollup field in the “Recurring orders” table to get the last scheduled delivery date.
• The recurring order “Start date” and the delivery “Date” now include time, so you can schedule orders at specific times of day.

See the source here to find a deeper explanation and link to an example base.

Script Code

							const ordersTable = base.getTable('Recurring orders');
const deliveriesTable = base.getTable('Deliveries');

const orders = await ordersTable.selectRecordsAsync();

const dayOfWeekToNumber = {
    Sun: 0,
    Mon: 1,
    Tue: 2,
    Wed: 3,
    Thu: 4,
    Fri: 5,
    Sat: 6,
};

for (const order of orders.records) {
    const name = order.name;
    const startDateString = order.getCellValue('Start Date');
    const endDateString = order.getCellValue('End Date');
    const daysOfWeek = order.getCellValue('Days of week');
    const lastDeliveryDateString = order.getCellValue('Last Delivery Date');

    if (!startDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have a start date.`);
        continue;
    }
    if (!endDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have an end date.`);
        continue;
    }
    if (!daysOfWeek) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have any 'Days of week' to repeat.`);
        continue;
    }

    const daysOfWeekSet = new Set();
    for (const dayOfWeek of daysOfWeek) {
        if (!dayOfWeekToNumber.hasOwnProperty(dayOfWeek.name)) {
            throw new Error(`Unexpected day of week: ${dayOfWeek.name}`);
        }
        daysOfWeekSet.add(dayOfWeekToNumber[dayOfWeek.name]);
    }

    const endDate = getDateFromString(endDateString);
    endDate.setDate(endDate.getDate() + 1); // First moment of the next day.

    // By default, deliveries will be creating starting from the start
    // date. But if deliveries have already been created, we want to start
    // at the day after the most recent delivery.
    const newDeliveriesStartDate = getDateTimeFromString(startDateString);
    if (lastDeliveryDateString) {
        const lastDeliveryDate = getDateFromString(lastDeliveryDateString);
        lastDeliveryDate.setDate(lastDeliveryDate.getDate() + 1);

        if (lastDeliveryDate >= endDate) {
            // The last generated delivery was after the end date, so skip processing
            output.text(`⚠️ No new deliveries to generate for ${name}. The last delivery date is past the end date.`);
            continue;
        }
        
        newDeliveriesStartDate.setFullYear(lastDeliveryDate.getFullYear());
        newDeliveriesStartDate.setMonth(lastDeliveryDate.getMonth());
        newDeliveriesStartDate.setDate(lastDeliveryDate.getDate());
    }

    let deliveriesToCreate = [];
    for (
        let currentDate = newDeliveriesStartDate;
        currentDate  0) {
        await deliveriesTable.createRecordsAsync(deliveriesToCreate.slice(0, 50));
        deliveriesToCreate = deliveriesToCreate.slice(50);
    }
}

output.text('✅ Done!');

function getDateFromString(dateString) {
    // Assumes dateString is yyyy-mm-dd
    const parts = dateString.split('-').map(part => parseFloat(part));
    const date = new Date();
    date.setFullYear(parts[0]);
    date.setMonth(parts[1] - 1);
    date.setDate(parts[2]);
    date.setHours(0, 0, 0, 0);
    return date;
}

function getDateTimeFromString(dateTimeString) {
    // Assumes dateString is like 2020-04-10T09:00:00.000Z
    const [dateString, timeString] = dateTimeString.split('T');
    const dateParts = dateString.split('-').map(part => parseFloat(part));
    const timeParts = timeString.split(':').map(part => parseFloat(part));

    const date = new Date();
    date.setFullYear(dateParts[0]);
    date.setMonth(dateParts[1] - 1);
    date.setDate(dateParts[2]);
    date.setHours(timeParts[0], timeParts[1], 0, 0);
    return date;
}

function getStringFromDateTime(dateTime) {
    // Returns yyyy-mm-ddThh:mm:00.000Z string.
    return [
        dateTime.getFullYear(),
        (dateTime.getMonth() + 1).toString().padStart(2, '0'),
        dateTime.getDate().toString().padStart(2, '0'),
    ].join('-') + 'T' + [
        dateTime.getHours().toString().padStart(2, '0'),
        dateTime.getMinutes().toString().padStart(2, '0'),
        '00.000Z',
    ].join(':');
}