https://www.pbainbridge.co.uk/2022/12/bulk-combine-csv-files-into-single.html

Bulk combine CSV files into a single Google Sheet.gs

function createSheets() {


  try {

    // log event
    logEvent("Starting 'createSheets' Function");


    // get the 'Welcome' Sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var welcomeSheet = ss.getSheetByName('Welcome');


    // create Toast notification to inform user
    ss.toast('', 'Starting ...');


    // get folder containing CSV files and student data
    var studentDataFolderId = welcomeSheet.getRange(5, 4).getValue();
    var studentDataFolder = DriveApp.getFolderById(studentDataFolderId);

    // get destination folder to create new Attendance Sheets in
    var destinationFolderId = welcomeSheet.getRange(9, 4).getValue();
    var destinationFolder = DriveApp.getFolderById(destinationFolderId);

    // get template Google Sheet for copying
    var templateId = welcomeSheet.getRange(13, 4).getValue();
    var templateSheet = DriveApp.getFileById(templateId);



    // make copy of Attendance Sheet template and get its ID
    var attendanceSheetFile = templateSheet.makeCopy(destinationFolder);
    var attendanceSheetFileID = attendanceSheetFile.getId();

    // open new Attendance Sheet for editing
    var attendanceSheetOpen = SpreadsheetApp.openById(attendanceSheetFileID);

    // get TEMPLATE Sheet for later duplicating
    var sheetTemplate = attendanceSheetOpen.getSheetByName('TEMPLATE');

    // get Term Time
    var termTime = sheetTemplate.getRange(1, 12).getValue();
    Logger.log('termTime is: ' + termTime);



    // loop through CSV files **********************************

    // get all files in folders
    var files = studentDataFolder.getFiles();

    // work through each file
    while (files.hasNext()) {

      // get next file
      var file = files.next();

      // get file name
      var fileName = file.getName();
      Logger.log('filename is: ' + fileName);


      // create Toast notification to inform user of progress
      ss.toast(fileName, 'File');


      // split file name for extracting key info
      var fileNameSplit = fileName.split(" - ");

      // get module mode from file name
      var moduleCode = fileNameSplit[0];

      // get tutor name from file name
      var tutorName = fileNameSplit[1];

      // get group number from file name
      var groupNumber = fileNameSplit[2];


      // get all data from CSV file
      var fileData = Utilities.parseCsv(file.getBlob().getDataAsString());


      // create empty array for pushing Group data into
      var groupData = [];

      // extract Activity & push into array for later pasting
      var activityName = fileData[0].toString();
      activityName = activityName.split(",")[1];
      groupData.push([activityName]);

      // extract Activity Reference & push into array for later pasting
      var activityRef = fileData[1].toString();
      activityRef = activityRef.split(",")[1];
      groupData.push([activityRef]);

      // extract Activity Type & push into array for later pasting
      var activityType = fileData[2].toString();
      activityType = activityType.split(",")[1];
      groupData.push([activityType]);

      // extract Module Name & push into array for later pasting
      var moduleName = fileData[3].toString();
      moduleName = moduleName.split(",")[1];
      groupData.push([moduleName]);

      // extract Start Date & push into array for later pasting
      var startDate = fileData[4].toString();
      startDate = startDate.split(",");
      startDate = startDate[1] + " " + startDate[2];
      groupData.push([startDate]);

      // extract End Date & push into array for later pasting
      var endDate = fileData[5].toString();
      endDate = endDate.split(",");
      endDate = endDate[1] + " " + endDate[2];
      groupData.push([endDate]);


      // create name for new Sheet tab
      var newSheetName = moduleCode + " " + tutorName + " " + groupNumber;

      // make copy of TEMPLATE Sheet tab
      var newSheet = attendanceSheetOpen.insertSheet(newSheetName, { template: sheetTemplate });


      // put tutor name into Google Sheet cell
      newSheet.getRange(1, 1).setValue(tutorName);


      // append Group data into sheet in one go
      var arrayLength = groupData.length;
      var arrayWidth = groupData[0].length;
      newSheet.getRange(1, 2, arrayLength, arrayWidth).setValues(groupData);


      // loop through rest of data array to populate students ***************

      var studentNames = [];
      var studentEmails = [];

      for (var i = 8; i < fileData.length; i++) {

        // get student name from data array
        var studentName = fileData[i].toString();
        studentName = studentName.split(",");
        studentName = studentName[0] + "," + studentName[1];

        // push name into array
        studentNames.push([studentName]);


        // get student email address from data array
        var studentEmail = fileData[i].toString();
        studentEmail = studentEmail.split(",")[2];

        // push emails into array
        studentEmails.push([studentEmail]);


      }
      // loop through rest of data array to populate students ***************


      // insert rows for inserting student data to
      var arrayLength = studentNames.length;
      newSheet.insertRows(9, arrayLength - 1);

      // append student names into sheet in one go
      var arrayWidth = studentNames[0].length;
      newSheet.getRange(9, 1, arrayLength, arrayWidth).setValues(studentNames);

      // append student email addresses into sheet in one go
      var arrayLength = studentEmails.length;
      var arrayWidth = studentEmails[0].length;
      newSheet.getRange(9, 2, arrayLength, arrayWidth).setValues(studentEmails);

    }

    // loop through CSV files **********************************



    // rename Attendance Sheet file now that completed and have data to work with
    attendanceSheetFile.setName(moduleCode + " - " + termTime + " - " + tutorName);

    // delete TEMPLATE' Sheet tab
    attendanceSheetOpen.deleteSheet(sheetTemplate);


    // run Function to sort Sheet Tabs into Ascending order
    var sortSheetsAscFlag = sortSheetsAsc(attendanceSheetFileID);


    // check previous Function ran successfully before proceeding
    if (sortSheetsAscFlag) {

      // create Toast notification to inform user
      ss.toast('Successfully finished', 'Completed');

      // log event
      logEvent("Successfully finished 'createSheets' Function");

    }
    else {

      // errors occured - do nothing as already actioned

    }

  }
  catch (error) {

    // error getting Google Drive folder
    logEvent("Error with 'createSheets' Function" + error);

    // create popup to inform user
    var popupTitle = "Error with 'createSheets' Function";
    var popupMessage = "Error message: " + error;
    htmlPopup(popupTitle, popupMessage);

  }


}


