Retrieve the number of files in the folder and use them for new file names

▽ Purpose
GAS has created a script to convert Excel files into spreadsheet files.In order to serialize the spreadsheet name, I tried to retrieve the number of files in the folder and use them as file names, but they didn't work.

▽Results
·The conversion to the spreadsheet will be completed without any problems.
·The first part of the spreadsheet is displayed as NaN.This is the last line of code, ss.rename(numFiles+"_"+filename);" and I would like numFiles to have a number, but it says NaN.
·If you look at the execution transcript in the first place, Logger.log(numFiles); displays undefined.

▽ Sample code

 // Convert Excel file to spreadsheet and rename
function convertXls2SS(){
  varsrcFolder=DriveApp.getFolderById(****); // Excel File Storage Folder
  variableFolder=DriveApp.getFolderById(****); // Spreadsheet Storage Folder
  var files = srcFolder.getFilesByType(MimeType.MICROSOFT_EXCEL);

  // Retrieve the number of files in the spreadsheet storage folder
  varsFiles=aftFolder.getFiles();
  var numFiles=ssFiles.length;
  Logger.log(numFiles);

//  var files=srcFolder.searchFiles('mimeType='+'"application/vnd.ms-excel">);
  while(files.hasNext()){
    // Count up the number of files
    numFiles++;
    // Convert pre-conversion Excel to spreadsheet
    var file=files.next();
    varres=Drive.Files.insert({
      "mimeType": MimeType.GOOGLE_SHEETS,
      "parents": [{id:****}],
      "title": file.getName()
    }, DriveApp.getFileById(file.getId()) .getBlob());

    // Change sheet name
    vars = SpreadsheetApp.openById (res.id);        
    ss.rename(numFiles+"_"+filename);

That's all.I would appreciate your help.

google-apps-script

2022-09-30 19:56

1 Answers

How about a solution like this?The problem is that the DriveApp method getFiles() returns FileIterator.ssFiles.length returns undefined because it is not an array.There are two possible ways to get the number of files:There may be other ways, so please take this answer as one of them.numFiles is the only problem, so I will answer that part.

To get the number of files getFiles() returns, make the following changes:

From:

varsFiles=aftFolder.getFiles();
var numFiles=ssFiles.length;
Logger.log(numFiles);

To:

varsFiles=aftFolder.getFiles();
var numFiles = 0;
while(ssFiles.hasNext()){
  ssFiles.next();
  numFiles+=1;
}
Logger.log(numFiles);

If you want to get the number of files as the size of the array, such as ssFiles.length, you can use the Drive API for Google's enhanced services to change the following:As you have already used the Google Extended Services Drive API in your script, I have also suggested this pattern ("From:" is the same as above). If you have more files, this is probably faster.

To:

varnumFiles=Drive.Files.list({q:"'###folderId##'in parents and mimeType!='+MimeType.FOLDER+"'}.items.length;
Logger.log(numFiles);

Notes:

If you use this script, you do not need the varaftFolder=DriveApp.getFolderById(****);// Spreadsheet storage folder.
- Institute ##folderId### with ****.

Institute ##folderId### with ****.

References:

2022-09-30 19:56

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656