How to overwrite the same sheet with new data after updating a new csv file to G drive using Google Apps Script

Question

Firstly, after uploading a new .csv file, i execute the script. Then the .csv file will convert to Google Sheets in a Google Drive folder. The problem I am facing right now is when i delete the .csv file and upload a another .csv file, it is creating a new sheet. How can I update the same sheet after i upload a new .csv file?

So far, this code will convert whatever .csv file to sheets in the folder. I am not sure how to overwrite the sheet when I reupload another .csv file with same name. i dont want the script to create another sheet when i upload a new .csv file

function convert() {
  var folder = DriveApp.getFolderById('');
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    Drive.Files.copy({}, file.getId(), {convert: true});
  }
} 

I want the .csv file that I have uploaded to overwrite the existing sheet in Google Drive

Answer 1

• CSV files and Google Spreadsheet files are put in the same folder.
• Filename of CSV file has the extension of .csv. But the filename of Spreadsheet has not extension. For example, you want to use the filenames of sample.csv of CSV file and sample of Spreadsheet as the same filename.
• When there is only one filename of CSV file in the folder, you want to convert CSV file to Spreadsheet as new Spreadsheet.
• When there is the same filename of CSV file and Spreadsheet in the folder, you want to overwrite the CSV file to the existing Spreadsheet.
• After creating and overwriting files are done, you want to delete all CSV files in the folder.

I could understand like above. If my understanding is correct, how about this modification? The flow of modified script is as follows. Please think of this as just one of several answers.

Flow:

1. Retrieve all files in the specific folder.
2. Create an object for processing files.
   • In this case, {create: [], overWrite: [], deleteCsvFiles: []} is created.
3. If there are files for converting to new Spreadsheet, those CSV files are converted to new Spreadsheet.
4. If there are files for overwriting the existing Spreadsheet, the existing Spreadsheet files are overwritten by the CSV files.
5. All CSV files in the folder are deleted.

Modified script:

Before you run the script, please set the variable of folderId. And then, please confirm whether Drive API is enabled at Advanced Google Services, again.

function convert() {
  var folderId = "###"; // Please set the folder ID here.

  // Retrieve all files in the folder
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var fileList = [];
  while (files.hasNext()) {
    var file = files.next();
    fileList.push({
      id: file.getId(),
      name: file.getName().split(".")[0],
      mimeType: file.getMimeType()
    });
  }

  // Create an object for processing files
  var temp = {};
  var res = fileList.reduce(function(o, e, i) {
    if (e.mimeType == MimeType.CSV) o.deleteCsvFiles.push(e.id);
    if (e.name in temp) {
      if (temp[e.name].length == 2) throw new Error("Error");
      if (fileList[temp[e.name][0]].mimeType != e.mimeType) {
        o.overWrite.push({
          from: e.mimeType == MimeType.CSV ? e.id : fileList[temp[e.name][0]].id,
          to: e.mimeType == MimeType.GOOGLE_SHEETS ? e.id : fileList[temp[e.name][0]].id,
        });
      } else {
        throw new Error("Error");
      }
      temp[e.name].push(i);
    } else {
      temp[e.name] = [i];
    }
    if (i == fileList.length - 1) {
      o.create = Object.keys(temp).reduce(function(ar, e) {
        if (temp[e].length == 1 && fileList[temp[e][0]].mimeType == MimeType.CSV) {
          ar.push(fileList[temp[e][0]]);
        }
        return ar;
      }, []);
    }
    return o;
  }, {create: [], overWrite: [], deleteCsvFiles: []});

  // Create new Spreadsheet from CSV file
  if (res.create.length > 0) {
    res.create.forEach(function(e) {
      Drive.Files.copy({}, e.id, {convert: true});
    });
  }

  // Overwrite Spreadsheet by CSV file
  if (res.overWrite.length > 0) {
    res.overWrite.forEach(function(e) {
      var mediaData = DriveApp.getFileById(e.from).getBlob();
      Drive.Files.update({}, e.to, mediaData);
    });
  }

  // // Delete CSV files
  // if (res.deleteCsvFiles.length > 0) {
  //   res.deleteCsvFiles.forEach(function(e) {
  //     Drive.Files.remove(e);
  //   });
  // }
}

Note:

• This modified script supposes that the files of the same filename are only 2 files. When there are more than 3 files of the same filename, an error is thrown.
• When there are the files of the same filename and same mimeType, an error is thrown.
• When the file size of CSV file is large, the error might occur.
• In the current stage, I commented out the script of "Delete CSV files". Because when this script is run, the CSV files are deleted. When you use this script, please be careful.

References:

• Advanced Google Services
• Files: update
• Files: delete