I have multiple google spreadsheets that I want to combine into one master file. I find a different reference from here but nothing is working with my case so far. This is the attempt code that I do:

function compileSheets() {
    
  var dataSourceWorkbook = SpreadsheetApp.getActive()
    
  //Open Sheet "Link List" and pull the data inside the script
  //Sample of link source can be found here: spreadsheetID 1Nua_Lhcnjec8w34hnL8kZsdeDqqkggqo63pY_pxaRy0
    
  var linkSourceSheet = dataSourceWorkbook.getSheetByName('Links')
  var linkSource = linkSourceSheet.getDataRange().getDisplayValues() //this contain links of spreadsheets that I want to compile
    
  //compile file --> to restore all the multiple spreadhseet into one
  var compilefile = SpreadsheetApp.openById('12zj2-wlBXi6Rd18nUQMiB-dY-3xz10IQLxeehNPlXeQ') //I provide a sample spreadsheet for reference
  var compilefilesheet = compilefile.getSheetByName('Compiled')
      
  var compiledData = []
    
  for(row in linkSource){ 
  //I create a case, if the report status is 'Updated' I will compile the spreadsheet
    
  if (linkSource[row][3]=="Report Updated") {
        
 //open report by URL
 var report = SpreadsheetApp.openByUrl(linkSource[row][2])
    
 //get 2d list
 var updatedreport = report.getSheetByName('Sheet1')
 var reportvalues = updatedreport.getRange(2, 1, updatedreport.getLastRow(), 16).getValues()

 //merge multiple 2d list into one
 compiledData.concat(reportvalues)
    
          
  }
}
    
  //print to compile spreadsheet
  compilefilesheet.clear()
    
  if(compiledData.length){
  compilefilesheet.getRange(2, 1, compiledData.length, compiledData[0].length).setValues(compiledData);
    
        
   }
      
 }

the problem with the code above, I've successfully pulled the 2D list, however:

1. I couldn't remove the blank rows as each report have blank rows.
2. I failed when merging the data with concat
3. I failed when printing the data to the compile sheet

Do you guys have idea how to fix this? Thank you in advance!

For reference: Link to link source: link Link to compile sheet: link

CodePudding user response：

In your script, how about the following modification?

From:

compiledData.concat(reportvalues)

To:

compiledData = compiledData.concat(reportvalues);

and

compiledData = [...compiledData, ...reportvalues];

Reference:

• concat()

Additional information:

As an additional information, when you want to remove the empty rows, you can also modify as follows.

From:

compiledData.concat(reportvalues)

To:

compiledData = compiledData.concat(reportvalues.filter(r => r.join("") != ""));

and

compiledData = [...compiledData, ...reportvalues.filter(r => r.join("") != "")];