Google Script concat severaly arrays of arrays

Question

I am struggling with the following task: I have an folder with 20 spreadsheets that have all a datatable with the same format (same columns). I want to loop through all of them, collect the data, combine it to one big array and display it on a spreadsheet.

However, I am struggling to combine the arrays. In the first step I load the column headers from the final sheet. Afterwards I loop through all files (I have a table with the sheets ID stored in the array aFiles) but I cannot combine the arrays. I tried it with aData.concat but it didn't do anything.

function getInformation(){

var ssZ = SpreadsheetApp.getActiveSpreadsheet();
var sZu = ssZ.getSheetByName("Meldeeinheiten");
var aFiles = sZu.getDataRange().getValues();
var sDa = ssZ.getSheetByName("Data_komplett")

var aData = sDa.getRange(1, 1, 1, 15).getValues()

for (var iFile = 1; iFile<aFiles.length; iFile ++){
     var org = aFiles[iFile][0];
     var name = aFiles[iFile][1]; 
     var id= aFiles[iFile][2]; 

     var ssI = SpreadsheetApp.openById(id);
     var sData = ssI.getSheetByName("Data");
     var lRow = sData.getLastRow();
     if (lRow >= 2){
         var aNew =[];
         aNew = sData.getRange(2, 1, sData.getLastRow(), 15).getValues();
         aData.concat(aData,aNew);
          }
     }
  var lDRow = sDa.getLastRow();
  sDa.getRange(2, 1, lDRow , 15).clear()

  var rng = sDa.getRange(1, 1, aData.length, 15);
  rng.setValues(aData);
  Logger.log(aData.length)

  }

The data in the tables is strucutred in the following way:

Spreadsheet A:
Org   Name    Hours   Comment
A     Joe     15      Weekend
A     Pete    20      Sunday
A     Maik    15      test

Spreadsheet B
Org   Name    Hours   Comment
B     Will    15      Monday
B     Anna    18      holiday
B     Dave    10      test

...

And so one.

Has anybody an idea how I can combine those data and create a "joint database"?

I have to apologize for my poor English skill. I cannot understand about your question. So can I ask you about it? 1. Can you provide the result you expect? 2. What is "Spreadsheet A" and "Spreadsheet B" in below table? 3. What is "joint database"? — Tanaike
– Tanaike, Commented Mar 31, 2020 at 22:11

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Issue:

aData.concat(aData,aNew);

Array.concat doesn't concat in-place. It returns the concatenated array.

Solution:

Use the returned array:

aData = aData.concat(aData,aNew);

Alternatively use flatMap,

const out = aFiles.flatMap(([org, name, id])=>
SpreadsheetApp.openById(id)
  .getSheetByName('Data')
  .getDataRange()//TODO Modify range according to last row
  .getValues())

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 1, 2020 at 2:37

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Script concat severaly arrays of arrays

1 Answer 1

Issue:

Solution:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Issue:

Solution:

Comments

Your Answer

Sign up or log in

Post as a guest

Related