google-apps-scriptgoogle-sheetsgoogle-drive-shared-drive

How to stop creating 2 files from an original file in Google Apps Script


I want the macro to save a copy from the original file that is a form in GSheets in a specific folder and to delete the cells for it to be used again. I think I've done it but the problem is that it creates 2 copies from the original, one with the cells deleted and another one with the form filled out. Here is the code:

// Call the function that activates the two functions

activateTwoFunctions();

//Call the two functions 'copyfileinDrive' and 'CleanForm'
function activateTwoFunctions() 
{
  // Call the first function

  copyfileinDrive();

  // Call the second function

  Cleanform();
}

//This function makes a copy of the original file and save it in an specific folder

function copyfileinDrive()
{
   var copySheet = DriveApp.getFileById('File ID'); // Original file
   var destinationFolder = DriveApp.getFolderById('Folder ID'); // Destination folder
   copySheet.makeCopy('New Reference Candidate',destinationFolder);
}

//This function clears the data entered by the user

function Cleanform() 
{
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D17').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E17').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F17').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G17').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D18').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E18').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F18').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G18').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D19').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E19').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F19').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G19').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D20').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E20').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F20').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G20').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D21').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E21').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F21').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G21').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D22').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E22').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F22').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G22').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D25').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D26').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D27').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D28').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D29').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D30').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D31').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D32').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D33').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E25').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E26').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E27').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E28').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E29').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E30').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E31').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E32').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('E33').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F25').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F26').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F27').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F28').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F29').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F30').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F31').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F32').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F33').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G25').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G26').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G27').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G28').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G29').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G30').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G31').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G32').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('G33').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H25').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H26').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H27').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H28').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H29').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H30').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H31').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H32').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('H33').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('D48').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('F48').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
  spreadsheet.getRange('C36:J45').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('C51:J55').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('L13').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A1').activate();
}

Solution

  • Make a copy of sheet and clear form:

    function copyfileinDrive() { 
    
    var copySheet = DriveApp.getFileById('fileID'); // Original file
    
    var destinationFolder = DriveApp.getFolderById('FolderID'); // Destination folder
    
    copySheet.makeCopy('New Reference Candidate',destinationFolder);
    
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    spreadsheet.getRangeList(['D17:G22','D25:H33','D48','F48']).setValue('FALSE');
    spreadsheet.getRangeList(['C36:J45','C51:J55','L13']).clear({ contentsOnly: true, skipFilteredRows: true });
    
    spreadsheet.getRange('A1').activate(); }
    

    The script above makes only one copy of the sheet(filled form) in the destination folder.

    Reference: