.full_outer_join_alasql_with_three_tables.md

FULL OUTER JOIN ALASQL WITH THREE TABLES

How to build

• git clone https://gist.github.com/da78186d21641d27bd824f02d8e331f4.git
• npm install
• gapps init YOUR_SCRIPT_ID
• npm start
• make a copy https://docs.google.com/spreadsheets/d/159FFNTdMqUq5ydHf3c0bsnaNJDqgiGND_iLZp58Rt24/copy
• go to your script then test it as add-on...

How to try

• try my add-on test deploy https://goo.gl/R0WGxr

.gitignore

node_modules
src
gapps.config.json

gs_code.js

function aggregator(spreadsheet) {

  var data1 = spreadsheet.getSheetByName('D1').getDataRange().getValues();
  var data2 = spreadsheet.getSheetByName('D2').getDataRange().getValues();
  var data3 = spreadsheet.getSheetByName('D3').getDataRange().getValues();

  var lemur = new SequentialLemur();

  var sql = 'SELECT \
    D1.[1] AS [0], D1.[2] AS [1], D1.[3] AS [2], D1.[4] AS [3], D1.[5] AS [4], D1.[6] AS [5],\
    D2.[2] AS [6], D2.[3] AS [7], D2.[4] AS [8], D2.[5] AS [9], D2.[6] AS [10],\
    D3.[2] AS [11], D3.[3] AS [12], D3.[4] AS [13], D3.[5] AS [14], D3.[6] AS [15], D3.[6] AS [16]\
    FROM ? AS D1 FULL OUTER JOIN ? AS D2 ON D1.[0] = D2.[0] FULL OUTER JOIN ? AS D3 ON D3.[0] = D2.[0]';

  
  return lemur.addData(data1, reducer_).addData(data2, reducer_).addData(data3, reducer_).aggregator(sql);


}

function reducer_(p, c) {
  if (c[0] && c[0].getRound)
    p.push([c[0].getRound().getTime()].concat(c));
  return p;
}

gs_prototypes.js

Date.prototype.getRound = function () {
  var d = new Date(this.getTime());
  d.setHours(0);
  d.setMinutes(0);
  d.setSeconds(0);
  d.setMilliseconds(0);
  return d;
}

gs_SequentialLemur.js

function SequentialLemur() {
  this.datas = [];
  this.alasql = undefined;
}

SequentialLemur.prototype.addData = function (data, reducer) {
    Logger.log(data);
  var d = undefined;
  if (typeof reducer === 'function') {
    d = data.reduce(reducer, []);
  } else {
    d = data.map(function (row) {
      return row;
    });
  }
  this.datas.push(d);

  return this;
}

SequentialLemur.prototype.bindAlasql = function (alasql) {
  this.alasql = alasql;
  return this;
}

SequentialLemur.prototype.getDatasById = function (id) {
  return this.datas[id];
}

SequentialLemur.prototype.aggregator = function (sql) {
  alasql.options.modifier = 'MATRIX';
//  Logger.log(this.datas[2]);
  return alasql(sql, this.datas);
}

gs_snippets.js

function getSheetByName(spreadsheet, sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet || spreadsheet.insertSheet(sheetName);
}

function setValuesToSheet(sheet, values, row, column) {
  row = row || 1;
  column = column || 1;
  return sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

function onInstall(e) { onOpen(e); }

gs_ui.js

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('aggregator', 'runAggregator')
    .addItem('clear', 'clearData')
    .addToUi();
}

function runAggregator() {

  var sp = SpreadsheetApp.getActiveSpreadsheet();
  var lmr = aggregator(sp);
  var sheet = getSheetByName(sp, 'FULL JOIN');
  sheet.clear();
  return setValuesToSheet(sheet, lmr).activate();

}

function clearData() {

  var sp = SpreadsheetApp.getActiveSpreadsheet();
  getSheetByName(sp, 'FULL JOIN').clear().activate();

}

gulpfile.js

var gulp = require('gulp');
var del = require('del');
var rename = require('gulp-rename');
var exec = require('child_process').exec;
var cnfg = require('./gapps.config.json');
var vp = require('vinyl-paths');
var strip = require('gulp-strip-comments');
var removeEmptyLines = require('gulp-remove-empty-lines');

gulp.task('default', ['src'], function (cb) {
  exec('gapps push | while read OUTPUT; do notify-send "$OUTPUT"; done', function (err, stdout, stderr) {
    console.log(stdout);
    console.log(stderr);
    cb(err);
  });
});

gulp.task('clean', function () {
  del([
    'src/*'
  ]);
});

gulp.task('src', ['clean'], function () {
  gulp.src(['*.html', '*.gs', '*.js', '!gulpfile.js'])
    .pipe(gulp.dest('src'));
  gulp.src('node_modules/alasql/dist/alasql.js')
    .pipe(removeEmptyLines({
      removeComments: true
    }))
    .pipe(strip())
    .pipe(rename('z_alasql.gs'))
    .pipe(gulp.dest('src'));
});

gulp.task('init', ['gappsexec'], function () {
  gulp.src(['src/*.html', 'src/*.gs', 'src/*.js'])
    .pipe(gulp.dest('.'));
});


gulp.task('cleanbup', function () {
  del([
    '.backups'
  ]);
});


gulp.task('gs', function (cb) {
  var path = `.backups/${new Date().getTime()}`;

  return gulp.src(['src/**/*', './gapps.config.json'], { base: '.' })
    .pipe(vp(del))
    .pipe(gulp.dest(path));
});

gulp.task('gappsexec', ['gs'], function (cb) {
  exec(`gapps init ${cnfg.fileId}`, function (err, stdout, stderr) {
    console.log(stdout);
    console.log(stderr);
    cb(err);
  })
});

package.json

{
  "name": "full_outer_join_alasql_with_three_tables",
  "version": "1.0.0",
  "description": "",
  "main": "gs_code.gs",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "gulp",
    "init": "gulp init"
  },
  "repository": {
    "type": "git",
    "url": "git+https://gist.github.com/da78186d21641d27bd824f02d8e331f4.git"
  },
  "author": "Alexander Ivanov <[email protected]>",
  "license": "WTFPL",
  "bugs": {
    "url": "https://gist.github.com/da78186d21641d27bd824f02d8e331f4"
  },
  "homepage": "https://gist.github.com/da78186d21641d27bd824f02d8e331f4",
  "devDependencies": {
    "alasql": "^0.3.9",
    "del": "^2.2.2",
    "gulp": "^3.9.1",
    "gulp-remove-empty-lines": "0.0.8",
    "gulp-rename": "^1.2.2",
    "gulp-strip-comments": "^2.4.5",
    "vinyl-paths": "^2.1.0"
  }
}

z_screenshot_01.png