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:
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.
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));
}
