Using a script to get the cell data from a specific date

[MrLuke]

Hello

I have a task I'm trying to achieve with a script in an automation. It's an automation to run every 3rd week of a month (ideally), and to trigger a script which will find all the Wednesdays in the next month and create certain records on those dates. That part I have sussed out, but what I can't find out how to do would be to grab the data/title from the last record of the current month the automation runs on so that I can make sure the new records it creates in the following month flow on correctly. 

Eg:

Automation runs on 16th Jan, triggers a script to create records on every Wednesday in February, starting with a specific title relative to the last Wednesday record in January.

The script needs to find out the title of record on the last wednesday of January (which would have been the 25th) and pass that variable into some logic I'll create to determine the titles of the February entries it automates.

Hopefully this makes sense! Thanks

[TheTimeSavingCo]

If you're open to creating a view that only shows the Wednesday records, you could try using this?

let query = await table.selectRecordsAsync()

for (let r of query.records){
    let date = new Date(r.getCellValue(field))
    dates.push(date)
    recordDateObject[date.toDateString()] = r.id
}

let maxDate=new Date(Math.max.apply(null,dates));

let latestRecord = await table.selectRecordAsync(recordDateObject[maxDate.toDateString()])

This script will just grab the record with the latest date for you, so if, when you run it on the 16th you're certain there's a record in the view for the 25th, it'll grab that one.  So just manually set it up the first month, and once you have the automation running it should be fine

It'll break if the view you're using ever contains a record that isn't from a Wednesday though

[MrLuke]

Thanks for this, there are other records in the view, but there will always be something on Wednesdays as it's a recurring schedule I'm trying to automate the creation of

[TheTimeSavingCo]

Ah, any chance you could make a view that only contains Wednesdays?  Use a `Weekday()` to check whether it's Wednesday and filter on that

[MrLuke]

Not in this case as it's a client managed table for various schedules, but there's just a recurring event every Wednesday they don't want to have to manually input all the time

[MrLuke · Accepted solution]

Using your code example as a base, I managed to crack what I wanted to achieve 🙂 Here's the final code in case it helps anyone else to modify etc to their needs:

function firstOrLastDayOfMonth(isFirstDay) {
  let date = new Date();
  return isFirstDay 
    ? new Date(date.getFullYear(), date.getMonth(), 1) 
    : new Date(date.getFullYear(), date.getMonth() + 1, 0);
}

let start = firstOrLastDayOfMonth(true);
let end = firstOrLastDayOfMonth(false);

let dates = [],
  recordDateObject = [],
  options = { weekday: 'long' };
for (let r of query.records) {
  let date = new Date(r.getCellValue("Pub Date"));
  let dayName = date.toLocaleDateString('en-US', options);
  //Get all Wednesday records
  if (date >= start && date <= end && dayName === "Wednesday") {
    dates.push(date);
  }
  recordDateObject[date.toDateString()] = r.id
}
let maxDate = new Date(Math.max.apply(null, dates));
let latestRecord = await table.selectRecordAsync(recordDateObject[maxDate.toDateString()]);

console.log(latestRecord); //Final record of the last Wednesday of the current month