App script to export the Gsheets to CSV and replace the existing files in the same folder

Question

I am using the below app script to export Gsheets to CSV, however evertime i use the below code it created new folder/files on Gdrive however what i want to do is to update the exsisting CSV files on the same folder or a specific folder without creating new files/folders:

Used Code:

/*
 * script to export data in all sheets in the current spreadsheet as individual csv files
 * files will be named according to the name of the sheet
 * author: Michael Derazon
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}];
  ss.addMenu("csv", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // create a folder from the name of the spreadsheet
 var folder = DriveApp.getFileById(ss.getId()).getParents().next();
  for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".csv" extension to the sheet name
    fileName = sheet.getName() + ".csv";
    // convert all available sheet data to csv format
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the csv data
    folder.createFile(fileName, csvFile);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

Expecting to update the exsisitng files everytime i push update not to create a new one unless a new sheet is added to the Gsheet

Answer 1

I believe your goal is as follows.

• You want to create a new file when the file of the same filename does not exist in folder.
• You want to update the file when the file of the same filename is existing in folder.

In this case, how about the following modification? In this modification, your function convertRangeToCsvFile_ is used. And, please modify saveAsCSV() as follows.

Modified script:

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.getFileById(ss.getId()).getParents().next();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    // append ".csv" extension to the sheet name
    fileName = sheet.getName() + ".csv";
    // convert all available sheet data to csv format
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the csv data

    // --- I modified the below script.
    var files = folder.getFilesByName(fileName);
    var file = files.hasNext() ? files.next() : folder.createFile(fileName, "", MimeType.CSV);
    file.setContent(csvFile);
    // ---

  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}

• By this modification, when the file of the same filename is existing in folder, the existing file is overwritten by a new value of csvFile. So, the file ID of the existing file is not changed.

• If you want to remove the existing file and create a new file by changing the file ID, please modify the above script as follows.

  • From

    var files = folder.getFilesByName(fileName);
    var file = files.hasNext() ? files.next() : folder.createFile(fileName, "", MimeType.CSV);
    file.setContent(csvFile);
    

  • To

    var files = folder.getFilesByName(fileName);
    if (files.hasNext()) {
      files.next().setTrashed(true);
    }
    folder.createFile(fileName, csvFile);
    

Note:

• I think that your goal can be also achieved using Drive API. But, from your showing script, I proposed the modification using Drive service (DriveApp) instead of Drive API.

Reference:

• getFilesByName(name)