Workarounds for Using SQL JOIN on Google Sheets

Learn how to use functions in Google Sheets to achieve the same result as SQL Join.

Table of Contents
  1. A VLOOKUP + ARRAYFORMULA hack
  2. A sample using Google Apps Script
  3. Too complicated?

Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. A VLOOKUP + ARRAYFORMULA hack
  2. A sample using Google Apps Script
  3. Too complicated?

Let’s say we have the following data on the Orders sheet:

First few rows of the Orders sheet.

And the following data on the Customers sheet:

First few rows of 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:

First few rows of the Orders sheet.

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:

First few rows of the modified Orders sheet. The formula was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.

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:


First few rows of the modified Orders sheet. The newly-defined function was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.

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:

First few rows of the modified Orders sheet. A modified formula using the newly-defined function was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.


Too complicated?

Searching for an actual demonstration? Click on the link below to access a sample sheet:

SQL Join 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.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

October 11, 2021

Workarounds for Using SQL JOIN on Google Sheets

Google Sheet spreadsheet

Let’s say we have the following data on the Orders sheet:

First few rows of the Orders sheet.

And the following data on the Customers sheet:

First few rows of 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:

First few rows of the Orders sheet.

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:

First few rows of the modified Orders sheet. The formula was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.

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:


First few rows of the modified Orders sheet. The newly-defined function was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.

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:

First few rows of the modified Orders sheet. A modified formula using the newly-defined function was used to replace the CustomerID by the corresponding CustomerName from the Customers sheet.


Too complicated?

Searching for an actual demonstration? Click on the link below to access a sample sheet:

SQL Join 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.

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.