I am trying to replace the old converted files with the new file. This is because every time i run the script it keeps on duplicating and multiplying in the same folder.

Here is the code:

function ConvertFiles() {

var sheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

var r= 2;
for(r= 2;r < sheet.getLastRow(); r  ){

var fileId= sheet.getRange(r,1).getValues();
var folderID = sheet.getRange(r,8).getValues();
Logger.log(fileId); 

var files = DriveApp.getFileById(fileId);
var name = files.getName().split('.')[0]; 
var blob = files.getBlob();


var destinationFolderId = DriveApp.getFolderById(folderID);   
Logger.log(folderID); 

var newFile = {
    title : name   '_converted', parents: [{id: 
destinationFolderId.getId()}]}; 
Logger.log(newFile); 

 }
}

My goal is:

1. To replace/update the old converted file into the latest one everytime the script runs (if it has the same filename)

2. I would like to push back the converted fileId into the google sheet to be displayed.

How can i solve this issue?

CodePudding user response：

I have added comments to show each part of the code. Kindly check the whole script below:

Script:

function ConvertFiles() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var r= 2;
  for(r= 2;r < sheet.getLastRow(); r  ){
    // Use getValue instead of getValues
    var fileId = sheet.getRange(r,1).getValue();
    var folderID = sheet.getRange(r,8).getValue();
    
    var files = DriveApp.getFileById(fileId);
    var name = files.getName().split('.')[0]; 
    var blob = files.getBlob();
    
    var destinationFolderId = DriveApp.getFolderById(folderID); 
      
    var newFile = {
    // Remove '_converted' from name if existing to avoid duplication of the string before adding '_converted'
    // This will allow to have newly converted file "replace" the old converted file properly
      title: name.replace('_converted','')   '_converted', 
      parents: [{
        id: destinationFolderId.getId()
      }]
    }; 

    // GOAL #1: To replace/update the old converted file into the latest one everytime the script runs (if it has the same filename)
    // Find the file with same name of the file to be converted
    while(destinationFolderId.getFilesByName(newFile.title).hasNext()) {
      // ID of the file with same converted name
      var oldConvertedFileWithSameNameID = destinationFolderId.getFilesByName(newFile.title).next().getId();

      // Delete before writing
      Drive.Files.remove(oldConvertedFileWithSameNameID);
    }

    // Create new converted file then get ID
    var newFileID = Drive.Files.insert(newFile, blob, {
      convert: true
    }).id;

    // Goal #2: I would like to push back the converted fileId into the google sheet to be displayed.
    // Add the ID of the converted file (added to column I)
    sheet.getRange(r,9).setValue(newFileID);
  }
}

Sample sheet:

Sample files:

First run (files):

First run (sheet):

Updated original files:

Run after updating original file (files):

Run after updating original file (sheet):

Note:

• This will retain the original file, but will replace the existing converted file of it everytime the script is run.
• I have used Drive services.

CodePudding user response：

i will post my sample sheet here:

This is my google sheet for listing purposes:

So since im using the code you shared on the above, im getting an error message as i mentioned in the comment. As you can see there, i have multiple of folders which contains files that i need to be converted into Google sheet(which will populate at column I and J).

This is the example of one of the folder which contains excel file:

after running the script this file need to be converted into a google sheet and store in the same folderid. However, if the user runs the script for second time, i would like to replace the previous google sheet file with the latest one(if they have the same name). Same goes to other folders as well, all the files in those folders will go through the same process. If the name is not same it will basically populate into a separate google sheet file.

After running your code:

Im getting this error telling that GoogleJsonResponseException: API call to drive.files.insert failed with error: File not found:, line 42

!! For your information, there is only excel file(the original/not converted version) and there is no any google sheets file in the folders

How can i solve this?