Google script to import CSV file to google sheet.

importCSV.js

function importCSVFiles()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  Logger.log(`Active Spreadsheet: ${ss.getName()}`);
  
  const rootFolderId = '1EDAXA7TbzCnzmczRMHD_LxoF0yopS5D2';                  // Root folder
  const csvFolderName = 'Data';                                              // Folder containing csv files and the Archive folder
  const archiveFolderId = '1clsKkp0bauYkbg0uQj8LfaHIQIrpJWO8';               // Archive Folder
  const destinationSheetName = 'final';                                      // destination sheet in spreadsheet
  
  const root = DriveApp.getFolderById(rootFolderId);
  Logger.log(`Root Folder: ${root.getName()}`);
  const archiveFolder = DriveApp.getFolderById(archiveFolderId);
  Logger.log(`Archive Folder: ${archiveFolder.getName()}`);
  
  const fileName =/ *.csv/;
  
  var rootFolders = root.getFolders();
  
  const sheet = ss.getSheetByName(destinationSheetName);
  Logger.log(`Sheet Name: ${sheet.getName()}`);
  
  var csvArray = [];
  
  while (rootFolders.hasNext()) 
  {
    var folder = rootFolders.next ();
    Logger.log(`folder.getName() ${folder.getName()}`);
    if (folder.getName() === csvFolderName) 
    {
      importCSV(folder);
    }
    else 
    {
      var csvFolder = folder.getFoldersByName(csvFolderName);
      while (csvFolder.hasNext()) 
      {
        importCSV(csvFolder.next());
      }
    }
  }
  
  function importCSV(folder) 
  {
    var files = folder.getFiles();
    while (files.hasNext()) 
    {
      var file = files.next();
      Logger.log(`file.getName() ${file.getName()}`);
      csvArray = file.getBlob().getDataAsString("UTF-8");
      csvArray = Utilities.parseCsv(csvArray.replaceAll(/"/g, '\\"'), ';');
      csvArray.shift();
      sheet.insertRowsAfter(1,csvArray.length);
      sheet.getRange (2,1,csvArray.length,csvArray[0].length).setValues(csvArray);
      
      file.moveTo(archiveFolder);
    }
  }
}

I have reviewed the variables multiple times and fail to correct it. Can you please guide me on this error?

Error
TypeError: Cannot read property 'getName' of null
importCSVFiles @ Code.gs:21

Well getName is used in multiple places.
You are going to need to check the console logs and see which one is giving u an error.
Just FYI sheet name refers to the sheet inside the spreadsheet and not the name of the whole spreadsheet.

Hey there, thanks so much for this!

I'm wondering if you'd know how I could also write the name of the .csv file in the next blank column (after parsing)? I have multiple files pulling into the sheet, which is awesome btw, but I also need to know which file the data sets came from.

Any help would be amazing and appreciated!

You are going to have to play around with the insert API's . This is all I know on this topic as this is the most that I needed. You already have the filename in line 48. So I guess you do another insert on line 55.

That's what I was playing around with. Maybe the answer will lie in the reference you linked. Thanks for the help!