Google Apps Script - use query to combine 2 sheets but fail to get the complete

Question

I wonder if there's a limitation of number of rows when use query function to combine sheets, and how can we fix it?

Background

I have 2 sheets that contain data, Sheet1 and Sheet2. In Sheet1, I have around 13k rows with Date and ID:

In Sheet2 I have around 4.5k rows and I extract the date from timestamp in column C to make the date value align with Sheet1:

Purpose

I would like to combine Sheet1 and Sheet2 in Sheet3, using unique to deduplicate those rows with the same ID and same Date at the same time.

Question

Currently in Sheet3 I use this formula:

and the dates in Sheet2 somehow don't show up. But when I eliminate most of rows in Sheet1 and, for example, only retain 10 rows, the error in Sheet3 seems to be fixed accordingly.

Can someone help with this? Thanks!

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT(QUERY(UNIQUE(
 {Sheet1!A:A&"×"&Sheet1!B:B; Sheet2!A2:A&"×"&Sheet2!B2:B}), 
 "where Col1 is not null"), "×"), "order by Col2"))

demo spreadsheet