I got hundreds of datachunks (adding up daily) within a single gDrive Folder. They all nare amed pretty similat like in_history.csv, in_history (1).csv, in_history (2).csv, out_history.csv, out_history (2).csv, ... ...

The all follow the exact same format, where colum three holds a date/time stamp:

...

I kindly ask someone to point me into a direction on how to rename each file based on "column" 3 (Date) so I end with filenames like

• IN - 2022/04/13 - 2022/07/30.csv
• IN - yyyy/mm/dd - yyyy/mm/dd.csv
• OUT - yyyy/mm/dd - yyyy/mm/dd.csv
• ...

In a programmatical way I think I need something like this:

1. Loop through a defined folder to get all filenames
2. Loop through each containing .csv to get the lowest and highest date within colum 3
3. If filename contains "in" change filename to "IN - - .csv

I want to start using AppScript as I already found out how to trigger tasks on intervals and hope to learn more to utilize AppScript to ease up my daily life.

I lack o funderstanding on how to loop through the folder and get all files, from those files the rangse of "column" 3 (while there are no columns in .csv), extract the the smallest to highest date and modify the corresponding filename. Its a myth - not even sure if possible at all.

Any pointers or maybe even a starter I can wrap my brain around?

Note: The gDRive I am working in is a "personal" (not part of google organizational accounts/drives).

CodePudding user response：

I've coded up something quickly without much error handling, it should give you the result based on your given data files,

The high-level idea is,

1. Get the files from the src folder.
2. Read each file's content, and extract the last column dates.
3. Sort the dates, then take the last item in the list as StartDate and the first item in the list as EndDate.
4. Create the new file name as {IN or OUT} - {StartDate} - {EndDate}.csv.
5. Rename the old file to the above file name.

function fileRenamingTask() {
  var srcFolderId = "Your src folder id";
  var destFolderId = "Your dest folder id, can be the same as src folder";
  
  var SourceFolder = DriveApp.getFolderById(srcFolderId)
  var Files = SourceFolder.getFiles();
  while(Files.hasNext()) {
    
    var file = Files.next();
    var newFileName = getNewFileName(file);
    
    rename(file, newFileName, destFolderId)
  }
}

function getNewFileName(file) {
    var rows = parseCsv(file); 
    var dates = [] 
    for (i = 1; i < rows.length; i  ) {
      var dateTime = rows[i][2]; //assuming that the date item is always at the 3rd place
      var dateStr = dateTime.split(" ")[0]; //split with the space and get the date part
      dates.push(dateStr);
    }

    //sort the dates from latest to oldest
    dates.sort( function(a, b) {
      return new Date(b) - new Date(a);
     }
    )

    var startDate = dates[dates.length - 1];
    var endDate = dates[0];

    var dateRange = `${startDate.replace(/-/g, '/')} - ${endDate.replace(/-/g, '/')}`;
    var fileName = file.getName().split('_')[0].toUpperCase(); //get the first part of the filename in_history
    
    return `${fileName} - ${dateRange}.csv`;
}

function parseCsv(file) {
  var data = file.getBlob().getDataAsString();
  var lines = data.split("\n");
  var data = lines.map(function(lin){return lin.split(",")});
  return data;
}

function rename(file, newFileName, folderId) {
    var FileRename =  file.makeCopy(newFileName);
    var DestinationFolder = DriveApp.getFolderById(folderId)
    DestinationFolder.addFile(FileRename);
}