CODEWITHSUNDEEP
javascriptchartsgoogle-apps-scriptgoogle-sheetsgoogle-visualization
SR1
SR1

Reputation: 253

ScriptError: The script completed but the returned value is not a supported return type

So I have been experimenting with Google Apps Script and the Charts API all day, and have gotten nowhere, unfortunately.

I did many things - including following Mogsdad's well-written tutorial here. Here's the code I have so far - including from that tutorial, but I keep getting an error saying the script has completed but returned a different type from what is accepted, or something of the like.

Dashboard-Code.gs:

/**
 * Serves HTML of the application for HTTP GET requests.
 * From gist.github.com/mogsdad/24518dff348ad14d3929
 *
 * @param {Object} e event parameter that can contain information
 *     about any URL parameters provided.
 */
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('Index');

  // Build and return HTML in IFRAME sandbox mode.
  return template.evaluate()
      .setTitle('Dashboard demo')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}


/**
 * Return all data from first spreadsheet as an array. Can be used
 * via google.script.run to get data without requiring publication
 * of spreadsheet.
 * From gist.github.com/mogsdad/24518dff348ad14d3929
 *
 * Returns null if spreadsheet does not contain more than one row.
 */
function getSpreadsheetData() {
  // This does not work, see https://code.google.com/p/google-apps-script-issues/issues/detail?id=5233
//  var ss = SpreadsheetApp.getActiveSpreadsheet();
//  var data = ss.getSheets()[0].getDataRange().getValues();
  var sheetId = '1-EE_WDD6QYi257_utUf_D0v5e9Gf8Lj5tETPqqllvxk';  // Replace with your spreadsheet ID. (Ick.)
  var data = SpreadsheetApp.openById(sheetId).getSheets()[0].getDataRange().getValues();
  return (data.length > 1) ? data : null;
}

Index.html:

<!-- Use a templated HTML printing scriptlet to import common 

stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
<html>
  <body>
    <h1 id="main-heading">Loading...</h1>
    <div id="dashboard-div">
      <div id="control-div">
        <div id="selector-div">
        </div>
        <div id="selector1-div">
        </div>
      </div>
      <div id="charts-div">

        <div id="table-div">
        </div>
      </div>
    </div>
    <div class="hidden" id="error-message">
    </div>
  </body>
</html>

<!-- Store data passed to template here, so it is available to the
     imported JavaScript. -->
<script>
</script>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>

and JavaScript.html (I decided to stick to what Mogsdad was saying):

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript"  src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('45', {packages: ['corechart']});
    google.charts.setOnLoadCallback(drawChart);
</script>
<script>
  // Load the Visualization API and desired package(s).
  google.load('visualization', '1.0', {'packages':['controls']});
  /**
   * Run initializations on dialog load.
   */
  $(function() {
    // Set a callback to run when the Google Visualization API is loaded.
    // Note: could also be accomplished via google.load options.
    google.setOnLoadCallback(sendQuery);
    // Assign handler functions to dialog elements here, if needed.
    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.
  });
  /**
   * Issue asynchronous request for spreadsheet data.
   * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9
   */
  function sendQuery() {
    google.script.run
      .withSuccessHandler(drawDashboard)
      .withFailureHandler(function(msg) {
            // Respond to failure conditions here.
            $('#main-heading').text(msg);
            $('#main-heading').addClass("error");
            $('#error-message').show();
          })
      .getSpreadsheetData();
  }
  /**
   * Callback function to generate visualization using data in response parameter.
   * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9
   * 
   * @param {Object[][]}  Two-Dim array of visualization data
   */
  function drawDashboard(response) {
    $('#main-heading').addClass("hidden");
    if (response == null) {
      alert('Error: Invalid source data.')
      return;
    }
    else {
      // Transmogrify spreadsheet contents (array) to a DataTable object
      var data = google.visualization.arrayToDataTable(response,false);
      var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard-div'));


      var table = new google.visualization.ChartWrapper({
        'chartType': 'Table',
        'containerId': 'table-div'
      });

      var categoryPicker = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
        'containerId': 'selector-div',
        'options': {
          'filterColumnLabel': 'Category'
        }
      });

      var subCategoryPicker = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'selector1-div',
      'options': {
      'filterColumnLabel': 'Category'
      }
      });


      // Set up dependencies between controls and charts
      dashboard.bind([categoryPicker,subCategoryPicker], table);
      // Draw all visualization components of the dashboard
      dashboard.draw(data);
    }
  }  
