https://www.pbainbridge.co.uk/2022/10/import-csv-data-into-google-sheet-files.html

Import CSV data into Google Sheet files.gs

/*
The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.
*/

function createSheets() {


  // get template Google Sheet for copying
  var templateSheet = DriveApp.getFileById('1Rj-TGRW01kx7TEVU-nXywn0yJ1GC96pgegWnuJ0Vtag');

  // get folder containing CSV files and data
  var csvFolder = DriveApp.getFolderById('1c5qw95r5-7i3licvjQsQ3umKYgG7uOTg');

  // get destination folder to create new Google Sheets in
  var destinationFolder = DriveApp.getFolderById('1HK1walbsSJj52utTGb8a6D0McjqX-jLH');


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

  // get all files in folders
  var files = csvFolder.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);


    // 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 Reference & push into array for later pasting
    var activityRef = fileData[0].toString();
    activityRef = activityRef.split(",")[1];
    groupData.push([activityRef]);

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

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

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

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


    // make copy of Google Sheet template and get its ID
    var newSheetFile = templateSheet.makeCopy(activityType, destinationFolder);
    var newSheetFileID = newSheetFile.getId();


    // open new Attendance Sheet for editing
    var newSheetOpen = SpreadsheetApp.openById(newSheetFileID);

    // get first Sheet
    var sheet1 = newSheetOpen.getSheets()[0];


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


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

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

    for (var i = 7; 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;
    sheet1.insertRows(8, arrayLength - 1);

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

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

  }

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




}