▽ 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
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:
varsFiles=aftFolder.getFiles();
var numFiles=ssFiles.length;
Logger.log(numFiles);
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.
varnumFiles=Drive.Files.list({q:"'###folderId##'in parents and mimeType!='+MimeType.FOLDER+"'}.items.length;
Logger.log(numFiles);
varaftFolder=DriveApp.getFolderById(****);// Spreadsheet storage folder
.
##folderId###
with ****
.##folderId###
with ****
.
845 Uncaught (inpromise) Error on Electron: An object could not be cloned
1225 When building Fast API+Uvicorn environment with PyInstaller, console=False results in an error
765 M2 Mac fails to install rbenv install 3.1.3 due to errors
768 GDB gets version error when attempting to debug with the Presense SDK (IDE)
© 2025 OneMinuteCode. All rights reserved.