</script>

I do not need the Numerical Filter, all I need is something analagous to a cascading dropdown + table.

I would appreciate any pointers. Where am I going wrong? I don't think the issue is in the JS.html file - it's probably something to do with the acquisition of data from the Google sheet.

Upvotes: 1

Views: 999

Answers (1)

WhiteHat
WhiteHat

Reputation: 61212

first, don't need both chart libraries, jsapi & loader.js

according to the release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader.js from now on.

next, a callback is being set to a function that does not exist...

here --> google.charts.setOnLoadCallback(drawChart);

recommend setup similar to following... 

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
  // Load the Visualization API and desired package(s).
  google.charts.load('45', {
    callback: sendQuery,
    packages: ['controls', 'corechart']
  });

  /**
   * Issue asynchronous request for spreadsheet data.
   * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9
   */
  function sendQuery() {
    google.script.run
      .withSuccessHandler(drawDashboard)
      .withFailureHandler(function(msg) {
            // Respond to failure conditions here.
            $('#main-heading').text(msg);
            $('#main-heading').addClass("error");
            $('#error-message').show();
          })
      .getSpreadsheetData();
  }

  /**
   * Callback function to generate visualization using data in response parameter.
   * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9
   * 
   * @param {Object[][]}  Two-Dim array of visualization data
   */
  function drawDashboard(response) {
    $('#main-heading').addClass("hidden");
    if (response == null) {
      alert('Error: Invalid source data.')
      return;
    }
    else {
      // Transmogrify spreadsheet contents (array) to a DataTable object
      var data = google.visualization.arrayToDataTable(response,false);
      var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard-div'));

      var table = new google.visualization.ChartWrapper({
        'chartType': 'Table',
        'containerId': 'table-div'
      });

      var categoryPicker = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
        'containerId': 'selector-div',
        'options': {
          'filterColumnLabel': 'Category'
        }
      });

      var subCategoryPicker = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
        'containerId': 'selector1-div',
        'options': {
          'filterColumnLabel': 'Category'
        }
      });


      // Set up dependencies between controls and charts
      dashboard.bind([categoryPicker,subCategoryPicker], table);
      // Draw all visualization components of the dashboard
      dashboard.draw(data);
    }
  }  
</script>

EDIT

working example using query class --> google.visualization.Query 

google.charts.load('45', {
  callback: sendQuery,
  packages: ['controls', 'corechart']
});

function sendQuery() {
  var url = 'https://docs.google.com/spreadsheets/d/1-EE_WDD6QYi257_utUf_D0v5e9Gf8Lj5tETPqqllvxk/edit#gid=0';
  new google.visualization.Query(url).send(function (response) {
    if (response.isError()) {
      console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    } else {
      drawDashboard(response.getDataTable());
    }
  });
}

function drawDashboard(data) {
  $('#main-heading').addClass("hidden");
  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard-div'));

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'table-div'
  });

  var categoryPicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'selector-div',
    options: {
      filterColumnLabel: 'Category'
    }
  });

  var subCategoryPicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'selector1-div',
    options: {
      filterColumnLabel: 'Category'
    }
  });

  dashboard.bind([categoryPicker,subCategoryPicker], table);
  dashboard.draw(data);
}
.hidden {
  display: none;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<h1 id="main-heading">Loading...</h1>
<div id="dashboard-div">
  <div id="control-div">
    <div id="selector-div">
    </div>
    <div id="selector1-div">
    </div>
  </div>
  <div id="charts-div">

    <div id="table-div">
    </div>
  </div>
</div>
<div class="hidden" id="error-message">
</div>

Upvotes: 2

Related Questions