https://www.pbainbridge.co.uk/2023/06/bulk-duplicate-google-drive-files.html

Bulk duplicate Google Drive files.gs

/*
Some Global Variables to make it easier to keep a track of changing column
positions, etc.
*/

// 'File Link' column number
var fileLinkCol = 2;

// Maximum runtime of script in minutes to prevent timeout (5 minutes)
var maxRuntime = 5 * 60 * 1000;


/*
This overall script is designed to bulk copy a Drive file and give it a unique
name from data within the Google Sheet.
*/

function getSpreadsheetData() {

  try {

    // Log starting of the script
    logEvent('Script has started');

    // create error variable to use to Flag any failures
    var errorFlag = false;

    // get current spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // display Toast notification
    toastPopup('Script has now started', 'Start');


    // get Welcome sheet
    var welcomeSheet = ss.getSheetByName('Welcome');

    // get Google Folder ID for storing created Files in
    var destinationFolderId = welcomeSheet.getRange(5, 2).getValue();

    // get Drive File ID for the item that will be copied
    var fileId = welcomeSheet.getRange(9, 2).getValue();


    // get Files sheet
    var filesSheet = ss.getSheetByName('Files');

    // get all data as a 2-D array
    var data = filesSheet.getDataRange().getValues();

    // create a name:value pair array to send the data to the next Function
    var spreadsheetData = { filesSheet: filesSheet, destinationFolderId: destinationFolderId, fileId: fileId, data: data };


    // run Function to copy Drive Files
    errorFlag = createFiles(spreadsheetData);


    // determine final outcome
    if (errorFlag == false) {

      // display popup message
      var popupTitle = "Completed";
      var popupMessage = "Tool successfully completed without errors."
      htmlPopup(popupTitle, popupMessage);

      // log event
      logEvent("Script completed successfully");

    } else if (errorFlag == "runtimeReached") {

      // display popup message
      var popupTitle = "Runtime Reached";
      var popupMessage = "This tool has met/exceeded its runtime. If you still have files to copy then please select to run it again and it will continue from where it left off."
      htmlPopup(popupTitle, popupMessage);

    } else {

      // problems occurred

      // log event
      logEvent("Script completed with errors");

    };

  } catch (err) {
    // log event
    logEvent("Error in 'getSpreadsheet' Function: " + err.stack);

    // run Function to launch HTML popup
    var popupTitle = "'getSpreadsheet' Function error";
    var popupMessage = "Problem with 'getSpreadsheet' Function: " + err.stack;
    htmlPopup(popupTitle, popupMessage);
  };

}


/*
This Function loops through each row and initiates the copy of the Drive file.
*/

function createFiles(spreadsheetData) {

  try {

    // Log starting of the function
    logEvent("Starting 'createFiles' Function");

    // create error variable to use to Flag any failures
    var errorFlag = false;

    // get start time so can manage duration of script and avoid timeout
    var startTime = new Date().getTime();

    // extract data from name:value pair array
    var filesSheet = spreadsheetData['filesSheet'];
    var destinationFolderId = spreadsheetData['destinationFolderId'];
    var fileId = spreadsheetData['fileId'];
    var data = spreadsheetData['data'];

    // get last Row number
    var lastRow = filesSheet.getLastRow();


    // create counter variable for number of successful files created
    var filesCounter = 0;

    // create empty array for pushing file Urls into to append to Sheet at very end
    var fileUrls = [];


    // get destination Folder
    var destinationFolder = DriveApp.getFolderById(destinationFolderId);

    // get file to be copied
    var templateFile = DriveApp.getFileById(fileId);


    // start of loop to go through each row in turn ********************************
    for (var i = 1; i < lastRow; i++) {

      // extract values from row of data so easier to work with
      var fileName = data[i][0];
      var fileLink = data[i][1];


      // check file Link column is empty before proceeding, so no existing file
      if (fileLink == '') {

        // display Toast notification
        toastPopup(fileName, 'Creating File');


        // create new file copy in destination
        var newFile = templateFile.makeCopy(fileName, destinationFolder);

        // get new Drive File Url
        var newFileUrl = newFile.getUrl();


        // check new file created successfully
        if (newFileUrl) {
          // increment file creation counter by '1'
          filesCounter++;

          // create nice clickable link using File Url
          var newfileLink = '=HYPERLINK("' + newFileUrl + '","' + fileName + '")';

          // push file Url into array for later adding to Sheet
          fileUrls.push([newfileLink]);
        } else {
          // error has occurred, popup message will already be displayed

          // new file not created successfully, return error flag
          return true;
        };

      } else {
        // 'File Link' column not empty so do nothing
      };



      // *************************** perform runtime check ***************************
      // get current time
      var endTime = new Date().getTime();

      // find elapsed time by subtracting from start time
      var elapsedTime = endTime - startTime;

      // check against maximum runtime
      var timeLimitExceeded = elapsedTime >= maxRuntime;


      // check status of runtime
      if (timeLimitExceeded) {

        // runtime has been met/exceeded
        logEvent('Runtime has been met/exceeded.');

        // set errorFlag and break out of loop
        errorFlag = "runtimeReached";
        break;

      } else {
        // runtime has not been met/exceeded, script can continue looping through files
      };
      // *************************** perform runtime check ***************************



    }// end of loop to go through each row in turn **********************************


    // check value of file creation counter to send anonymous metrics
    if (filesCounter > 0) {

      // new files were created

      // now append file/row data to 'Files' sheet in one go
      var lastRow = filesSheet.getRange("B1:B").getValues().filter(String).length;
      logEvent("lastRow in Column B is: " + lastRow);
      var arrayLength = fileUrls.length;
      var arrayWidth = fileUrls[0].length;
      filesSheet.getRange(lastRow + 1, 2, arrayLength, arrayWidth).setValues(fileUrls);

    } else {
      // do nothing as no new files created
    }

    // return errorFlag
    return errorFlag;

  } catch (err) {
    // log event
    logEvent("Error in 'createFiles' Function: " + err.stack);

    // run Function to launch HTML popup
    var popupTitle = "'createFiles' Function error";
    var popupMessage = "Problem with 'createFiles' Function: " + err.stack;
    htmlPopup(popupTitle, popupMessage);

    // return true as error
    return true;
  }


}


/*
Function to output messages to the 'Log' sheet.
Can be called anywhere else in script.
*/

function logEvent(action) {
  
  // get the user running the script
  var theUser = Session.getActiveUser().getEmail();
  
  // get the relevant spreadsheet to output log details
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('Log');
  
  // create and format a timestamp
  var dateTime = new Date();
  var timeZone = ss.getSpreadsheetTimeZone();
  var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
  
  // create array of data for pasting into log sheet
  var logData = [niceDateTime, theUser, action];
  
  // append details into next row of log sheet
  logSheet.appendRow(logData);
  
}


/*
This Function creates a menu item to run this script.
*/

function onOpen() {
  
  SpreadsheetApp.getUi()
  .createMenu('Admin')
  .addItem('Copy files', 'getSpreadsheetData') // label for menu item, name of function to run.
  .addToUi();  
  
}


/*
Display a modal dialog box with custom HtmlService content.
Does not suspend the script.
*/

function htmlPopup(popupTitle, popupMessage) {

  var htmlOutput = HtmlService
    .createHtmlOutput(popupMessage)
    .setWidth(360)
    .setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);

}


/*
Function to create a Toast Popup notification with customised message.
Created as standalone so can be called from anywhere else within Project.
*/

function toastPopup(msg, title) {
  
  // get Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // create Toast Popup
  ss.toast(msg, title);
  
}