Let’s say we have the following data on the Orders sheet:
And the following data on the Customers sheet:
(The sample data is the data used by W3Schools for their SQL guides)
We want to replace the CustomerID in the Orders sheet by matching the CustomerID with the CustomerName on the Customers sheet. In SQL, we can use the JOIN command. However, Google’s own implementation of SQL has no JOIN command.
It is still possible to get the same results using Google Sheets’s built-in functions. In this tutorial, we compiled some of the workarounds that can be used to achieve the same results as SQL JOIN, but on Google Sheets.
A VLOOKUP + ARRAYFORMULA hack
VLOOKUP is a powerful function for matching the contents of one column of a sheet with the contents of another sheet. If you want to review VLOOKUP, we have the following tutorials that you can check first:
How to Vlookup From Another Spreadsheet on Google Sheets
How to use VLOOKUP with Multiple Criteria in Google Sheets
Use VLOOKUP to Convert Numerical Scores to Letter Scores
In this hack, we will combine QUERY with VLOOKUP and ARRAYFORMULA. What we will do is to chop off the columns so that we can achieve the same format but replacing the CustomerID with CustomerName. Let us check the Orders sheet again:
For our first and third column, we will use the plain QUERY function with the SELECT command separately. For our second column, we will use the following formula:
=ArrayFormula({Orders.OrderID,vlookup(Orders.CustomerID,{Customers.CustomerID,Customers.CustomersName},2,false),Orders.OrderDate})
This looks long and daunting, so let’s explain each individual part:
- The VLOOKUP function replaces the CustomerID column in the Orders sheet (Orders.CustomerID) with the CustomerName column in the Customers sheet (Customers.CustomersName).
- The braces concatenate (combine) the columns into a single input for the ARRAYFORMULA
- The ARRAYFORMULA function converts VLOOKUP so that it can be applied to a range
The result is:
You can still use QUERY functions inside the code to call the specific columns and it will still run flawlessly.
A sample using Google Apps Script
Alternatively, we can define a function through the Google Apps Script. For this workaround, we will adopt Martí’s answer in a question in StackOverflow. While this is not a verified answer (and unfortunately has zero votes as of this writing), this works well as a way of defining a JOIN function in Google Sheets.
Here is the script in question:
function JOINRANGES(range1, index1, range2, index2) {
const result = []
for(let row1 of range1) {
for (let row2 of range2) {
if (row1[index1] == row2[index2]) {
const r = [...row1, ...row2]
// Remove the keys themselves
r.splice(row1.length+index2, 1)
r.splice(index1, 1)
result.push(r)
}
}
}
return result
}
The function JOINRANGES uses the following syntax:
=JOINRANGES(range1, index1, range2, index2)
Let’s explain each part:
range1 = range with the column you want to replace. For our example it’s the range of the Orders sheet.
index1 = the index of the column that you want to replace, starts with 0 on the first column. In our example, CustomerID is in the 2nd column so its index is 1.
range2 = range with the column that replaces that of range1. For our example it’s the range of the Customers sheet.
index2 = the index of the column that is the same as that of the range1 column with index1. It presumes that the column that will replace that of range1 column in index1 is the next column in range2. In our example, CustomerID is in the index2 of range2 and the CustomerName is in the index2+1 of range2.
The script works perfectly, but it requires that the column that you want to replace is the last column. Additionally, you need to enclose this inside an ARRAYFORMULA function. If you simply used the formula with the given ranges:
=arrayformula(joinranges(Orders!A1:C197,1,Customers!A1:B120,0))
The result is:
The CustomerName column that replaces the CustomerID column is placed as the last column instead of being between OrderID and OrderDate. As the code is tailored for replacing the last column of the target sheet, it can be cumbersome to modify it for every case. A simpler workaround involves, again, the ARRAYFORMULA. In the same fashion as in the previous section, we will do the following:
=arrayformula({joinranges(Orders!A1:B197,1,Customers!A1:B120,0),Orders!C1:C197})
We reduced the range1 and attached the removed column as additional input to ARRAYFORMULA. The result now is:
Too complicated?
Searching for an actual demonstration? Click on the link below to access a sample sheet:
Too complicated? Lido provides a simple and easy solution to importing data from your e-Commerce and marketing platforms. Automatically import data from your favorite e-Commerce and marketing platforms such as Shopify, Facebook, and Google Analytics and apply Lido’s software to extract meaningful metrics from them. Get started here.
Recommended articles
More resources
