Google Spreadsheet - Code to Create a window with chart that can be filtered via drop down with all data pulled from spreadsheet

gistfile1.txt

Link to the Google Spreadsheet

https://docs.google.com/spreadsheets/d/1jl1Ib1PSYeojVrdme8b6DZ6WTt896qFYqA1JFwbZUTo/edit?usp=sharing



Sheet Name "TimeSeriesAndFilterData" with the following data

Date	Example1	Example2	Filter
01/01/2015	10	20	TeamDD
02/01/2015	5	23	TeamAA
10/01/2015	20	30	TeamBB
20/01/2015	4	15	TeamBB
10/02/2015	20	20	TeamCC
20/02/2015	15	30	TeamDD
01/03/2015	10	25	TeamAA
10/03/2015	11	20	TeamCC
20/03/2015	15	15	TeamBB
01/06/2015	20	10	TeamCC
20/07/2015	25	20	TeamAA

===================

// File = Code.gs
// Requires the moment.js library for easy to use datetime functions
// https://script.google.com/macros/library/versions/d/MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48
// Use the Script Editor Resources Library setting to load into project.

// Load the library once.
var moment = Moment.load();

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // property service
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("ssID", doc.getId());

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('charts')
  .addItem('as TimeSeries dialog chart with filter', 'showTimeSeriesFilteredChart')
  .addToUi();
}

function showTimeSeriesFilteredChart() {
  var html = HtmlService.createTemplateFromFile('TimeSeriesFilteredChartIndex.html')
             .evaluate()
             .setSandboxMode(HtmlService.SandboxMode.IFRAME)
             .setWidth(900)
             .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'chart using gviz with data supplied by server function filterable by dropdown.');
}

/**
* get the getTimeSeriesFilterableData
* Need to convert the dates in the first column to text prior to sending to the client. 
*/
function getTimeSeriesFilterableData(){
  var ssID = SCRIPT_PROP.getProperty("ssID");
  // get the data in the active sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('TimeSeriesAndFilterData');
  // if there is no sheet, then fallback to a specific sheet/spreadsheet
  // because we are executing as a webapp
  if (!sheet){
           
           sheet = SpreadsheetApp
           .openById(ssID)
           .getSheetByName('TimeSeriesAndFilterData');
  }
  // Assume that first column is date which needs to be converted into text in order for transfer to client function. 
  var headers = sheet.getRange(1, 1, 1 ,sheet.getLastColumn()).getValues();
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1 ,sheet.getLastColumn()).getValues().reduce(function(p,c) {
                  p.push(c);return p;
                  },[]);
  var textdatedata = data.map(function(value,index) { return [moment(value[0]).format('YYYY-MM-DD')].concat(value.slice(1)) });
  Logger.log(textdatedata)
  return headers.concat(textdatedata);
}


/**
* get the Filter names
* Assume that the column, With a Header named ,Filter, makes up text values 
* that can be used to filter data on the client side.
* This function returns sorted list of strings that can be loaded into drowndown.
*
* An alternative approach might be to send data and filter in one as one data playload to the client and then seperate there.  
**/
function getTimeSeriesFilterData() {
// get the data in the active sheet
var ssID = SCRIPT_PROP.getProperty("ssID");
// get the data in the active sheet
var sheet = SpreadsheetApp.getActive().getSheetByName('TimeSeriesAndFilterData');
// var sheet = SpreadsheetApp.getActiveSheet();
// create a 2 dim area of the data in the carrier names column and codes
var headers = sheet.getRange(1, 1, 1 ,sheet.getLastColumn()).getValues();
var FilterIndex = headers[0].indexOf("Filter");
  
var data = sheet.getRange(2, FilterIndex+1, sheet.getLastRow()-1 ,1).getValues().reduce(function(p,c) {
                  p.push(c[0]);return p;
                  },[]);
var sortedunique = removeDuplicates(data);
return sortedunique;
}

function removeDuplicates (arr) {
    var deduper = {}
    var sort_arr = arr.sort() //=> optional
    sort_arr.forEach(function (item) {
        deduper[item] = null;
    });
    return Object.keys(deduper);
}

====Filename styles.css ==========================
<!-- 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">
<style>
.content {
padding:10px;
}
</style>

===============================

<!DOCTYPE html>
<!-- File = TimeSeriesFilteredChartIndex.html-->
<!-- styles -->
<?!= HtmlService.createHtmlOutputFromFile('styles.css').getContent(); ?>
<div class="content">
  <div class="block">
     <label for="code">Pick an Filter</label>
     <select id="codes"></select>
  </div>
  <div class = "block">
     <div id="chose"></div>
  </div>
  <div class = "block">
    <div id="chart"></div>
  </div>
  <div class = "block">
    <div id="message" class="message"></div>
  </div>
</div>

<!-- javascript. -->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<?!=HtmlService.createHtmlOutputFromFile('TimeSeriesFilteredChartMain').getContent(); ?>
<?!=HtmlService.createHtmlOutputFromFile('TimeSeriesFilteredChartClient').getContent(); ?>
<?!=HtmlService.createHtmlOutputFromFile('TimeSeriesFilteredChartApp').getContent(); ?>
<?!=HtmlService.createHtmlOutputFromFile('TimeSeriesFilteredChartRender').getContent(); ?>


===============
<!-- File = TimeSeriesFilteredChartMain.html -->
<script>
// the app
google.load('visualization', '1.0', {'packages':['corechart']});
// wait for the viz lib to have loaded
google.setOnLoadCallback (function () {
  // kick by getting data, and building page
  Client.getTimeSeriesFilterData (function (data) {
    
    // store the data
    App.globals.data = data;
    
    // render the chart
    Render.build();
    
    // add any listeners
    App.listeners = function () {
      document.getElementById("codes").addEventListener ("change", function (e) {
      Render.showChoice(e);
      });
});
</script>

==================


<!-- File = TimeSeriesFilteredChartRender.htm -->
<script>
/**
* anything to do with rendering happens here
*/
var Render = (function (render) {
  /**
  * display the selection made on change
  * @param {object} e returned by change event
  */
  render.showChoice = function (e) {
  document.getElementById("chose").innerHTML = 'You chose ' + e.target.value;
  };
  /**
  * build selector
  */
  render.build = function () {
  // the select element is already in place
  var selector = document.getElementById ("codes");

  // add the options
  App.globals.data.forEach (function (d) {
    selector.appendChild(new Option ( "(" +d+ ") " + d, d ));
    });
  };
  return render;
})(Render || {});
</script>

==================

<!-- TimeSeriesFilteredChartApp.html -->
<script>
// app related globals
var App = (function(app) {
  /**
  * report a message
  * @param {string} message the message
  */
  app.reportMessage = function (message) {
  document.getElementById("message").innerHTML = message;
  };
  /**
  * any global stuff/settings is here
  */
  app.globals = {};
  /**
  * add listeners
  */
  app.listeners = function () {
    document.getElementById("codes").addEventListener ("change", function (e) {
    Render.showChoice(e);
    });
  };
return app;
}) (App || {});
</script>
============


<!-- File = TimeSeriesFilteredChartClient.html -->
<script>
// all code for client-server communication
var Client = (function(client) {
  client.getTimeSeriesFilterData = function (successFunction) {
  // this executes a function asynchronously on the server
  // under control of the client
  google.script.run
    // this will be executed if it fails
    .withFailureHandler(function (err) {
       App.reportMessage (err);
       })
    // this will be executed if it succeeds
    .withSuccessHandler (successFunction)
    // this is what gets executed
    .getTimeSeriesFilterData();
    };
  return client;
})(Client || {});
</script>
===============