Google Apps Script - use query to combine 2 sheets but fail to get the complete
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!
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
- Finding min and max in array Javascript
- Python: slicing a multi-dimensional array
- How to count all objects in an array that match a condition?
- Why `[1, 2, 3]` is not showing as an `Array(3)` in Console?
- Cannot memory-free strings from arbitrary-length array of strings
- Explode delimited string into a 2d array with a specified key in each single-element row
- Why there is no F# immutable arrays?
- How to convert two longs to a byte array = how to convert UUID to byte array?
- C++ constexpr operations on each element of a constexpr array
- is there any array reverse indexing in c like python's a[-1]?
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines