Using google script to import .csv files into an e

问题:

Is there a way to not duplicate the headers when the new .csv files are imported from my one drive folder into the existing google sheet? I want the .csv files to be added to the existing sheet in sequential order without adding the headers...example - like row 2 and 19 show. Also, sequential to make the dates go in order in column A. Another question I had was, do you know what happened in line 10? I have deleted and re entered the new data in and every time that happens. This is my script I have now. This is a shareable link to the sheet and what it looks like. https://docs.google.com/spreadsheets/d/1f9HEwikMxm5sJzzRh_-etBxXzL0NpK47i9LtoZVCv_0/edit?usp=sharing This is the script I have right now.

function appendingCSV() {
 var ss=SpreadsheetApp.getActiveSpreadsheet()
 var sht=ss.getActiveSheet();
 var drng = sht.getDataRange();
 var lastRow = drng.getLastRow();
 var data = loadFiles();
 var dataA =Utilities.parseCsv(data);
if(dataA.length>0)
{
 var rng = sht.getRange(lastRow + 1, 1, dataA.length, dataA[0].length);
 rng.setValues(dataA);
}
else
{
  SpreadsheetApp.getUi().alert('No Data Returned from LoadFiles');
}
}

function loadFiles(folderID)
{
 var folderID = (typeof(folderID) !== 'undefined')? folderID : 
 '0B8m9xkDP_TJxUUlueHhXOWJMbjg';
var fldr = DriveApp.getFolderById(folderID);
var files = fldr.getFiles();
 var s='';
 var re = /^.*\.csv$/i;
 while (files.hasNext())
 {
  var file = files.next();
  var filename = file.getName();
  if(filename.match(re))
  {
    s += file.getBlob().getDataAsString() + '\n';
    file.setName(filename.slice(0,-3) + 'old');
  }
 }
 return s;
}

function createTimeDrivenTriggers() {
// Trigger every Friday at 09:00.
 ScriptApp.newTrigger('myFunction')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.FRIDAY)
    .atHour(9)
    .create();
 }

回答1:

In your loadFiles() script. Try changing it to something like this.

function loadFiles(folderID)
{
  //var folderID = (typeof(folderID) !== 'undefined')? folderID : 'Your_folder_id';
  var folderID = (typeof(folderID) !== 'undefined')? folderID : 'Your_folder_id';
  var fldr = DriveApp.getFolderById(folderID);
  var files = fldr.getFiles();
  var s='';
  var re = /^.*\.csv$/i;
  while (files.hasNext())
  {
    var file = files.next();
    var filename = file.getName();
    if(filename.match(re))
    {
      s+=file.getBlob().getDataAsString().split('\n').splice(0,1).join('\n') + '\n';
      //s += file.getBlob().getDataAsString() + '\n';
      file.setName(filename.slice(0,-3) + 'old');
    }
  }
  return s;
}

You may have to play with this a little. I'm not sure if the last '\n' is needed or not and I'm not that great at chaining so many operations. But you need to remove the headers from each file. You could write a local script that you give to your techs that strips off the headers at the origin and in that case then go back to the way it is now.