Merge multiple Google Sheet into one master logging sheet

Question

We have google spreadsheet with recorded data by people and by day following this format.

Here you have Bob's activities: and Robert's activities:

Each dude has a spreadsheet with his name and it is possible there is no activity on one day.

I would like to create a master logging sheet with activities of all people following this format:

Here you can find a link to this spreadsheet: https://docs.google.com/spreadsheets/d/1cgbWgWDe9Ec1t6Af33ICZObQwiJJyLHxOmFJDCoY6As/edit?usp=sharing

I would like this "master sheet" to be autogenerated and dynamique if a change appears in one of the people activities. So I guess I will need Scripts ?

Yet I cannot find a way to do it...

Will you be doing anything with the data once it is merged? Even changing the order of items? — Karl_S
I do not need to edit by hand the "master sheet" but I will have reference to it on another sheets. Such as counting quantity per type per people or quantity per type per date PS: by default I would like the master sheet sorted by ascending date — Matthieu

Karl_S Karl_S · Accepted Answer · 2017-03-27T21:12:50

I do not have much time to explain this, but the below formula will add a column for "Bob" and "Robert" and to each set of data and join both sets as an array, return it to the query and sort the result by the date column. There are differences between regions and your sample sheet wanted me to use ; when normally use , and \ where I wold use , in the array definition: { range1 , range2 } vs { range1 \ range2 }

=QUERY(
      {ARRAYFORMULA({IF(ISBLANK(Bob!A3:A);;"Bob") \Bob!A3:G});
      ARRAYFORMULA({IF(ISBLANK(Robert!A3:A);;"Robert") \Robert!A3:G})};
      "select * where Col1 <> '' order by Col2")

Put that in cell A2 and you shouyld be good. You can use IMPORTRANGE() to bringhte data in from different sheets as well.

Edit: In the answer above I missed that the data has 3 sets of values across each row which the OP desires to be stacked. To do this we nest query() functions for the initial data and return the appropriate columns in each:

=QUERY(
 {
  QUERY(
   {
    ARRAYFORMULA({IF(ISBLANK(Bob!A3:A);;"Bob") \Bob!A3:G});
    ARRAYFORMULA({IF(ISBLANK(Robert!A3:A);;"Robert") \Robert!A3:G})
   };
   "select Col1, Col2, Col3, Col4 where Col1 <> ''"; 0);
  QUERY(
   {
    ARRAYFORMULA({IF(ISBLANK(Bob!A3:A);;"Bob") \Bob!A3:G});
    ARRAYFORMULA({IF(ISBLANK(Robert!A3:A);;"Robert") \Robert!A3:G})
   };
   "select Col1, Col2, Col5, Col6 where Col1 <> ''"; 0);
  QUERY(
   {
    ARRAYFORMULA({IF(ISBLANK(Bob!A3:A);;"Bob") \Bob!A3:G});
    ARRAYFORMULA({IF(ISBLANK(Robert!A3:A);;"Robert") \Robert!A3:G})
   };
   "select Col1, Col2, Col7, Col8 where Col1 <> ''"; 0)
  };
  "select * order by Col2"; 0)

This returns only rows with valid data in the first set of queries and then orders them in the last query.

Merge multiple Google Sheet into one master logging sheet

1 Answers