Skip to content

Instantly share code, notes, and snippets.

@blessanm86

blessanm86/importCSV.js

Created October 27, 2021 14:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save blessanm86/396b4c40cc44f03c16c43fab04bdda8a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save blessanm86/396b4c40cc44f03c16c43fab04bdda8a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. blessanm86 created this gist Oct 27, 2021.
    59 changes: 59 additions & 0 deletions importCSV.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    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);
    }
    }
    }