Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy.
Supose you have all in A1:C5 and E1:G5 data in one sheet. All you need is only put a line like that:
=LEFTJOIN("A1:C5";"E1:G5"; "A=F";0)
We can see a working example here
I used code from Mogsdad@stackoverflow, so thanks him to.
/**
* Convert a cell reference from A1Notation to 0-based indices (for arrays)
* or 1-based indices (for Spreadsheet Service methods).
*
* @param {String} cellA1 Cell reference to be converted.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @return {object} {row,col}, both 0-based array indices.
*
* @throws Error if invalid parameter
*
* @author Mogsdad at stackoverflow
*/
function cellA1ToIndex( cellA1, index ) {
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
// Use regex match to find column & row references.
// Must start with letters, end with numbers.
// This regex still allows induhviduals to provide illegal strings like "AB.#%123"
var match = cellA1.match(/(^[A-Z]+)|([0-9]+$)/gm);
if (match.length != 2) throw new Error( "Invalid cell reference" );
var colA1 = match[0];
var rowA1 = match[1];
return { row: rowA1ToIndex( rowA1, index ),
col: colA1ToIndex( colA1, index ) };
}
/**
* Return a 0-based array index corresponding to a spreadsheet column
* label, as in A1 notation.
*
* @param {String} colA1 Column label to be converted.
*
* @return {Number} 0-based array index.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @throws Error if invalid parameter
*
* @author Mogsdad at stackoverflow
*/
function colA1ToIndex( colA1, index ) {
if (typeof colA1 !== 'string' || colA1.length > 2)
throw new Error( "Expected column label." );
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
var A = "A".charCodeAt(0);
var number = colA1.charCodeAt(colA1.length-1) - A;
if (colA1.length == 2) {
number += 26 * (colA1.charCodeAt(0) - A + 1);
}
return number + index;
}
/**
* Return a 0-based array index corresponding to a spreadsheet row
* number, as in A1 notation. Almost pointless, really, but maintains
* symmetry with colA1ToIndex().
*
* @param {Number} rowA1 Row number to be converted.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @return {Number} 0-based array index.
*
* @author Mogsdad at stackoverflow
*/
function rowA1ToIndex( rowA1, index ) {
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
return rowA1 - 1 + index;
}
/**
* Split a String with Sheet Range A1Notation into Object with Sheet and Range
*
* @param {String} range Sheet string range in format A1 (ex: Sheet1!A1:C3)
*
* @return {object} {sheet,range}, both 0-based array indices.
*
* @author SauloAlessandre at stackoverflow
*/
function SSA1_split_sheet_range(range)
{
var sheet_range = range.split('!');
var sheet_name;
var range_name;
if (sheet_range.length == 2) {
sheet_name = sheet_range[0];
range_name = sheet_range[1];
}
else {
sheet_name = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
range_name = sheet_range[0];
}
return {sheet: sheet_name, range: range_name};
}
/**
* @param {String} range Sheet string range in format A1 (ex: Sheet1!A1:C3)
*
* @return {Sheet object} for the range passed in param
*
* @author SauloAlessandre at stackoverflow
*/
function SSA1_range(range) {
var sheet_range = SSA1_split_sheet_range(range);
var sheet = SpreadsheetApp.getActive().getSheetByName(sheet_range.sheet);
return sheet.getRange(sheet_range.range);
}
function RowsLeftJoin_(left_range, right_ranges, joins, tables, right_ordered, skip_header)
{
right_ordered = right_ordered || 0;
skip_header = skip_header || 0;
debug = function(row) {
// Logger.log(row);
}
var add_right_columns = function(columns, column, row) {
for (var i = 0; i < columns.length; i++) {
if (i != column)
row.push(columns[i]);
}
}
var discover_column = function(column, column_range) {
var sheet_range = SSA1_split_sheet_range(column_range);
var range = sheet_range.range.split(":");
if (range.length != 2)
throw new Error("Error RANGE should be ex: A1:B, not " + column_range);
var c1 = cellA1ToIndex(range[0]).col;
var c2;
var col = colA1ToIndex(column);
var match = range[1].match(/(^[A-Z]+)|([0-9]+$)/gm);
if (match.length != 2)
c2 = colA1ToIndex(range[1]);
else
c2 = cellA1ToIndex(range[1]).col;
if (col < c1 || col > c2)
throw new Error("Error JOIN column [" + column + "] is out of range [" + column_range + "]");
return col-c1;
}
var join_right = function(left_range, right_range, right_max, join, search, row) {
var right_values = SSA1_range(right_range).getValues();
var compare = join.split("=");
if (compare.length != 2)
throw new Error("Error JOIN should be ex: A=B, not " + join);
var column_left = discover_column(compare[0], left_range);
var column_right = discover_column(compare[1], right_range);
var start = 0;
var end = right_max - 1;
debug("column_left " + column_left + " column_right " + column_right);
if (right_ordered) {
while (start <= end){
var mid = Math.floor((start + end)/2);
debug("join_right [" + right_values[mid][column_right] + "] === [" + search[column_left] + "]");
if (right_values[mid][column_right] === search[column_left]) {
debug("join_right found " + search[column_left]);
add_right_columns(right_values[mid], column_right, row);
return;
}
else if (right_values[mid][column_right] < search[column_left]) {
start = mid + 1;
}
else {
end = mid - 1;
}
}
}
else {
for (; start <= end; start++) {
if (right_values[start][column_right] === search[column_left]) {
debug("join_right found " + search[column_left]);
add_right_columns(right_values[start], column_right, row);
return;
}
}
}
debug("join_right not found " + search[column_left]);
}
var count_if_not_empty = function(rows) {
var total = rows.length;
for (var i = rows.length-1; i >= 0; i--) {
if (!rows[i][0])
total--;
}
return total;
}
var range = [];
var left_values = SSA1_range(left_range).getValues();
var rows_range = left_values.length;
for (var i = 0; i < rows_range; i++) {
var cols_range = left_values[i].length;
var row = [];
for (var j = 0; j < cols_range; j++) {
if (left_values[i][j])
row.push(left_values[i][j]);
}
for (t = 0; t < tables; t++ ) {
var right_range = right_ranges[t];
var right_values = SSA1_range(right_range).getValues();
var right_max = count_if_not_empty(right_values);
var join = joins[t];
if (row.length > 0) {
join_right(left_range, right_range, right_max, join, left_values[i], row);
debug(row);
}
}
if (row.length > 0)
range.push(row);
}
return range;
}
/**
* Return a LEFT JOINed range between 2 tables, including all data for the left table
*
* @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on)
* @param {Range_String} right_range right range to join "C1:D15" (only equal values are visible)
* @param {String} join equal comparator between left and right (ex: A=C)
* @param {bool} sorted indicates when the right range is sorted (good for big tables)
*
* @return {Range}
*
* @author SauloAlessandre at stackoverflow
*/
function LeftJoin(left_range, right_range, join, right_ordered, skip_header)
{
return LeftJoinR1(left_range, right_range, join, right_ordered, skip_header);
}
/**
* Return a LEFT JOINed range between 2 tables (same LeftJoni)
*
* @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on)
* @param {Range_String} right_range right range to join "C1:D15" (only equal values are visible)
* @param {String} join equal comparator between left and right (ex: A=C)
* @param {bool} sorted indicates when the right range is sorted (good for big tables)
*
* @return {Range}
*
* @author SauloAlessandre at stackoverflow
*/
function LeftJoinR1(left_range, right_range1, join1, right_ordered, skip_header)
{
var joins = [];
var right_ranges = [];
joins.push(join1);
right_ranges.push(right_range1);
rows = RowsLeftJoin_(left_range, right_ranges, joins, 1, right_ordered, skip_header);
return rows;
}
/**
* Return a LEFT JOINed range between 3 tables, including all data for the left table
*
* @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on)
* @param {Range_String} right_range1 right range to join "C1:D15" (only equal values are visible)
* @param {String} join1 equal comparator between left and right 1 (ex: A=C)
* @param {Range_String} right_range2 right range to join "C1:D15" (only equal values are visible)
* @param {String} join1 equal comparator between left and right 2 (ex: A=C)
* @param {bool} sorted indicates when the right range is sorted (good for big tables)
* @param {bool} skip_header ignore header
*
* @return {Range}
*
* @author SauloAlessandre at stackoverflow
*/
function LeftJoinR2(left_range, right_range1, join1, right_range2, join2, right_ordered, skip_header)
{
var joins = [];
var right_ranges = [];
joins.push(join1); joins.push(join2);
right_ranges.push(right_range1); right_ranges.push(right_range2);
rows = RowsLeftJoin_(left_range, right_ranges, joins, 2, right_ordered, skip_header);
return rows;
}
/**
* Return a LEFT JOINed range between 4 tables
*
* @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on)
* @param {Range_String} right_range1 right range to join "C1:D15" (only equal values are visible)
* @param {String} join1 equal comparator between left and right 1 (ex: A=C)
* @param {Range_String} right_range2 right range to join "C1:D15" (only equal values are visible)
* @param {String} join2 equal comparator between left and right 2 (ex: A=C)
* @param {Range_String} right_range3 right range to join "C1:D15" (only equal values are visible)
* @param {String} join3 equal comparator between left and right 3 (ex: A=C)
* @param {bool} sorted indicates when the right range is sorted (good for big tables)
* @param {bool} skip_header ignore header
*
* @return {Range}
*
* @author SauloAlessandre at stackoverflow
*/
function LeftJoinR3(left_range, right_range1, join1, right_range2, join2, right_range3, join3, right_ordered, skip_header)
{
var joins = [];
var right_ranges = [];
joins.push(join1); joins.push(join2); joins.push(join3);
right_ranges.push(right_range1); right_ranges.push(right_range2); right_ranges.push(right_range3);
rows = RowsLeftJoin_(left_range, right_ranges, joins, 3, right_ordered, skip_header);
return rows;
}
