Convert all excel files from a folder to google sheets and output to another folder

[mountainman88]

I have this google apps script which converts an excel file to a google sheets file in the same folder based on the file name in cell a1 on a tab called files:

main(){ let spreadsheet = SpreadsheetApp.getActive(); let sheet = spreadsheet.getSheetByName("files"); let fileName = sheet.getRange("A1").getValue(); let spreadsheetId = convertExcelToGoogleSheets(fileName); importDataFromSpreadsheet(spreadsheetId); } function convertExcelToGoogleSheets(fileName) { let files = DriveApp.getFilesByName(fileName); let excelFile = null; if(files.hasNext()) excelFile = files.next(); else return null; let blob = excelFile.getBlob(); let config = { title: "[Google Sheets] " + excelFile.getName(), parents: [{id: excelFile.getParents().next().getId()}], mimeType: MimeType.GOOGLE_SHEETS }; let spreadsheet = Drive.Files.insert(config, blob); return spreadsheet.id; }

How can I change this script so cell A1 and B1 are input and output folder urls. I would like the script to scan the A1 folder link and convert all excel files within it to Google Sheets and then output or the B1 folder url.

Thanks

 

[mountainman88]

Im sharing a script below which converts all Excel files from within the Folder ID in cell A1 of a sheet called 'Files' and outputs them to a Folder ID in cell B1 of the sheet called files, you need the drive API service loaded:

function importXLS() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("names");
var folderAId = sourceSheet.getRange("A1").getValue(); // Get folder ID from cell A1
var folderBId = sourceSheet.getRange("B1").getValue(); // Get folder ID from cell B1

var files = DriveApp.getFolderById(folderAId).searchFiles('title != "nothing"');
while(files.hasNext()){
var xFile = files.next();
var name = xFile.getName();
if (name.indexOf('.xlsx')>-1){
var ID = xFile.getId();
var xBlob = xFile.getBlob();
var newFile = {
title : name+'_converted',
parents: [{id: folderBId}]
};
file = Drive.Files.insert(newFile, xBlob, {
convert: true
});
// Drive.Files.remove(ID);
}
}
}

Can someone help me modify this so it will copy the whole folder stucture from FOlderID A as well as converting the excel files.

In addition the icing on the cake would be if the Google Sheets could be overwritten each time the script is run, keeping the same FileID so it's an overwrte rather than creating a whole new set of files.

Thanks

 

[veyselemre]

Hi, try this..

function XlsxToSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("names");
  var folderAId = sourceSheet.getRange("A1").getValue(); // Get folder ID from cell A1
  var folderBId = sourceSheet.getRange("B1").getValue(); // Get folder ID from cell B1
  var xlsxFileList = [];
  var files = DriveApp.getFolderById(folderAId).searchFiles('title != "nothing"');
  var fileInfo = {};

  while (files.hasNext()) {
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx') > -1) {
      var ID = xFile.getId();
      fileInfo = { "name": name, "id": ID, "convertedName": name + "_converted", convertedId: "" }
      xlsxFileList.push(fileInfo)
    }
  }

  files = DriveApp.getFolderById(folderBId).searchFiles('title != "nothing"');
  while (files.hasNext()) {
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('_converted') > -1) {
      xlsxFileList.forEach(fileInfo => {
        if (fileInfo.convertedName == name) {
          fileInfo.convertedId = xFile.getId();

          return;
        }
      })
    }
  }


  xlsxFileList.forEach(fileInfo => {
    var xFile = DriveApp.getFileById(fileInfo.id)
    var xBlob = xFile.getBlob();

    if (!fileInfo.convertedId) {
      var newFile = {
        title: fileInfo.convertedName,
        parents: [{ id: folderBId }],
        mimeType: MimeType.GOOGLE_SHEETS
      };
      var file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
      fileInfo.convertedId = file.id;
    }
    else {
      currentFile = DriveApp.getFileById(fileInfo.convertedId);

      if (currentFile) {
        Drive.Files.update({
          title: currentFile.getName(), mimeType: currentFile.getMimeType()
        }, currentFile.getId(), xBlob);
      }
    }
  })

  Logger.log(xlsxFileList)
}