Here’s how the following code works:

  • gets data from the csv file as an array
  • inserts a number of rows after the header row
  • sets the array in the sheet
  • moves the csv files to an archive folder after importing them so they are not imported again
  • then moves to the next file until there are no more files to import

You will need to create an archive folder for the csv files to be stored in after they have been imported. I generally make one inside the folder containing the csv folders for this purpose.

In the code, you will need to provide the folder IDs for the archive folder and the root folder (folder that contains your CSV folder) and the name of the destination sheet in your spreadsheet. These can all be set in the first four variable in the code. (I already set csvFolderName to ‘CSVfolder’ per the information you provided above.

After saving this code, create a trigger on it to run on open.

Here is the code:

function importCSVFiles()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  const csvFolderName = 'CSVfolder';                                       // Folder containing csv files and the Archive folder
  const rootFolderId = 'id_of_folder_containing_CSVfolder';                // Root folder
  const archiveFolderId = 'id_of_archiveFolder';                           // Archive Folder
  const destinationSheetName = 'name_of_destination_sheet_in_spreadsheet'; // destination sheet in spreadsheet
  
  const root = DriveApp.getFolderById(rootFolderId);
  const archiveFolder = DriveApp.getFolderById(archiveFolderId);
  
  const fileName =/ *.csv/;
  
  var rootFolders = root.getFolders();
  
  const sheet = ss.getSheetByName(destinationSheetName);
  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);
      csvArray.shift();
      sheet.insertRowsAfter(1,csvArray.length);
      sheet.getRange (2,1,csvArray.length,csvArray[0].length).setValues(csvArray);
      
      file.moveTo(archiveFolder);
    }
  }
}

If it seems like you are having issues with the delimiter, you may need to set it specifically as a custom delimiter by substituting the above line of code csvArray = Utilities.parseCsv(csvArray); for csvArray = Utilities.parseCsv(csvArray,';');

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top