Google Sheets – How to Make SQL CROSS JOIN of Two Tables

google sheetsgoogle-apps-scriptgoogle-sheets-query

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 workaround over this?

Example

https://docs.google.com/spreadsheets/d/1YBf367DFhkwlnnsEuNxLoyWbtbo77KbH2VDEvtTf-zY/edit?usp=sharing

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

SELECT dates.date, users.name
FROM dates, users 
WHERE dates.date BETWEEN users.date_begin AND users.date_end

How would you do that?

Best Answer

With the following little script you can do that.

Code

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;  
}

Screenshot

data
enter image description here

outcome
enter image description here

Example

I've added the script to your example file.

Related Topic