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();
}
Make a copy of sheet and clear form:
The script above makes only one copy of the sheet(filled form) in the destination folder.
Reference: