bindiego
4/9/2015 - 3:40 AM
Google spreadsheet import data from mysql
Google spreadsheet import data from mysql
/**
* Copyright (c) 2015 Edanz Group All Rights Reserved
*
* Spreadsheet API: https://developers.google.com/apps-script/reference/spreadsheet/
*/
var address = '';
var user = '';
var userPwd = '';
var db = 'crm';
var instanceUrl = 'jdbc:mysql://' + address;
// var dbUrl = instanceUrl + '/' + db;
var dbUrl = instanceUrl + '/' + db + '?useUnicode=true&characterEncoding=UTF-8';
var conn = null;
var stmt = null;
var results = null;
// DB queryies
var select = 'select ';
var fields = '*';
// var fromContact = ' from contacts as c left join contacts_cstm as cc on c.id = cc.id_c and c.deleted = "0" ';
// var fromJob = ' from opportunities as o left join opportunities_cstm as oc on o.id = oc.id_c and o.deleted = "0" ';
// settings constant
var selColNum = 3;
var colNamesNum = 4;
var filtersJsonNum = 5;
// debug function
function debug() {
setConfigValue(selColNum, 'zzz');
setConfigValue(filtersJsonNum, 'zzz');
setConfigValue(colNamesNum, 'zzz');
}
/*
* Get row number for a give sheetId column value from the config sheet
*/
//function getRowNum(sheetId) {
function getRowNum() {
var sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
var rtn = null;
var offset = 2;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('config');
//var range = sheet.getRange(offset, 2, sheet.getMaxRows(), 1); // start from row 2, col 2, all rows but only column for sheet ids
var range = sheet.getRange(offset, 2, sheet.getLastRow(), 1); // start from row 2, col 2, all rows but only column for sheet ids
var values = range.getValues();
row_loop:
for (var row in values) {
col_loop:
for(var col in values[row]) {
if (values[row][col] == sheetId) {
var rtn = parseInt(row) + offset;
Logger.log('%s settings found at row %s', sheetId, rtn);
break row_loop;
}
}
}
if (!isNotEmpty(rtn))
Logger.log('%s settings NOT found', sheetId);
return rtn;
}
/*
* Get settings from a cell
*/
//function getConfigValue(sheetId, colNum) {
function getConfigValue(colNum) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('config');
var sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
var rowNum = getRowNum(sheetId);
if (isNotEmpty(rowNum)) {
var range = sheet.getRange(rowNum, parseInt(colNum), 1, 1);
var values = range.getValues();
var val = values[0][0];
Logger.log('\nvalue: %s', val);
return val;
} else {
return null;
}
}
/*
* Set / update settings to a cell
*/
//function setConfigValue(sheetId, colNum, val) {
function setConfigValue(colNum, val) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('config');
var sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
var rowNum = getRowNum(sheetId);
// create row if doesn't exist
if (isNotEmpty(rowNum)) {
var range = sheet.getRange(rowNum, parseInt(colNum), 1, 1);
range.setValue(val);
} else {
var spreadSheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
//var sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
// insert a new row
var maxRowNum = sheet.getMaxRows(); // regardless the content
var lastRowNum = sheet.getLastRow(); // last row has content
// prevent overflow
if (maxRowNum <= lastRowNum) {
sheet.insertRowsAfter(lastRowNum, 1000);
}
var newRow = lastRowNum + 1;
var range = null;
range = sheet.getRange(newRow, 1, 1, 1);
range.setValue(spreadSheetId);
range = sheet.getRange(newRow, 2, 1, 1);
range.setValue(sheetId);
range = sheet.getRange(newRow, parseInt(colNum), 1, 1);
range.setValue(val);
}
}
/*
* Import Jobs
*/
function importJobs(dbFields, columnNames, conditions) {
}
/*
* Show Contacts sidebar
*/
function showContactsUi() {
var ui = HtmlService.createHtmlOutputFromFile('ImportContacts')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(800)
.setHeight(600);
SpreadsheetApp.getUi()
.showModalDialog(ui, 'Import Contacts');
}
/*
* Show Jobs sidebar
*/
function showJobsUi() {
var ui = HtmlService.createHtmlOutputFromFile('ImportJobs')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(800)
.setHeight(600);
SpreadsheetApp.getUi()
.showModalDialog(ui, 'Import Jobs');
}
/*
* Show Dedup UI
*/
function showDedupUi() {
var html = HtmlService.createHtmlOutputFromFile('Dedup')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('Dedup')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showSidebar(html);
}
/**
* DB init
*/
function init(url) {
Logger.log("\nUrl: %s\nUser: %s\nPW: %s", url, user, userPwd)
conn = Jdbc.getConnection(url, user, userPwd);
}
/**
* DB resources release
*/
function shutdown() {
if (results != null)
results.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
}
/*
* Called by system on opening the spreadsheet
*/
function edanzOnOpen(e) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name: "Import Contacts",
functionName: "showContactsUi"
},
{
name: "Import Jobs",
functionName: "showJobsUi"
},
{
name: "Dedup",
functionName: "showDedupUi"
}
];
spreadsheet.addMenu("Edanz", entries);
}
/**
* String utility
*/
function isNotEmpty(string) {
if(!string) return false;
if(string == '') return false;
if(string === false) return false;
if(string === null) return false;
if(string == undefined) return false;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return false;
return true;
}var cn_job_stage_map = {"Prospecting":"Rec Order","Received_Quote":"Rec Quote","order":"Order","withassigner":"With Assigner","quotereadyforauth":"Quote Ready for Auth","Qualification":"Quote","jobchecked":"Job Checked","edresponse":"1st Waiting for Ed\/WM","Needs Analysis":"1st With Editor\/WM","1stqualirtyrev":"1st Quality Review","feecheck":"Manager Check","rettoauthour":"1st Ready for Aut","Value Proposition":"1st Returned to Aut","2ndEdit Received":"Clarification Rec","twowaitingeditor":"Clarification Waiting for Ed\/WM","twowitheditor":"Clarification With Editor\/WM","2ndqualityreview":"Clarification Quality Review","twotogoback":"Clarification Ready for Aut","tworeturnedtoauthor":"Clarification Returned to Aut","awaitingqc":"Waiting for QC Ed","withqc":"With QC Ed","finishedqc":"Completed QC","Id. Decision Makers":"Invoiced","Perception Analysis":"Closed","lost":"Lost Job","cancelled":"Cancelled","Proposal\/Price Quote":"Misc","withtranslator":"With Translator","FS Manager Assessment":"FS Manager Assessment","re-opened":"Re-Opened"};
var job_stage_col_name = 'Stage';
function fixStage() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var values = range.getValues();
var colNum = -1;
row_loop:
for (var row in values) {
col_loop:
for(var col in values[row]) {
if (values[row][col] == job_stage_col_name) {
colNum = parseInt(col) + 1; // array index starts from 0, sheet column starts from 1
Logger.log('Stage data found at column: %s', colNum);
break row_loop;
}
}
}
// replace the values
if (colNum != -1) {
range = sheet.getRange(2, colNum, sheet.getLastRow(), 1);
values = range.getValues();
// Logger.log('Before replacement: %s', values);
row_loop:
for (var row in values) {
col_loop:
for(var col in values[row]) {
values[row][col] =
isNotEmpty(cn_job_stage_map[values[row][col]]) ?
cn_job_stage_map[values[row][col]] : '';
} // end inner loop
} // end outter loop
// Logger.log('After replacement: %s', values);
range.setValues(values);
} // end if
}<!DOCTYPE html>
<meta charset="utf-8">
<!-- This CSS package applies Google styling; it should always be included. -->
<!-- link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css" -->
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jquery/css/base.css" />
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jqueryui.com/style.css" />
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
<link rel="stylesheet"
href="//mistic100.github.io/jQuery-QueryBuilder/dist/bootstrap-datepicker/dist/css/bootstrap-datepicker3.min.css" />
<link rel="stylesheet"
href="//mistic100.github.io/jQuery-QueryBuilder/dist/seiyria-bootstrap-slider/dist/css/bootstrap-slider.min.css" />
<link rel="stylesheet" href="//mistic100.github.io/jQuery-QueryBuilder/dist/selectize/dist/css/selectize.default.css" />
<link rel="stylesheet"
href="https://mistic100.github.io/jQuery-QueryBuilder/dist/bootstrap-select/dist/css/bootstrap-select.min.css" />
<link rel="stylesheet"
href="https://mistic100.github.io/jQuery-QueryBuilder/dist/awesome-bootstrap-checkbox/awesome-bootstrap-checkbox.css" />
<link rel="stylesheet"
href="https://mistic100.github.io/jQuery-QueryBuilder/dist/jQuery-QueryBuilder/dist/css/query-builder.default.min.css" />
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<!-- Optional theme -->
<!-- link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css" -->
<style>
body {
font-family: "Trebuchet MS", "Helvetica", "Arial", "Verdana", "sans-serif";
font-size: 100%;
}
label {
font-weight: bold;
}
.branding-below {
bottom: 54px;
top: 0;
}
.branding-text {
left: 7px;
position: relative;
top: 3px;
}
.logo {
vertical-align: middle;
}
.width-100 {
width: 100%;
box-sizing: border-box;
-webkit-box-sizing : border-box;
-moz-box-sizing : border-box;
}
#sidebar-value-block,
#dialog-elements {
background-color: #eee;
border-color: #eee;
border-width: 5px;
border-style: solid;
}
#sidebar-button-bar,
#dialog-button-bar {
margin-bottom: 10px;
}
#feedback { font-size: 1.4em; }
#selectable .ui-selecting { background: #FECA40; }
#selectable .ui-selected { background: #F39814; color: white; }
#selectable { list-style-type: none; margin: 0; padding: 0; width: 100%; }
#selectable li { margin: 2px; padding: 0.2em; font-size: 1.4em; height: 26px; }
</style>
<div id='main'>
<div id='accordion'>
<h3>Select Columns</h3>
<div>
<ol id="selectable">
<li class="ui-widget-content ui-selected" value='o.id'>
Job ID
</li>
<li class="ui-widget-content" value='o.date_entered'>
Date Entered
</li>
<li class="ui-widget-content" value='oc.job_received_c'>
Order Date
</li>
<li class="ui-widget-content ui-selected" value='o.name'>
Job Name
</li>
<li class="ui-widget-content" value='amount'>
Amount
</li>
<li class="ui-widget-content ui-selected" value='sales_stage'>
Stage
</li>
<li class="ui-widget-content" value='o.description'>
Description
</li>
<li class="ui-widget-content ui-selected" value='journal_c'>
Journal
</li>
<li class="ui-widget-content" value='journal_url_c'>
Journal URL
</li>
<li class="ui-widget-content" value='editor_fee_final_c'>
Editor Fee
</li>
<li class="ui-widget-content" value='invoice_sent_c'>
Invoice Date
</li>
<li class="ui-widget-content" value='oc.invoicee_c'>
Invoicee
</li>
<li class="ui-widget-content" value='author_note_c'>
Author Notes
</li>
<li class="ui-widget-content" value='billing_date_c'>
Billing Date
</li>
<li class="ui-widget-content" value='billing_review_notes_c'>
Billing Review Notes
</li>
<li class="ui-widget-content" value='payment_received_c'>
Payment Recieved Date
</li>
<li class="ui-widget-content" value='amount_paid_c'>
Amount Paid
</li>
<li class="ui-widget-content" value='oc.key_words_c'>
Key Words
</li>
<li class="ui-widget-content" value='due_date_c'>
Due Date
</li>
<li class="ui-widget-content" value='job_number_inc_c'>
Job Number
</li>
<li class="ui-widget-content" value='partnerreferral_c'>
Partner Referral
</li>
<li class="ui-widget-content" value='oc.focus_cld_c'>
Cover Letter Development
</li>
<li class="ui-widget-content" value='oc.focus_js_c'>
Journal Selection
</li>
<li class="ui-widget-content" value='oc.abstract_development_c'>
Abstract Development
</li>
<li class="ui-widget-content" value='oc.reviewer_recommendation_c'>
Reviewer Recommendation
</li>
<li class="ui-widget-content" value='oc.focus_esr_words_c'>
Expert Review Word
</li>
<li class="ui-widget-content ui-selected" value='ocon.contact_id'>
Contact ID
</li>
<li class="ui-widget-content ui-selected" value='first_name'>
First Name
</li>
<li class="ui-widget-content ui-selected" value='last_name'>
Last Name
</li>
<li class="ui-widget-content" value='department'>
Department
</li>
<li class="ui-widget-content ui-selected" value='phone_mobile'>
Mobile
</li>
<li class="ui-widget-content ui-selected" value='email1'>
Email
</li>
<li class="ui-widget-content ui-selected" value='japanese_name_c'>
Native Name
</li>
<li class="ui-widget-content ui-selected" value='first_job_date_c'>
First Order Date
</li>
<li class="ui-widget-content ui-selected" value='number_inc_c'>
Contact Number
</li>
</ol>
</div>
<h3>Filters</h3>
<div id='querybuilder'>
</div>
</div>
<button type='button' class='btn btn-xs btn-success'
onclick='import_contacts()'>
Import Jobs
</button>
<button type='button' class='btn btn-xs btn-danger'
onclick='close_sidebar()'>
Close
</button>
</div>
<div id='loading'>
<br />
<center>Loading ... </center>
<br />
<center><img src='http://global.js.widget.eja.hk/sites/all/modules/edanz_ja/skins/aps/images/edanz_loading.gif' /></center>
</div>
<div id='debug'>
</div>
<script src="//code.jquery.com/jquery-1.11.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/momentjs/min/moment.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/bootstrap-datepicker/dist/js/bootstrap-datepicker.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/seiyria-bootstrap-slider/dist/bootstrap-slider.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/selectize/dist/js/standalone/selectize.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/jQuery-QueryBuilder/dist/js/query-builder.standalone.min.js"></script>
<script type="text/javascript">
/* main */
var fromClause = ' from opportunities as o ' +
'left join opportunities_cstm as oc on o.id = oc.id_c and o.deleted = "0" ' +
'left join opportunities_contacts as ocon on o.id = ocon.opportunity_id ' +
'left join contacts as c on ocon.contact_id = c.id ' +
'left join contacts_cstm as cc on c.id = cc.id_c ';
show_loading(false);
// settings constant
var selColNum = 3;
var colNamesNum = 4;
var filtersJsonNum = 5;
var db_fields = [];
var col_names = [];
// setup accordion
$(function() {
$( "#accordion" ).accordion();
});
// selectable area
$(function() {
$( "#selectable" ).selectable({
stop: function() {
// var result = $( "#debug" ).empty();
db_fields = [];
col_names = [];
$(".ui-selected").each(function(index, items) {
db_fields.push($(items).attr('value'));
col_names.push($(items).text().trim());
});
console.log(db_fields);
console.log(col_names);
}
});
});
// jquery-query-builder
$('#querybuilder').queryBuilder({
plugins: ['sortable', 'bt-tooltip-errors'],
filters: [
{
id: 'o.id',
label: 'Job ID',
type: 'string'
},
{
id: 'o.date_entered',
label: 'Date Entered',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'oc.job_received_c',
label: 'Order Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'o.name',
label: 'Job Name',
type: 'string'
},
{
id: 'amount',
label: 'Amount',
type: 'integer'
},
{
id: 'sales_stage',
label: 'Stage',
type: 'string',
input: 'checkbox',
values: {
'Perception Analysis': 'Perception Analysis',
'lost': 'lost',
'Id. Decision Makers': 'Id. Decision Makers',
'pending_client': 'pending_client',
'twowaitingeditor': 'twowaitingeditor',
'Prospecting': 'Prospecting',
'Value Proposition': 'Value Proposition',
'cancelled': 'cancelled',
'Needs Analysis': 'Needs Analysis',
'Received_Quote': 'Received_Quote',
'2ndqualityreview': '2ndqualityreview',
'twowitheditor': 'twowitheditor',
'withqc': 'withqc',
'Qualification': 'Qualification',
'Proposal/Price Quote': 'Proposal/Price Quote',
'1stqualityrev': '1stqualityrev',
'1stqualirtyrev': '1stqualirtyrev',
'twotogoback': 'twotogoback',
'tworeturnedtoauthor': 'tworeturnedtoauthor',
'edresponse': 'edresponse',
're-opened': 're-opened',
'rettoauthour': 'rettoauthour',
'withassigner': 'withassigner'
},
color: 'primary',
description: 'Stages',
operators: ['equal', 'not_equal', 'in', 'not_in', 'is_null', 'is_not_null']
},
{
id: 'o.description',
label: 'Description',
type: 'string'
},
{
id: 'journal_c',
label: 'Journal',
type: 'string'
},
{
id: 'journal_url_c',
label: 'Journal URL',
type: 'string'
},
{
id: 'editor_fee_final_c',
label: 'Editor Fee',
type: 'integer'
},
{
id: 'invoice_sent_c',
label: 'Invoice Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'oc.invoicee_c',
label: 'Invoicee',
type: 'string'
},
{
id: 'author_note_c',
label: 'Author Notes',
type: 'string'
},
{
id: 'billing_date_c',
label: 'Billing Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'billing_review_notes_c',
label: 'Billing Review Notes',
type: 'string'
},
{
id: 'payment_received_c',
label: 'Payment Recieved Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'amount_paid_c',
label: 'Amount Paid',
type: 'integer'
},
{
id: 'oc.key_words_c',
label: 'Key Words',
type: 'string'
},
{
id: 'due_date_c',
label: 'Due Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'job_number_inc_c',
label: 'Job Number',
type: 'integer'
},
{
id: 'partnerreferral_c',
label: 'Partner Referral',
type: 'string'
},
{
id: 'oc.focus_cld_c',
label: 'Cover Letter Development',
type: 'integer',
input: 'select',
values: {
0: 'Not Selected',
1: 'Selected'
},
operators: ['equal', 'not_equal', 'is_null', 'is_not_null']
},
{
id: 'oc.focus_js_c',
label: 'Journal Selection',
type: 'integer',
input: 'select',
values: {
0: 'Not Selected',
1: 'Selected'
},
operators: ['equal', 'not_equal', 'is_null', 'is_not_null']
},
{
id: 'oc.abstract_development_c',
label: 'Abstract Development',
type: 'integer',
input: 'select',
values: {
0: 'Not Selected',
1: 'Selected'
},
operators: ['equal', 'not_equal', 'is_null', 'is_not_null']
},
{
id: 'oc.reviewer_recommendation_c',
label: 'Reviewer Recommendation',
type: 'integer',
input: 'select',
values: {
0: 'Not Selected',
1: 'Selected'
},
operators: ['equal', 'not_equal', 'is_null', 'is_not_null']
},
{
id: 'oc.focus_esr_words_c',
label: 'Expert Review Word',
type: 'integer',
operators: ['equal', 'not_equal', 'is_null', 'is_not_null']
},
{
id: 'ocon.contact_id',
label: 'Contact ID',
type: 'string'
},
{
id: 'first_name',
label: 'First Name',
type: 'string'
},
{
id: 'last_name',
label: 'Last Name',
type: 'string'
},
{
id: 'department',
label: 'Department',
type: 'string'
},
{
id: 'phone_mobile',
label: 'Mobile',
type: 'string'
},
{
id: 'email1',
label: 'Email',
type: 'string'
},
{
id: 'japanese_name_c',
label: 'Native Name',
type: 'string'
},
{
id: 'first_job_date_c',
label: 'First Order Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'number_inc_c',
label: 'Contact Number',
type: 'integer'
}
],
rules: {
condition: 'AND',
rules: [{
id: 'sales_stage',
operator: 'in',
value: ['lost', 'Prospecting', 'cancelled', 'Received_Quote', 'Proposal/Price Quote']
}]
}
});
// recover settings if any
function cbSelCols(p) {
sel_cols = p;
console.log('Recovered sel_cols: %s', sel_cols);
if (isNotEmpty(sel_cols)) {
sel_cols = JSON.parse(sel_cols);
$('#selectable > li').each(function() {
var liId = $(this).attr('value');
$(this).removeClass('ui-selected');
if ($.inArray(liId, sel_cols) != -1) {
$(this).addClass('ui-selected');
}
});
}
}
function cbFiltersJson(p) {
filters_json = p;
console.log('Recovered filters_json: %s', filters_json);
if (isNotEmpty(filters_json)) {
$('#querybuilder').queryBuilder('setRules', JSON.parse(filters_json));
}
}
var sel_cols = null;
google.script.run.withSuccessHandler(cbSelCols).getConfigValue(selColNum);
var filters_json = null;
google.script.run.withSuccessHandler(cbFiltersJson).getConfigValue(filtersJsonNum);
/* end of main */
/*
* blocking UI for loading
*/
function show_loading(show) {
if (show) {
$('#main').hide();
$('#loading').show();
} else {
$('#loading').hide();
$('#main').show();
}
}
/*
* envent handler
*/
function load_done(error) {
show_loading(false);
//$('#debug').text(error);
}
/*
* javascript wrapper for google script
*/
function import_contacts() {
db_fields = [];
col_names = [];
$(".ui-selected").each(function(index, items) {
db_fields.push($(items).attr('value'));
col_names.push($(items).text().trim());
});
var res = $('#querybuilder').queryBuilder('getSQL', false, false);
var rules_json = JSON.stringify($('#querybuilder').queryBuilder('getRules'));
show_loading(true);
console.log('db_fields: ' + db_fields);
console.log('col_names: ' + col_names);
console.log('sql_where : ' + res.sql);
console.log('rules_json: ' + rules_json);
google.script.run
.withFailureHandler(load_done)
.withSuccessHandler(load_done)
.importData(
db_fields,
col_names,
fromClause,
res.sql,
rules_json
);
//$('#debug').text(res.sql);
}
/*
* javascript wrapper for google script
*/
function close_sidebar() {
google.script.host.close();
}
/**
* String utility
*/
function isNotEmpty(string) {
if(!string) return false;
if(string == '') return false;
if(string === false) return false;
if(string === null) return false;
if(string == undefined) return false;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return false;
return true;
}
/*
* Array utility
*/
function isArray(myArray) {
return myArray.constructor.toString().indexOf("Array") > -1;
}
</script>
/*
* Import Contacts
*/
function importData(dbFields, colNames, fromClause, conditions, conditions_json) {
// debug info
Logger.log('\nDB fileds: %s\n Column Names: %s\n From Clause: %s\n SQL conditions: %s\n JSON conditions: %s',
dbFields, colNames, fromClause, conditions, conditions_json);
// init
init(dbUrl);
// starting timestamp
var start = new Date();
var query = '';
//var sheetName = 'Contacts - ' + Utilities.formatDate(new Date(), "GMT", "dd-MM-yyyy");
//var sheetName = 'Contacts';
//Logger.log('Targeting sheet name: %s', sheetName);
try {
// do the job
stmt = conn.createStatement();
// add db fiedls instead of *
if (isNotEmpty(dbFields)) {
fields = ''; // reset
for (i = 0; i < dbFields.length; ++i) {
if (i === (dbFields.length - 1)) {
fields = fields + dbFields[i];
} else {
fields = fields + dbFields[i] + ', ';
}
}
Logger.log("\nFields: %s", dbFields);
}
// build the query
query = select + fields + fromClause;
// apply where conditions if any
if (isNotEmpty(conditions)) {
query += ' where ' + conditions;
Logger.log('Conditions detected, query: %s', query);
}
Logger.log('Final query: %s', query);
results = stmt.executeQuery(query);
var numCols = results.getMetaData().getColumnCount();
Logger.log('Number of columns returned: %s', numCols);
//var ss = SpreadsheetApp.getActiveSpreadsheet(); // REVISIT: check null
//var sheet = ss.getSheetByName(sheetName);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
// REVISIT: clean up
// if (round == 0) {
/*
if (sheet != null) {
Logger.log('Sheet %s exists, cleaning up', sheetName);
sheet.clear();
} else {
Logger.log('Sheet %s created', sheetName);
sheet = ss.insertSheet(sheetName);
}
*/
// }
if (sheet != null) {
Logger.log('Found current active sheet, clearing up');
sheet.clear();
} else {
Logger.log('Cannot found an active sheet');
throw 'Cannot found an active sheet';
}
sheet.appendRow(colNames);
sheet.setFrozenRows(1);
Logger.log('Header row created');
// fill returned data to this array
var allData = [];
var i = 0;
// loop through rows
while (results.next()) {
allData[i] = [];
// loop through columns
for (var col = 0; col < numCols; ++col) {
allData[i][col] = results.getString(col + 1);
}
++i;
}
// get the size of the array
var allDataSize = allData.length;
Logger.log('allDataSize: %s', allDataSize);
if (allDataSize == 0) {
Logger.log('%s rows returned, program terminated', allDataSize);
return;
}
// Fill data
sheet.getRange(2, 1, allDataSize, numCols).setValues(allData);
// Fix Stages data
fixStage();
} catch (e) {
Logger.log('Error occurred:\n%s', e);
} finally {
// save settings
setConfigValue(
//SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId(),
selColNum,
JSON.stringify(dbFields));
setConfigValue(
//SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId(),
filtersJsonNum,
conditions_json);
setConfigValue(
//SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId(),
colNamesNum,
JSON.stringify(colNames));
// release resources
shutdown();
// log total time elapsed
var end = new Date();
Logger.log('Time elapsed for readContacts: %sms', end - start);
}
}<!DOCTYPE html>
<meta charset="utf-8">
<!-- This CSS package applies Google styling; it should always be included. -->
<!-- link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css" -->
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jquery/css/base.css" />
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jqueryui.com/style.css" />
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
<link rel="stylesheet"
href="//mistic100.github.io/jQuery-QueryBuilder/dist/bootstrap-datepicker/dist/css/bootstrap-datepicker3.min.css" />
<link rel="stylesheet"
href="//mistic100.github.io/jQuery-QueryBuilder/dist/seiyria-bootstrap-slider/dist/css/bootstrap-slider.min.css" />
<link rel="stylesheet" href="//mistic100.github.io/jQuery-QueryBuilder/dist/selectize/dist/css/selectize.default.css" />
<link rel="stylesheet" href="//cdn.rawgit.com/mistic100/jQuery-QueryBuilder/master/dist/query-builder.min.css" />
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<!-- Optional theme -->
<!-- link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css" -->
<style>
body {
font-family: "Trebuchet MS", "Helvetica", "Arial", "Verdana", "sans-serif";
font-size: 100%;
}
label {
font-weight: bold;
}
.branding-below {
bottom: 54px;
top: 0;
}
.branding-text {
left: 7px;
position: relative;
top: 3px;
}
.logo {
vertical-align: middle;
}
.width-100 {
width: 100%;
box-sizing: border-box;
-webkit-box-sizing : border-box;
-moz-box-sizing : border-box;
}
#sidebar-value-block,
#dialog-elements {
background-color: #eee;
border-color: #eee;
border-width: 5px;
border-style: solid;
}
#sidebar-button-bar,
#dialog-button-bar {
margin-bottom: 10px;
}
#feedback { font-size: 1.4em; }
#selectable .ui-selecting { background: #FECA40; }
#selectable .ui-selected { background: #F39814; color: white; }
#selectable { list-style-type: none; margin: 0; padding: 0; width: 100%; }
#selectable li { margin: 2px; padding: 0.2em; font-size: 1.4em; height: 26px; }
</style>
<div id='main'>
<div id='accordion'>
<h3>Select Columns</h3>
<div>
<ol id="selectable">
<li class="ui-widget-content ui-selected" value='id'>
Contact ID
</li>
<li class="ui-widget-content" value='c.date_entered'>
Date Entered
</li>
<li class="ui-widget-content ui-selected" value='first_name'>
First Name
</li>
<li class="ui-widget-content ui-selected" value='last_name'>
Last Name
</li>
<li class="ui-widget-content" value='department'>
Department
</li>
<li class="ui-widget-content ui-selected" value='phone_mobile'>
Mobile
</li>
<li class="ui-widget-content" value='phone_work'>
Work Number
</li>
<li class="ui-widget-content ui-selected" value='email1'>
Email
</li>
<li class="ui-widget-content" value='primary_address_street'>
Street
</li>
<li class="ui-widget-content" value='primary_address_city'>
City
</li>
<li class="ui-widget-content" value='primary_address_state'>
State
</li>
<li class="ui-widget-content" value='primary_address_postalcode'>
Postcode
</li>
<li class="ui-widget-content ui-selected" value='japanese_name_c'>
Native Name
</li>
<li class="ui-widget-content" value='first_job_date_c'>
First Order Date
</li>
<li class="ui-widget-content" value='number_inc_c'>
Contact Number
</li>
<li class="ui-widget-content ui-selected" value='partner_c'>
Partner
</li>
<li class="ui-widget-content" value='notes_c'>
Date Entered
</li>
</ol>
</div>
<h3>Filters</h3>
<div id='querybuilder'>
</div>
</div>
<button type='button' class='btn btn-xs btn-success'
onclick='import_contacts()'>
Import Contacts
</button>
<button type='button' class='btn btn-xs btn-danger'
onclick='close_sidebar()'>
Close
</button>
</div>
<div id='loading'>
<br />
<center>Loading ... </center>
<br />
<center><img src='http://global.js.widget.eja.hk/sites/all/modules/edanz_ja/skins/aps/images/edanz_loading.gif' /></center>
</div>
<div id='debug'>
</div>
<script src="//code.jquery.com/jquery-1.11.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/momentjs/min/moment.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/bootstrap-datepicker/dist/js/bootstrap-datepicker.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/seiyria-bootstrap-slider/dist/bootstrap-slider.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/selectize/dist/js/standalone/selectize.min.js"></script>
<script src="https://mistic100.github.io/jQuery-QueryBuilder/dist/jQuery-QueryBuilder/dist/js/query-builder.standalone.min.js"></script>
<script type="text/javascript">
/* main */
var fromClause = ' from contacts as c left join contacts_cstm as cc on c.id = cc.id_c and c.deleted = "0" ';
show_loading(false);
// settings constant
var selColNum = 3;
var colNamesNum = 4;
var filtersJsonNum = 5;
var db_fields = [];
var col_names = [];
// setup accordion
$(function() {
$( "#accordion" ).accordion();
});
// selectable area
$(function() {
$( "#selectable" ).selectable({
stop: function() {
// var result = $( "#debug" ).empty();
db_fields = [];
col_names = [];
$(".ui-selected").each(function(index, items) {
db_fields.push($(items).attr('value'));
col_names.push($(items).text().trim());
});
console.log(db_fields);
console.log(col_names);
}
});
});
// jquery-query-builder
$('#querybuilder').queryBuilder({
plugins: ['sortable', 'bt-tooltip-errors'],
filters: [
{
id: 'id',
label: 'Contact ID',
type: 'string'
},
{
id: 'c.date_entered',
label: 'Date Entered',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'first_name',
label: 'First Name',
type: 'string'
},
{
id: 'last_name',
label: 'Last Name',
type: 'string'
},
{
id: 'department',
label: 'Department',
type: 'string'
},
{
id: 'phone_mobile',
label: 'Mobile',
type: 'string'
},
{
id: 'phone_work',
label: 'Work Number',
type: 'string'
},
{
id: 'email1',
label: 'Email',
type: 'string'
},
{
id: 'primary_address_street',
label: 'Street',
type: 'string'
},
{
id: 'primary_address_city',
label: 'City',
type: 'string'
},
{
id: 'primary_address_state',
label: 'State',
type: 'string'
},
{
id: 'primary_address_postalcode',
label: 'Postcode',
type: 'string'
},
{
id: 'japanese_name_c',
label: 'Native Name',
type: 'string'
},
{
id: 'first_job_date_c',
label: 'First Order Date',
type: 'date',
validation: {
format: 'YYYY-MM-DD'
},
plugin: 'datepicker',
plugin_config: {
format: 'yyyy-mm-dd',
todayBtn: 'linked',
todayHighlight: true,
autoclose: true
}
},
{
id: 'number_inc_c',
label: 'Contact Number',
type: 'integer'
},
{
id: 'partner_c',
label: 'Partner',
type: 'string'
},
{
id: 'notes_c',
label: 'Notes',
type: 'string'
}
]
});
// recover settings if any
function cbSelCols(p) {
sel_cols = p;
console.log('Recovered sel_cols: %s', sel_cols);
if (isNotEmpty(sel_cols)) {
sel_cols = JSON.parse(sel_cols);
$('#selectable > li').each(function() {
var liId = $(this).attr('value');
$(this).removeClass('ui-selected');
if ($.inArray(liId, sel_cols) != -1) {
$(this).addClass('ui-selected');
}
});
}
}
function cbFiltersJson(p) {
filters_json = p;
console.log('Recovered filters_json: %s', filters_json);
if (isNotEmpty(filters_json)) {
$('#querybuilder').queryBuilder('setRules', JSON.parse(filters_json));
}
}
var sel_cols = null;
google.script.run.withSuccessHandler(cbSelCols).getConfigValue(selColNum);
var filters_json = null;
google.script.run.withSuccessHandler(cbFiltersJson).getConfigValue(filtersJsonNum);
/* end of main */
/*
* blocking UI for loading
*/
function show_loading(show) {
if (show) {
$('#main').hide();
$('#loading').show();
} else {
$('#loading').hide();
$('#main').show();
}
}
/*
* envent handler
*/
function load_done(error) {
show_loading(false);
//$('#debug').text(error);
}
/*
* javascript wrapper for google script
*/
function import_contacts() {
db_fields = [];
col_names = [];
$(".ui-selected").each(function(index, items) {
db_fields.push($(items).attr('value'));
col_names.push($(items).text().trim());
});
var res = $('#querybuilder').queryBuilder('getSQL', false, false);
var rules_json = JSON.stringify($('#querybuilder').queryBuilder('getRules'));
show_loading(true);
console.log('db_fields: ' + db_fields);
console.log('col_names: ' + col_names);
console.log('sql_where : ' + res.sql);
console.log('rules_json: ' + rules_json);
google.script.run
.withFailureHandler(load_done)
.withSuccessHandler(load_done)
.importData(
db_fields,
col_names,
fromClause,
res.sql,
rules_json
);
//$('#debug').text(res.sql);
}
/*
* javascript wrapper for google script
*/
function close_sidebar() {
google.script.host.close();
}
/**
* String utility
*/
function isNotEmpty(string) {
if(!string) return false;
if(string == '') return false;
if(string === false) return false;
if(string === null) return false;
if(string == undefined) return false;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return false;
return true;
}
/*
* Array utility
*/
function isArray(myArray) {
return myArray.constructor.toString().indexOf("Array") > -1;
}
</script>
function dedup(column) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = [];
//var titleBar = data[0];
Logger.log('Original data size: %s', data.length);
if (!isNotEmpty(column)) {
Logger.log('Column is not specified, reset to 0(A)');
column = 0;
}
// loop all existing rows
for(i in data){
var row = data[i];
var duplicate = false;
// loop all new data
for(j in newData){
// if(row.join() == newData[j].join()) {
if (row[column] == newData[j][column]) {
Logger.log('Found duplication: %s\t%s', row[column], newData[j][column]);
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}
Logger.log('Data size after dedup: %s', newData.length);
sheet.clearContents();
//sheet.appendRow(titleBar);
//sheet.setFrozenRows(1);
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
<!DOCTYPE html>
<meta charset="utf-8">
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jquery/css/base.css" />
<link rel="stylesheet" href="//jqueryui.com/jquery-wp-content/themes/jqueryui.com/style.css" />
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
<div id='main'>
<select id='dedupCol'>
<option value="0">A</option>
<option value="1">B</option>
<option value="2">C</option>
<option value="3">D</option>
<option value="4">E</option>
<option value="5">F</option>
<option value="6">G</option>
<option value="7">H</option>
<option value="8">I</option>
<option value="9">J</option>
<option value="10">K</option>
<option value="11">L</option>
<option value="12">M</option>
<option value="13">N</option>
<option value="14">O</option>
<option value="15">P</option>
<option value="16">Q</option>
<option value="17">R</option>
<option value="18">S</option>
<option value="19">T</option>
<option value="20">U</option>
<option value="21">V</option>
<option value="22">W</option>
<option value="23">X</option>
<option value="24">Y</option>
<option value="25">Z</option>
</select>
<br />
<button type='button' class='btn btn-xs btn-success'
onclick='dedup()'>
Dedup
</button>
<button type='button' class='btn btn-xs btn-danger'
onclick='close_sidebar()'>
Close
</button>
</div>
<div id='loading'>
<br />
<center>Loading ... </center>
<br />
<center><img src='http://global.js.widget.eja.hk/sites/all/modules/edanz_ja/skins/aps/images/edanz_loading.gif' /></center>
</div>
<div id='debug'>
</div>
<script src="//code.jquery.com/jquery-1.11.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<script type="text/javascript">
/* main */
show_loading(false);
/* end main */
function dedup() {
var dedupCol = $( "#dedupCol option:selected" ).attr('value');
console.log('Dedup Column: %s', dedupCol);
show_loading(true);
google.script.run
.withFailureHandler(load_done)
.withSuccessHandler(load_done)
.dedup(dedupCol);
}
/*
* blocking UI for loading
*/
function show_loading(show) {
if (show) {
$('#main').hide();
$('#loading').show();
} else {
$('#loading').hide();
$('#main').show();
}
}
/*
* envent handler
*/
function load_done(error) {
show_loading(false);
//$('#debug').text(error);
}
/*
* javascript wrapper for google script
*/
function close_sidebar() {
google.script.host.close();
}
/**
* String utility
*/
function isNotEmpty(string) {
if(!string) return false;
if(string == '') return false;
if(string === false) return false;
if(string === null) return false;
if(string == undefined) return false;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return false;
return true;
}
/*
* Array utility
*/
function isArray(myArray) {
return myArray.constructor.toString().indexOf("Array") > -1;
}
</script>/**
* @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
*/
var DIALOG_TITLE = 'Example Dialog';
var SIDEBAR_TITLE = 'Example Sidebar';
/**
* Adds a custom menu with items to show the sidebar and dialog.
*
* @param {Object} e The event parameter for a simple onOpen trigger.
*/
function onOpen(e) {
/*
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Show sidebar', 'showSidebar')
.addItem('Show dialog', 'showDialog')
.addToUi();
*/
edanzOnOpen(e);
}
/**
* Runs when the add-on is installed; calls onOpen() to ensure menu creation and
* any other initializion work is done immediately.
*
* @param {Object} e The event parameter for a simple onInstall trigger.
*/
function onInstall(e) {
onOpen(e);
}
/**
* Opens a sidebar. The sidebar structure is described in the Sidebar.html
* project file.
*/
function showSidebar() {
var ui = HtmlService.createTemplateFromFile('Sidebar')
.evaluate()
.setTitle(SIDEBAR_TITLE);
SpreadsheetApp.getUi().showSidebar(ui);
}
/**
* Opens a dialog. The dialog structure is described in the Dialog.html
* project file.
*/
function showDialog() {
var ui = HtmlService.createTemplateFromFile('Dialog')
.evaluate()
.setWidth(400)
.setHeight(190);
SpreadsheetApp.getUi().showModalDialog(ui, DIALOG_TITLE);
}
/**
* Returns the value in the active cell.
*
* @return {String} The value of the active cell.
*/
function getActiveValue() {
// Retrieve and return the information requested by the sidebar.
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
return cell.getValue();
}
/**
* Replaces the active cell value with the given value.
*
* @param {Number} value A reference number to replace with.
*/
function setActiveValue(value) {
// Use data collected from sidebar to manipulate the sheet.
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
cell.setValue(value);
}
/**
* Executes the specified action (create a new sheet, copy the active sheet, or
* clear the current sheet).
*
* @param {String} action An identifier for the action to take.
*/
function modifySheets(action) {
// Use data collected from dialog to manipulate the spreadsheet.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var currentSheet = ss.getActiveSheet();
if (action == "create") {
ss.insertSheet();
} else if (action == "copy") {
currentSheet.copyTo(ss);
} else if (action == "clear") {
currentSheet.clear();
}
}