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:
- I couldn’t remove the blank rows as each report have blank rows.
- I failed when merging the data with concat
- 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
>Solution :
In your script, how about the following modification?
From:
compiledData.concat(reportvalues)
To:
compiledData = compiledData.concat(reportvalues);
and
compiledData = [...compiledData, ...reportvalues];
Reference:
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("") != "")];