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

79 views Asked by At

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();
}
2

There are 2 answers

2
Serenity On BEST ANSWER

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:

0
Cooper On

Try this:

function copyAndClean() {
  const copySheet = DriveApp.getFileById('fileid'); // Original file
  const destinationFolder = DriveApp.getFolderById('folderid'); // Destination folder
  copySheet.makeCopy('New Reference Candidate', destinationFolder);
  const ss = SpreadsheetApp.getActive();
  ss.getCurrentCell().setValue('FALSE');
  const sh = ss.getActiveSheet();
  sh.getRange("D17:G22").setValue("FALSE");
  sh.getRange("D25:H33").setValue("FALSE")
  sh.getRange('D48').setValue("FALSE");
  sh.getRange('F48').setValue("FALSE");
  sh.getRange('C36:J45').clear({ contentsOnly: true, skipFilteredRows: true });
  sh.getRange('C51:J55').clear({ contentsOnly: true, skipFilteredRows: true });
  ss.getRange('L13').clear({ contentsOnly: true, skipFilteredRows: true });
  ss.getRange('A1').activate();
  SpreadsheetApp.flush();
}

I only get one copy