Import new (latest) .csv file into existing Google spreadsheet automatically

Question

I receive a new CSV file every hour in my Google Drive. I need my spreadsheet updated with the data in the latest CSV file after it has been received in the Google Drive folder.

The files coming into the folder has a unique name for each new one according to date and time. For example: FileName_date_24hourtime.csv FileName_20190524_1800.csv then FileName_20190524_1900.csv etc.

Firstly I'm not sure what the best approach is:

simply with a formula (probably not possible with not knowing the exact filename?) like =IMPORTDATA
a google script to find latest .csv file and automatically import as soon as file was added to Google Drive folder

Any assistance will be great!

The .csv file: .csv file contains 28 rows and data should be split by ; .csv file looks like this: NAME;-63.06;-58.08;50.62;-66.67;-80.00 NAME;-61.82;-56.83;-50.55;-77.78;-70.00 NAME;-57.77;-50.21;52.88;-77.78;-70.00 NAME1;-57.69;-61.48;-55.59;-55.56;-60.00 NAME2;-61.62;-53.79;50.34;-66.67;-70.00 NAME3;-54.62;-54.57;-52.22;55.56;-60.00 ... with total of 28 rows

Data should go to "Import_Stats" sheet.

Oleg Valter Oleg Valter · Accepted Answer · 2019-05-25T13:39:22

The best approach here would be a script with a trigger that runs a function that performs data import to a spreadsheet.

Create a time-based trigger with 1-hour offset:

function trigger() {
  var trg = ScriptApp.newTrigger('checkFiles');
      trg.timeBased().everyHours(1).create();
}

Create function that checks files in a folder (e.g. "checkFiles").

function checkFiles(alreadyWaited) {
  //get spreadsheet and sheet;
  var id = 'yourSpreadsheetId';
  var ss = SpreadsheetApp.openById(id);
  var sh = ss.getSheetByName('Import_Stats');    

  var folderId = 'yourIdHere'; //folder by id is the simplest way;

  //get folder and files in it;
  var folder = DriveApp.getFolderById(folderId);
  var files       = folder.getFilesByType('text/csv');
  var filesImport = folder.getFilesByType('text/csv'); //fetch files again;

  //try to fetch number of files;
  var scriptProps = PropertiesService.getScriptProperties();
  var numFiles = scriptProps.getProperty('numFiles');

  //count current number of files;
  var numCurr = 0;
  while(files.hasNext()) {
  var f = files.next();
  numCurr++;
  }

  //if this is the first time, save current number;
  if(numFiles===null) { 
    scriptProps.setProperty('numFiles',numCurr);
  }else {
    numFiles = parseInt(numFiles);
  }

  if(numFiles===null||numFiles===(numCurr-1)) {

    //get today and reset everything except hours;
    var today = new Date();
        today.setMinutes(0);
        today.setSeconds(0);
        today.setMilliseconds(0);      

    //iterate over files;
    while(files.hasNext()) {
      var file = files.next();

      //get file creation date and reset;
      var created = file.getDateCreated();
          created.setMinutes(0);
          created.setSeconds(0);
          created.setMilliseconds(0);

      //calculate offset, equals 0 for each file created this hour;
      var offset = today.valueOf()-created.valueOf();

      if(offset===0) {
        //perform data import here;
        var data = file.getBlob().getDataAsString();

        //ignore empty files;
        if(data!=='') { 
          //split data in rows;
          var arr = data.split('\r\n');

          //resplit array if only one row;
          if(arr.length===1) {
            arr = data.split('\n');
          }

          //append rows with data to sheet;
          arr.forEach(function(el){
            el = el.split(';');
            sh.appendRow(el);
          });
        }          
      }

    }

  }else {
    //if never waited, set minute to wait, else add minute;
    if(!alreadyWaited) {
      alreadyWaited = 60000;
    }else {
      alreadyWaited += alreadyWaited;
    }

    //if waited for 10 minutes -> end recursion;
    if(alreadyWaited===600000) {
      Logger.log('Waited 10 minutes but recieved no files!');
      return;
    }

    //wait a minute and recheck;
    Utilities.sleep(60000);
    return checkFiles(alreadyWaited);
  }      
}

And this is what should happen:

Import new (latest) .csv file into existing Google spreadsheet automatically

1 Answers