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

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!

CodePudding user response：

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"))