I have files on the folder and i import them to the google sheet. I need to add filename to first column before import, but my script add it only to first cell before import

So it gives me:

And i need

  function Import_Kredo() {
 
  var arr = [];
  var files = DriveApp.getFolderById("13kymSiqX0-L6a9ev4It1_BEjH-xM8rnc").getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Table 1");
  copySheet.getRange('A2:G').clear();
  while (files.hasNext()) {
    var file = files.next();
    var mimeType = file.getMimeType();
    var ss1 = null, id = null;
    if (mimeType == MimeType.MICROSOFT_EXCEL) {
      id = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS }, file.getId()).id;
      ss1 = SpreadsheetApp.openById(id);
    } else if (mimeType == MimeType.GOOGLE_SHEETS) {
      ss1 = SpreadsheetApp.open(file);
    }
    if (!ss1) continue;
    var sheet = ss1.getSheetByName("Table 1");
    if (!sheet) continue;
    var sheetsRange = sheet.getDataRange();
    var sheetsValues = sheetsRange.getValues().filter(r => r[1]).filter(e => e);
    var v = sheetsValues.slice(1);
    console.log([file.getName(), v])
    arr = arr.concat([[file.getName(), ...Array(v[0].length - 1).fill(null)], ...v]);
    if (id) DriveApp.getFileById(id).setTrashed(true); // or Drive.Files.remove(id);
  }
  copySheet.getRange(5, 1, arr.length, arr[0].length).setValues(arr);
}

CodePudding user response：

In your situation, how about the following modification?

From:

arr = arr.concat([[file.getName(), ...Array(v[0].length - 1).fill(null)], ...v]);

To:

var filename = file.getName();
arr = arr.concat(v.map(e => [filename, ...e]));

• By this modification, the filename is put into the 1st column of each row.