/*
Organise Google Sheet Tabs in Ascending order.
*/

function sortSheetsAsc(attendanceSheetFileID) {


  try {

    // log event
    logEvent("Starting 'sortSheetsAsc' Function");


    // open Google Sheet
    var ss = SpreadsheetApp.openById(attendanceSheetFileID);

    // get array of Sheets (Tabs)
    var sheets = ss.getSheets();

    // get length of array of Sheets
    var sheetsLength = sheets.length;

    // create empty array to push Sheet names into
    var sheetNameArray = [];


    // loop through each Sheet (Tab) *************************
    for (var i = 0; i < sheetsLength; i++) {

      // get Sheet name and push into array
      var sheetName = sheets[i].getName();
      sheetNameArray.push(sheetName);

    }
    // loop through each Sheet (Tab) *************************


    // use JavaScript 'sort' method to sort array alphabetically
    sheetNameArray.sort();


    // loop through each Sheet and physically reorder ****************
    for (var j = 0; j < sheetsLength; j++) {

      // get Sheet (Tab) name from array
      var sheetNameFromArray = sheetNameArray[j];

      // get Sheet (Tab)
      var singleSheet = ss.getSheetByName(sheetNameFromArray);

      // set current Sheet (Tab) active ready for moving
      ss.setActiveSheet(singleSheet);

      // move Sheet (Tab) to new position
      ss.moveActiveSheet(j + 1);

    }
    // loop through each Sheet and physically reorder ****************


    // log event
    logEvent("Successfully finished 'sortSheetsAsc' Function");


    // return success flag
    return true;


  }
  catch (error) {

    // error getting Google Drive folder
    logEvent("Error with 'sortSheetsAsc' Function" + error);

    // create popup to inform user
    var popupTitle = "Error with 'sortSheetsAsc' Function";
    var popupMessage = "Error message: " + error;
    htmlPopup(popupTitle, popupMessage);

    // return fail flag
    return false;

  }


}


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

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Admin')
    .addItem('Start now', ',createSheets') // label for menu item, name of function to run.
    .addToUi();

}


/*
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);

}


/*
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);

}