App script: copy data from folder to single spreadsheet-CodePudding

I am working on a script to copy all the all data from sheets saved in a folder and concatenate them to a single spreadsheet.

The variable combinedData is not returning any data when I run it through the logger outside the while loop. Any ideas why this is happening?


// DATA CONCATINATION
function Data_concat() {
var folder =  DriveApp.getFolderById("1Rky5tOyrTMJ15uhTog1ltBMwONap7Rx1");
  
var filesIterator = folder.getFiles();

var file;
var filetype;
var ssID;
var combinedData = [];
var data;

while(filesIterator.hasNext()){

  file = filesIterator.next();
  filetype = file.getMimeType();
  if(filetype === "application/vnd.google-apps.spreadsheet"){
    ssID = file.getId();
    data = getDatafromSS(ssID);
    combinedData = combinedData.concat(data);

  }//end of if
  
}//end of while 

Logger.log(combinedData)

//var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("raw_Data");
//ws.getRange("A2:J").clearContent();
//ws.getRange(2,1,combinedData.length, combinedData[0].length).setValues(combinedData)

}//end of  Data_concat


//GET DATA FROM TARGET SHEET
function getDatafromSS(ssID){
  

  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheets()[0];
  var data = ws.getRange("A2:G"   ws.getLastRow()).getValues();

  return data;

}

CodePudding user response：

Save Data in all Spreadsheets in a folder

On Different Sheets:

function saveDataInAllSheetsInAFolder() {
  const tss = SpreadsheetApp.getActive();
  const tssnames = tss.getSheets().map(sh => sh.getName()).flat();
  const fldr = DriveApp.getFolderById("Folderid");
  const files = fldr.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.openById(file.getId());
    ss.getSheets().forEach((sh,i) => {
      let vs = sh.getDataRange().getValues();
      let nsh = tss.insertSheet(ss.getName()   "-"   sh.getName()   "-"   Utilities.formatDate(new Date(),tss.getSpreadsheetTimeZone(),"yyyyMMdd HH:mm:ss.S"));
      nsh.getRange(1,1,vs.length,vs[0].length).setValues(vs)
    })
  }
}

On Same Sheet:

function saveDataInAllSheetsInAFolder() {
  const tss = SpreadsheetApp.getActive();
  const nsh = tss.insertSheet(Utilities.formatDate(new Date(),tss.getSpreadsheetTimeZone(),"yyyyMMdd HH:mm:ss.S"));
  nsh.getRange(1,1).setValue("Data Sheet "   Utilities.formatDate(new Date(),tss.getSpreadsheetTimeZone(),"yyyyMMdd HH:mm:ss.S") );
  tss.setActiveSheet(nsh);
  const tssnames = tss.getSheets().map(sh => sh.getName()).flat();
  const fldr = DriveApp.getFolderById("Folderid");
  const files = fldr.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.openById(file.getId());
    ss.getSheets().forEach((sh,i) => {
      let vs = sh.getDataRange().getValues();
      nsh.getRange(nsh.getLastRow()   1,1,vs.length,vs[0].length).setValues(vs);
    })
  }
}