How to make SQL JOIN of two tables with Google Spreadsheet?

by Emilio Nicolás   Last Updated February 16, 2018 02:03 AM

I am just figuring out how to achieve an apparently simple problem. I use to have data as in database tables in my spreadsheets. It's simple data but I often need to use =QUERY function and would be great if it could manage joins.

Any simple work arround over this?


There are users subscription information and deliver dates, I want to JOIN both. In SQL would be something as simple as this:

FROM dates, users 
WHERE BETWEEN users.date_begin AND users.date_end

How would you do that?

Tags : google-sheets

Answers 2

With the following little script you can do that.


function mySQL(ref, dates) {
  var output = [];
  for(var i = 0, iLen = ref.length; i < iLen; i++) {
    var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]);
    for(var j = 0, jLen = dates.length; j < jLen; j++) {
      var date = new Date(dates[j]);
      if(date >= begin && date <= end) {
        output.push([date, user]);
  return output;  


enter image description here

enter image description here


I've added the script to your example file.

Jacob Jan Tuinstra
Jacob Jan Tuinstra
November 20, 2014 21:43 PM

I know this has been inactive for long, but I came across this post while looking for a "Google Sheets JOIN", and as I got something that worked for me, I think I might share it.

Albums: a list of Beatles albums, where column A has an unique ID for each row (primary key) and column B has the name of the album.

Songs: a sheet of Beatles songs. Every row contains in column A the ID of an album (a "foreign key", the same IDs found in Tab1), and the name of a song. In this sheet you will find multiple rows with a same ID.

Tab3: is the sheet where you want to display in column A song names, in column B the album names, in column C the album ID.

So you have in Tab3!A1:

=QUERY(Songs!A1:B; "SELECT B")

And in Tab3!B1:

=ARRAYFORMULA({if(A1:A; vlookup(A1:A; Albums!A1:B; 2; FALSE); ) \ if(A1:A; vlookup(A1:A; Albums!A1:B; 1; FALSE); )})

Magic is made by the "grouping operators" "{}" and "\". It's a pity there is no way to make VLOOKUP return more than one column (well, I haven't found any). The IFs, as you see, only check if column A has a value to use for the lookup, and if there is none, it leaves the cells blank.

This is as close to an INNER JOIN as I could get... hope everyone can improve on it.


Fabricio Rocha

Brasilia, Brasil

Fabricio Rocha
Fabricio Rocha
February 16, 2018 01:56 AM

Related Questions

Copying values between google sheets

Updated July 26, 2018 21:03 PM

Aggregate results by week and month for charts

Updated January 18, 2019 09:03 AM