DataTable population using google script call function

Abrham

So I'm working with a Google Script. The script pulls information from a google spreadsheet and should be displaying it in the datatable. The table is creating correctly and using the columns I've defined, the data just isn't pulling in. When I debug my script the dataset is populated and should be returned from the function.

Are data tables able to be loaded from a function returning the dataset?

<script>
$(document).ready(function() {
        $('#customerlist').DataTable( {
            data: google.script.run.getData(),
            columns: [
              { title: "Company" },
              { title: "Service Type" },
              { title: "Email" },
              { title: "Contact1" },
              { title: "Cell #" },
              { title: "Contact 2" },
              { title: "Cell #" },
              { title: "Contact 3" },
              { title: "Cell #" },
              { title: "Contact 4" },
              { title: "Cell #" },
              { title: "Contact 5" },
              { title: "Cell #" },
              { title: "Contact 6" },
              { title: "Cell #" },
              { title: "Contact 7" },
              { title: "Cell #" },
              { title: "Contact 8" },
              { title: "Cell #" },
              { title: "Contact 9" },
              { title: "Cell #" }
            ]
        } );
      } );
    </script>

function getData(){
  var id = "abc";
  var sheetname = "Master";
  var dataSet = SpreadsheetApp.openById(id).getSheetByName(sheetname).getDataRange().getValues();
  
  return dataSet;
}

kthorngren

Using a function does seem to work here with an ajax (with async false).
http://live.datatables.net/meragiso/1/edit

So it would seem your function should work as long as its not an async operation. What does the variable dataset contain? Is it a javascript array or a JSON string?

If JSON string you will need to use JSON.parse() to convert it to a javascript array.

If this doesn't help then please post the first few rows of the dataset variable so we can see what it contains.

Do you get any alert errors or errors in the browser's console?

Kevin

Abrham

Seems like a javascript array to me. I'm copying from the script debugger.

[["avdb", "Database - Microsoft SQL Server", "vl@xti.com", "Microsoft SQL Server Primary", "111-387-0974", "Kat Meows", "119-129-8419", "Tony Sao", "111-310-0975", "Dennis Rbin... ["800art", "Database - Microsoft SQL Server", "vmssql@xtivia.com", "Microsoft SQL Server Primary", "111-387-0974", "Kat Mows", "111-339-8419", "Tony Srno", "111-310-0975", "Dennis Rins... ["A A D A (Ana Autoile ers Ation)", "Database - Microsoft SQL Server", "vml@xta.com", "Microsoft SQL Server Primary", "111-387-0974", "Kat Mws", "111-339-8419", "Tony...

Abrham

Hmm It seems I have an array of arrays, which means I may have to use the columns.data extension to display the data?

kthorngren

I'm not sure if the google API's are asynchronous or not. My suggestion would be to initialize your Datatable without the data: google.script.run.getData(), so you will start with an empty table. Then call the getData() function and use rows.add() to add the data. The function would look like this:

function getData(){
  var id = "abc";
  var sheetname = "Master";
  var dataSet = SpreadsheetApp.openById(id).getSheetByName(sheetname).getDataRange().getValues();
   
  $('#customerlist').DataTable().rows.add( dataSet ).draw();
}

Assuming you are not getting any errors and you have at least 21 elements in each array this should work. If not then can you post a link to your page so we can take a look?

You only want to use columns.data if you have an array of objects. Since you have an array of arrays the - option columns.title will help you with populating the text of the column headers.

Kevin

kthorngren

Does the table show something like No data available in table or something that looks like blank rows?

Kevin

Abrham

No data available in table.

Abrham

I added the title attributes for columns, I also added :

```
$('#customerlist').DataTable().rows.add( dataSet ).draw();

to initialize the dataset and removed the return, still getting no data available in table. I will have to create a test project because all the data is proprietary. I'll share the link when I get it together.

Abrham

Let me know if you can access the app from here

https://script.google.com/macros/s/AKfycbyPQQ6cbkaqYz6p8qwsqXSUCzFKKV-uelTkhTIDnkM-EQlNLzLe/exec

kthorngren

Before doing that I would try using JSON.parse(dataSet) to make sure its not a JSON string.

Kevin

Abrham

It's not parsing

SyntaxError: Unexpected token: C

Abrham

Figured it out, I believe. When using the script run command for google, you have to use a successor handler to display the data and call the document ready function.

HTML

<script>
    
      function dispData(dataSet) {
        $(document).ready(function() {
          $('#customerlist').DataTable( {
            data: dataSet,
            columns: [
                { "title": "Customer Company" },
                { "title": "Service Type" },
                { "title": "Email" },
                { "title": "Contact1" },
                { "title": "Cell #" },
                { "title": "Contact 2" },
                { "title": "Cell #" },
                { "title": "Contact 2" },
                { "title": "Cell #" },
                { "title": "Contact 2" },
                { "title": "Cell #" }
              ]
          } );
        } );
      }
      
      google.script.run.withSuccessHandler(dispData).loadData();
     
    </script>

code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function loadData(){
  var spreadsheet = "Sheet1";
  var id = "1GhiI79GHHNeUTYpiXJm52XTaxByKrrjNrgw-3F2ZqhU"
  var data = SpreadsheetApp.openById(id).getSheetByName(spreadsheet).getDataRange().getValues();
  
  return data;
}