Help

Creating a list of dates within ranges

2139 3
cancel
Showing results for 
Search instead for 
Did you mean: 
LKI
4 - Data Explorer
4 - Data Explorer

Hi Airtable community,

I'm trying to write a script to update a column on my table based on different types of date ranges. I have the following columns that are relevant to the script:Screenshot 2023-07-05 at 8.16.10 PM.png

First column defines the type of date. Type 1 is regular date range (from day A to day B), type 2 is range with days of the week (Mondays and Saturdays from day A to day B), type 3 is random dates (July 1st, 7th, and 19th).
Second and third columns are start and end dates, which are mandatory for every row.
Fourth column is only filled out for type 3 dates, and it has DD/MM/YYYY dates separated by spaces.
Fifth column is only filled out for type 2 dates (range with days of the week), and includes the days of the week to be included, separated by spaces.

I created the script below to populates another column with a string of DD/MM/YYYY dates in each of those scenarios. However, the rows with date ranges with days of the week are not populating at all, and I don't know how to fix it. It looks like it may be because the parameters in the getDatesInRange function are not in the right format.

Any ideas?

Thank you! 

 

 

const table = base.getTable('Table 1');
const records = await table.selectRecordsAsync();

const updates = [];

for (const record of records.records) {
const eventType = record.getCellValueAsString('Evento datas tipo');
const specificDates = record.getCellValue('Lista de datas');
const startDate = record.getCellValue('Data início');
const endDate = record.getCellValue('Data término');
const daysOfWeek = record.getCellValueAsString('Lista de dias da semana');
let updatedValue = '';

if (eventType === 'Lista de datas específicas') {
updatedValue = specificDates || '';
} else if (eventType === 'Intervalo de datas') {
if (startDate && endDate) {
updatedValue = getFormattedDatesInRange(startDate, endDate);
}
} else if (eventType === 'Intervalo de datas com dias da semana') {
if (startDate && endDate && daysOfWeek) {
const allDates = getDatesInRange(startDate, endDate);
const filteredDates = allDates.filter(date => {
const dayOfWeek = date.getUTCDay();
const dayOfWeekAbbreviation = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'][dayOfWeek];
return daysOfWeek.includes(dayOfWeekAbbreviation);
});
updatedValue = getFormattedDates(filteredDates);
}
}

updates.push({
id: record.id,
fields: {
'Lista de datas teste': updatedValue
}
});
}

await table.updateRecordsAsync(updates);

function getDatesInRange(startDate, endDate) {
const dates = [];
const current = new Date(startDate);

while (current <= endDate) {
dates.push(new Date(current));
current.setDate(current.getDate() + 1);
}
return dates;
}

function getFormattedDates(dates) {
return dates.map(date => {
const day = String(date.getUTCDate()).padStart(2, '0');
const month = String(date.getUTCMonth() + 1).padStart(2, '0');
const year = String(date.getUTCFullYear());
return `${day}/${month}/${year}`;
}).join(' ');
}

function getFormattedDatesInRange(startDate, endDate) {
const start = new Date(startDate);
const end = new Date(endDate);
return getFormattedDates(getDatesInRange(start, end));
}

 

3 Replies 3
Sho
11 - Venus
11 - Venus

Hi @LKI ,
I just had a quick look
This line is wrong.

 

const dayOfWeekAbbreviation = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'][dayOfWeek];

If possible, an error should be output, so share it.

 

LKI
4 - Data Explorer
4 - Data Explorer

Hi Sho,

It doesn't actually hit an error, it just doesn't generate dates when it should (see empty array in the console log). I think it's because that specific start date is still "2023-07-01" while the other ones were formatted?

Screenshot 2023-07-05 at 9.59.34 PM.png

My mistake. It was an unfamiliar way of writing for me
Debugging other people's code is hard...
I guess it would be easier if I could test it on Airtable