Google Script import CSV every 30min

Question

I have this script for automatically importing a CSV into Google Sheets. I always used it to import every hour, but now need to import every 30min. The CSV file is usually available every 30min around the hour +-, for example 13:04 and then 13:33, 14:05, 14:32 etc. So +- every 30min around the hour. I'm struggling to get the script to import every 30min and also get the time right. Any help will be greatly appreciated!

Perhaps a more clear example:

Triggers are set, no issue. Script imports CSV data into Google Sheet, working. Issue is it's looking for the latest CSV file around the hour that it select to import. That worked. But now there is a CSV file every 30min. Script executes every 30min but only the CSV from the hour gets imported, not 30min also. For example, script runs at 19:00 and 19:00 CSV is imported. Script runs again at 19:30 but the 19:00 CSV get imported, not the 19:30 CSV

function checkFiles_DATACSV() {

  //get spreadsheet and sheet;
  var id = 'xxx'; //yourSpreadsheetId
  var ss = SpreadsheetApp.openById(id);
  var sh = ss.getSheetByName('Import_DATACSV');    
  
  var folderId = 'xxx'; 

  //get folder and files in it;
  var folder = DriveApp.getFolderById(folderId);
  var files  = folder.getFilesByType('text/csv');
  
  //get today and reset everything except hours;
  var today = new Date();
  //    today.setMinutes(0);
  //    today.setSeconds(0);
      //today.setMilliseconds(0);      

  //iterate over files;
  while(files.hasNext()) {
    var file = files.next();

    //get file creation date and reset;
    var created = file.getDateCreated();
        created.setMinutes(0);
        created.setSeconds(0);
        //created.setMilliseconds(0);
        CSVcreated = file.getDateCreated();

    //calculate offset, equals 0 for each file created this hour;
    var offset = today.valueOf()-created.valueOf(); 

    if(offset===0) {
      
      //add current spreadsheet date and time
      var time = new Date();
      time = Utilities.formatDate(time, "GMT", "dd/MM/yyyy, HH:mm");
     
      
      //perform data import here;
      var data = file.getBlob().getDataAsString();

      //ignore empty files;
      if(data!=='') { 
        //split data in rows;
        var arr = data.split('\r\n');

        //resplit array if only one row;
        if(arr.length===1) {
          arr = data.split('\n');
        }

        //delete existing sheet rows
        var clearsheet = ss.getSheetByName("Import_DATACSV");
        clearsheet.clearContents();
        //var clearsheetH = ssh.getSheetByName("Import_DATACSV");
        //clearsheetH.clearContents();
        
        //add data to sheet
        arr.forEach(function(el){
          el = el.split(';');
          
          //sh.appendRow(el)
          //sh.getRange(ss.getLastRow(), 8).setValue(CSVcreated);
          //sh.getRange(ss.getLastRow(), 16).setValue(time);

          //sh.appendRow(el)
          //sh.getRange(shh.getLastRow(), 7).setValue(CSVcreated);
          //shh.getRange(shh.getLastRow(), 16).setValue(time);  
        });
                     
       
       
      }          
    }

  }


}

To add the specific time and date to the sheet will also be great.

Thanks

Answer 1

To round the date created to the nearest half hour:

test(new Date(2022, 8, 25, 0 , 44, 1));
test(new Date(2022, 8, 25, 0 , 45, 2));
test(new Date(2022, 8, 25, 1 , 14, 3));
test(new Date(2022, 8, 25, 1 , 15, 4));
test(new Date(2022, 8, 25, 1 , 44, 5));
test(new Date(2022, 8, 25, 1 , 45, 6));
    
function roundToNearestHalfHour(date) {
  // >= 00:45 && < 01:15 => 01:00
  // >= 01:15 && < 01:45 => 01:30
  const rounded = new Date(date);
  const h = rounded.getHours();
  let m = rounded.getMinutes();
  let hOffset = 0;
  if (m >= 45) {
    hOffset = 1;
    m = 0;
  }
  else if (m < 15) {
    m = 0;
  }
  else {
    m = 30;
  }
  rounded.setHours(h + hOffset, m, 0, 0);
  return rounded;
}

function test(created) {
  const rounded = roundToNearestHalfHour(created);
  const o = {
    'created': created.toLocaleString(),
    'rounded': rounded.toLocaleString()
  };
  console.log(o);
}