CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ankit_dot
ankit_dot

Reputation: 95

get latest uploaded CSV file in drive folder

I've a google sheet which needs to updated from CSV file, which usually uploaded/modified in the same folder where google sheet exist.

Now, I want to figure out, how to get latest uploaded/modified csv file in the same folder where google sheet exist, which starts from prefix like "CSV_" and update its data in sheet.

I've tried something like this to get the files:-

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();
  
  
  var getParentFile = DriveApp.getFileById(ssID).getParents();
  const parFolder = getParentFile.next()
  const files = parFolder.getFiles()
}

and this to upload data :-

var file = DriveApp.getFileById('CSV FILE ID HERE');
var data = file.getBlob().getDataAsString();
var lines = data.split("\n");
var data = lines.map(function(lin){return lin.split(",")});
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var dest = Spreadsheet.getSheets()[0];
dest.clear();
dest.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Thanks

Upvotes: 0

Views: 920

Answers (1)

Tanaike
Tanaike

Reputation: 201603

In your situation, how about the following modification?

Modified script:

function myFunction() {
  var prefix = "CSV_";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();
  var getParentFile = DriveApp.getFileById(ssID).getParents();
  var parFolder = getParentFile.next();

  var files = parFolder.searchFiles(`title contains '${prefix}' and trashed = false`); // OR var files = parFolder.searchFiles(`title contains '${prefix}' and trashed = false and mimeType = '${MimeType.CSV}'`);
  var fileList = [];
  while (files.hasNext()) {
    var file = files.next();
    if (file.getName().slice(0, prefix.length) == prefix) {
      fileList.push({ id: file.getId(), t: file.getDateCreated() });
    };
  }
  if (fileList.length == 0) return;
  var fileId = fileList.sort((a, b) => a.t.getTime() < b.t.getTime() ? 1 : -1)[0].id;

  var file = DriveApp.getFileById(fileId);
  var data = file.getBlob().getDataAsString();
  var csv = Utilities.parseCsv(data);
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var dest = Spreadsheet.getSheets()[0];
  dest.clear();
  dest.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}
  • When this script is run, the latest created file with the prefix of CSV_ is retrieved. And, using the retrieved file, your script is run.
  • When I saw your script, I thought that in order to parse CSV data, you might be able to use var csv = Utilities.parseCsv(data).

References:

Upvotes: 1

Related Questions