How to Use SQL Join in Google Sheets

You’re a Google Sheets user, but you want to perform join operations, just like you would with the SQL JOIN command. You may be wondering how to achieve this on Google Sheets, given that Google’s SQL implementation does not provide a JOIN command. In this article, we’ll share some tips and tricks to get the same results as a SQL join on Google Sheets.

Tip 1: VLOOKUP + ARRAYFORMULA

The VLOOKUP function is a powerful tool for matching the content of one column in a sheet with the content of another sheet. In this trick, we’ll combine the QUERY, VLOOKUP, and ARRAYFORMULA functions to get the desired results. Here’s how to do it:

1. Use the QUERY function with the SELECT command for the first and third columns.
2. Use the following formula for the second column:

=ArrayFormula({Orders.OrderID,vlookup(Orders.CustomerID,{Customers.CustomerID,Customers.CustomersName},2,false),Orders.OrderDate})

This formula may seem long and complex, but let’s break it down:

• The VLOOKUP function replaces the CustomerID column in the Orders sheet with the CustomerName column in the Customers sheet.
• The braces concatenate the columns into a single entry for ARRAYFORMULA.
• The ARRAYFORMULA function applies the VLOOKUP function to a range of cells.

The result is a modified Orders sheet, where the CustomerID column has been replaced with the corresponding names from the Customers sheet. You can still use QUERY functions within the code to select specific columns, and it will work perfectly.

Tip 2: Google Apps Script

Another trick is to use Google Apps Script to define a JOIN function. Here’s an example script:

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];
        r.splice(row1.length+index2, 1);
        r.splice(index1, 1);
        result.push(r);
      }
    }
  }
  return result;
}

This JOINRANGES function uses the following syntax:

=JOINRANGES(range1, index1, range2, index2)

Here’s what each part means:

• range1: The range containing the column you want to replace. In our example, it’s the range in the Orders sheet.
• index1: The index of the column you want to replace, starting with 0 for the first column. In our example, CustomerID is in the second column, so its index is 1.
• range2: The range containing the column that will replace the one in range1. In our example, it’s the range in the Customers sheet.
• index2: The index of the column that corresponds to range1 with index1. It assumes that the column replacing range1 in index1 is the next column in range2. In our example, CustomerID is at index 0 of range2, and CustomerName is at index 1 of range2.

This script works perfectly, but it requires the column you want to replace to be the last column. Additionally, you need to wrap it with the ARRAYFORMULA function. To simplify the use of this trick, you can use the following formula:

=arrayformula({joinranges(Orders!A1:B197,1,Customers!A1:B120,0),Orders!C1:C197})

This formula uses the JOINRANGES function with the appropriate ranges and attaches the removed column as an additional entry for ARRAYFORMULA. The result is a modified Orders sheet where the CustomerID column has been replaced with the corresponding names from the Customers sheet.

Conclusion

With these tricks, you can achieve similar join operations as SQL on Google Sheets. If you’re looking for a concrete demonstration, you can check out our sample SQL Join sheet to see how they work in practice.

Feel free to use these tricks to succeed in your Google Sheets projects. You can also simplify the data import from your e-commerce and marketing platforms by using Crawlan’s Lido tool. Extract valuable insights from your Shopify, Facebook, Google Analytics, and many more data sources. Start now and streamline your work with Google Sheets!