none
Task Pane App Excel - The supplied data object type is not supported error recieved RRS feed

  • Question

  • I believe I am passing in an array of arrays in the following code. However I get data object type not supported.

    function bindingForAllCsv(csvdict) {
        var end = 0;
        var res = csvdict[3].Value.split("\r\n");
        var type = "16";
        var rowct = res.length;
        var namedRange = "A" + (end + 1) + ":K" + (end + rowct + 1);
        var id = "Rec" + type;
        var bnding = "bindings#" + id;
        Office.context.document.bindings.addFromNamedItemAsync(namedRange, "matrix", { id: id },
            function(asyncResult) {
                if (asyncResult.status == "failed") {
                    write('Error: ' + asyncResult.error.message);
                }
            });
                //Calculate field arrays and put in row arrays
        var row = [];
        var fields = [];
        for (var j = 0; j < res.length; j++) {
            fields = res[j].split(",");
            row.push(fields);
        }
        Office.select(bnding).setDataAsync(row, { coercionType: "matrix" },
            function(asyncResult) {
                if (asyncResult.status == "failed") {
                    write('Error: ' + asyncResult.error.message);
                }
            });
        }
    I'm not real adept at javascript so I am not sure I have an array of arrays.

    John Donnelly

    Wednesday, May 21, 2014 9:37 PM

Answers

  • Hi John,

    To write a data table to a Range in the worksheet, you need to define the parameter like this:

    function bindingForAllCsv(csvdict) {
            var end = 0;
            var myTable = new Office.TableData();
            myTable.header = ['RecordCode', 'TypeCode', 'CodeDesc', 'SummCode', 'SummCodeDesc', 'Sign', 'Amount', 'FundsType', 'BankRefNum', 'CustRefNum', 'DescText'];
            myTable.rows = [['RecordCode', 'TypeCode', 'CodeDesc', 'SummCode', 'SummCodeDesc', 'Sign', 'Amount', 'FundsType', 'BankRefNum', 'CustRefNum', 'DescText'],['16', '169', '1', '2', '3', '4', '5', '6', '7', '8', '9'], ['16', '169', '1', '2', '3', '4', '5', '6', '7', '8', '9']];
            var type = "3";
            var rowct = myTable.length;
            var namedRange = "A" + (end + 1) + ":K" + (type);
            var id = "Rec" + type;
            var bnding = "bindings#" + id;
            Office.context.document.bindings.addFromNamedItemAsync(namedRange, "table", { id: id },
                function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        write('Error: ' + asyncResult.error.message);
                    }
                });
            Office.select(bnding).setDataAsync(myTable, { coercionType: "table" },
                function (asyncResult) {
                    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                        write('Error: ' + asyncResult.error.message);
                    } else {
                        write('Bound data: ' + asyncResult.value);
                    }
                });
        }

    The result:

    For more information, please refer to Binding.setDataAsync method (JavaScript API for Office v1.1).


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 26, 2014 2:34 AM
    Moderator
  • Hi John,

    the struct of Office.TableData is like this:

    Please try:

    function createTablefromTemplate(optionVal) {
            var tableData = new Office.TableData();
            var res = optionVal.split("\r\n");
            for (var i = 0; i < res.length; i++) {
                var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
                if (i === 0) {
                    tableData.Header = jQuery.makeArray(datasource);
                }
                tableData.rows.push(jQuery.makeArray(datasource));
            }
            return tableData;
        }

    Here is the function:

    function bindingForAllCsv(csvdict) {
            var end = 0;
            var myTable = new Office.TableData();
    
            myTable = createTablefromTemplate(csvdict);
            var type = myTable.rows.length;
            var namedRange = "A" + (end + 1) + ":K" + (type);
            var id = "Rec" + type;
            var bnding = "bindings#" + id;
            Office.context.document.bindings.addFromNamedItemAsync(namedRange, "table", { id: id },
                function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        app.showNotification('Error: ' , asyncResult.error.message);
                    }
                });
            Office.select(bnding).setDataAsync(myTable, { coercionType: "table" },
                function (asyncResult) {
                    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                        app.showNotification('Error: ' , asyncResult.error.message);
                    } else {
                        app.showNotification('Bound data: ', asyncResult.value);
                    }
                });
        }

    If I define the parameter like this:

    var car1 = "['RecordCode','TypeCode','CodeDesc','SummCode','SummCodeDesc','Sign','Amount','FundsType','BankRefNum','CustRefNum','DescText']\r\n['16','169','1','2','3','4','5','6','7','8','9']\r\n['16','169','1','2','3','4','5','6','7','8','9']";

    the result:

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 30, 2014 3:24 AM
    Moderator
  • Hi John,

    Suppose the data you got is not generated by yourself.

    you could contact with the engineer to add headers into data.

    Also, if the header is fixed and you have reached a consensus, you ould set header like this:

    	function createTablefromTemplate(optionVal) {
    	        var tableData = new Office.TableData();
    	        var res = optionVal.split("\r\n");
    	        var headerlist = "RecordCode,TypeCode,CodeDesc,SummCode,SummCodeDesc,Sign,Amount,FundsType,BankRefNum,CustRefNum,DescText";
    	        tableData.Header = jQuery.makeArray(datasource);
    	        for (var i = 0; i < res.length; i++) {
    	            var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
    	            tableData.rows.push(jQuery.makeArray(datasource));
    	        }
    	        return tableData;
        }
    Hope this helps.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 30, 2014 6:11 PM
    Moderator

All replies

  • Hi John,

    How do you define the array named “csvdict”?

    Which line caused the issue while debugging the function?

    What is the whole issue message?

    I would suggest you debugging the code and watch every variables’ value.

    I made a test in a Task Pane App.

    I changed the code below:

    var res = csvdict[3].Value.split("\r\n");

    To:

    var res = csvdict[3].split("\r\n");

    And call this function like this:

    var cars = ["Benz", "BMW", "Audi", "Volks\r\nwagen"];
    
    $('#BindForCSV').click(bindingForAllCsv(cars));
    

    The code works fine for me:

    Is this the case?

    If the information above does not give you help, would you mind sharing a simple sample for us to trouble shoot?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 22, 2014 10:02 AM
    Moderator
  • Hi George,

    Thanks for your response. The csvdict is a dictionary object that comes back from an Ajax call to the server. It has a Key and a Value properties. The Key property being the type of record or line from a csv file and the Value property is multiple lines from the csv file like so:

    csvdict[0].Key = "16"
    
    csvdict[0].Value = "RecordCode,TypeCode,CodeDesc,SummCode,SummCodeDesc,Sign,Amount,FundsType,BankRefNum,CustRefNum,DescText \r\n 
    
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,17084676,,,00000000000,- OTHER \r\n 
    
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,5247327,,,00000000000,- OTHER"

    The error happens when I call setDataAsync and the message comes from asyncResult.error.message using this function:

    function write(message) {
        app.showNotification(message);
    }

    I have stepped through the code and the example you give works for me as well. The only difference between your code and my constructed array of array's is that my values aren't enclosed in quotes. Mine are as follows:

    [[RecordCode,TypeCode,CodeDesc,SummCode,SummCodeDesc,Sign,Amount,FundsType,BankRefNum,CustRefNum,DescText],
    
    [16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,17084676,,,00000000000,- OTHER],
    
    [16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,5247327,,,00000000000,- OTHER]];

    I guess the bottom line is I can pass literals in just fine but I don't know how to construct the right object to pass in. I'm thinking that I could iterate through and enclose all values in quotes but that seems like the hard way.

    Thanks, John


    John Donnelly

    Thursday, May 22, 2014 3:43 PM
  • Hi John,

    If I define the array as below:

    var car1 = "Benz";
    var car2 = "BMW";
    var car3 = "Audi";
    var car4 = "Volks\r\nwagen";
    var cars = [car1, car2, car3, car4];

    The code is also fine.

    I believe the issue is related to the array.

    After you debug the code at this line:

    var res = csvdict[3].Value.split("\r\n");

    What do "csvdict" and "res" look like?

    Would you mind providing a simple value for us to test?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 23, 2014 7:55 AM
    Moderator
  • Thanks George. I'm not sure what you mean by sample but I can return any object from the server so I'm thinking maybe a sample of csvdict as the original string variable before I parse and create the dictionary object. Here it is with a header and the "16" records/lines:

    RecordCode,TypeCode,CodeDesc,SummCode,SummCodeDesc,Sign,Amount,FundsType,BankRefNum,CustRefNum,DescText
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,17084676,,,00000000000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,5247327,,,00000000000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,2155726,,,00000000000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,1574241,,,00000000000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,1393625,,,00032060000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,958762,,,00000000000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,881747,,,00032060000,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,64691,,,05276400884,- OTHER REFERENCE
    16,169,ACH Miscellaneous,140,Average Closing Ledger MTD,Credit,3266,,,05276400884,- OTHER REFERENCE

    This is the res variable right after debugging  the line you mentioned:

     

    This is the csvdict variable right after as well:

    Thanks for your help, John


    John Donnelly

    Friday, May 23, 2014 3:21 PM
  • Hi John,

    To write a data table to a Range in the worksheet, you need to define the parameter like this:

    function bindingForAllCsv(csvdict) {
            var end = 0;
            var myTable = new Office.TableData();
            myTable.header = ['RecordCode', 'TypeCode', 'CodeDesc', 'SummCode', 'SummCodeDesc', 'Sign', 'Amount', 'FundsType', 'BankRefNum', 'CustRefNum', 'DescText'];
            myTable.rows = [['RecordCode', 'TypeCode', 'CodeDesc', 'SummCode', 'SummCodeDesc', 'Sign', 'Amount', 'FundsType', 'BankRefNum', 'CustRefNum', 'DescText'],['16', '169', '1', '2', '3', '4', '5', '6', '7', '8', '9'], ['16', '169', '1', '2', '3', '4', '5', '6', '7', '8', '9']];
            var type = "3";
            var rowct = myTable.length;
            var namedRange = "A" + (end + 1) + ":K" + (type);
            var id = "Rec" + type;
            var bnding = "bindings#" + id;
            Office.context.document.bindings.addFromNamedItemAsync(namedRange, "table", { id: id },
                function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        write('Error: ' + asyncResult.error.message);
                    }
                });
            Office.select(bnding).setDataAsync(myTable, { coercionType: "table" },
                function (asyncResult) {
                    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                        write('Error: ' + asyncResult.error.message);
                    } else {
                        write('Bound data: ' + asyncResult.value);
                    }
                });
        }

    The result:

    For more information, please refer to Binding.setDataAsync method (JavaScript API for Office v1.1).


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 26, 2014 2:34 AM
    Moderator
  • Ok thanks George but I also wanted to offer up another answer I worked out yesterday. I was thinking about this wrong in that I though the setDataAsync method would accept an object and save the creation of the array but I couldn't find an object that I could pass in. However here are the statements that worked for me and this blog post helped get me on track.

    tableData.headers = [hdrFields[0], hdrFields[1], hdrFields[2], hdrFields[3], hdrFields[4],
     hdrFields[5], hdrFields[6], hdrFields[7], hdrFields[8], hdrFields[9],
     hdrFields[10]];
    
    myTable.rows.push([dataFields[0], dataFields[1], dataFields[2], dataFields[3], dataFields[4],
     dataFields[5], dataFields[6], dataFields[7], dataFields[8],
     dataFields[9], dataFields[10]]);
    Thanks for your help.


    John Donnelly

    Monday, May 26, 2014 3:24 PM
  • Hi John,

    I suspect that your row arrays had the different fields count, for example:

    [["Hello", "World"], ["R1C1", "R1C2"], ["R2C1", "R2C2", "R2C3"]]

    Is that the case? To resolve this issue, please make sure each row has the same fields count.

    The code below is for your test:

    /// <reference path="../App.js" />
    
    (function () {
        "use strict";
    
        Office.initialize = function (reason) {
            $(document).ready(function () {
                app.initialize();
    
                $('#add-named-item').click(addNamedItem);
            });
        };
    
    
        function addNamedItem() {
            var namedRange = "A2:K10",
                id = "mybinding",
                binding = "bindings#" + id;
    
            Office.context.document.bindings.addFromNamedItemAsync(namedRange, "matrix", { id: id },
               function (asyncResult) {
                   if (asyncResult.status == "failed") {
                       app.showNotification('Error:', asyncResult.error.message);
                   } else {
    
                       Office.select(binding).setDataAsync([["Hello", "World"], ["R1C1", "R1C2"], ["R2C1", "R2C2"]], { coercionType: "matrix" },
                        function (asyncResult) {
                            if (asyncResult.status == "failed") {
                                app.showNotification('Error:', asyncResult.error.message);
                            }
                        });
                   }
               });
        }
    })();

    Please let me know whether it works for you.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 29, 2014 7:26 AM
    Moderator
  • Thanks Jeffrey that works for me. However, and if you want me to start a new question I will, I switched to a table (from the matrix) mid-stream in this thread but the issue is the same; how to set up the array of arrays programmatically. I created a huge switch statement based on the length of the array holding the hdrFields or dataFields above which seems the hard way. Is there a way to do this with a For loop? Here's my current code:

    function createTablefromTemplate(optionVal) {
                var tableData = new Office.TableData();
                var res = optionVal.split("\r\n");
                var hdrFields = [];
                var dataFields = [];
                for (var i = 0; i < res.length; i++) {
                    if (i === 0) {
                        hdrFields = res[i].split(",");
                        var len = hdrFields.length;
                        switch (len) {
                            case 3:
                                tableData.headers = [hdrFields[0], hdrFields[1], hdrFields[2]];
                                break;
                            case 4:
                                tableData.headers = [hdrFields[0], hdrFields[1], hdrFields[2], hdrFields[3]];
                                break;
                            etc.

    And for the data fields:

            case 3:
                tableData.rows.push([dataFields[0], dataFields[1], dataFields[2]]);
                break;
            case 4:
                tableData.rows.push([dataFields[0], dataFields[1], dataFields[2], dataFields[3]]);
                break;

    Here's what I would like to see work but I can't get it to:

    function createTablefromTemplate(optionVal) {
                var tableData = new Office.TableData();
                var res = optionVal.split("\r\n");
                var hdrFields = [];
                var dataFields = [];
                for (var i = 0; i < res.length; i++) {
                    if (i === 0) {
                        hdrFields = res[i].split(",");
                        var hdrlen = hdrFields.length;
                        for (var j = 0; j < hdrlen; j++) {
                            if (j === 0) {
                                tableData.headers = [hdrFields[j]];
                            } else {
                                tableData.headers = tableData.headers + "," + [hdrFields[j]];
                            }
                        }
                    } else {
                        dataFields = res[i].split(",");
                        var datalen = dataFields.length;
                        for (var k = 0; k < datalen; k++) {
                            tableData.rows.push([dataFields[k]]);
                        }
                    }
                }
                return tableData;
            }
        }
    Basically I'm looking for a programmatic way to load the two-dimensional array other than the clumsy switch statement above.


    John Donnelly

    Thursday, May 29, 2014 4:14 PM
  • Hi John,

    the struct of Office.TableData is like this:

    Please try:

    function createTablefromTemplate(optionVal) {
            var tableData = new Office.TableData();
            var res = optionVal.split("\r\n");
            for (var i = 0; i < res.length; i++) {
                var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
                if (i === 0) {
                    tableData.Header = jQuery.makeArray(datasource);
                }
                tableData.rows.push(jQuery.makeArray(datasource));
            }
            return tableData;
        }

    Here is the function:

    function bindingForAllCsv(csvdict) {
            var end = 0;
            var myTable = new Office.TableData();
    
            myTable = createTablefromTemplate(csvdict);
            var type = myTable.rows.length;
            var namedRange = "A" + (end + 1) + ":K" + (type);
            var id = "Rec" + type;
            var bnding = "bindings#" + id;
            Office.context.document.bindings.addFromNamedItemAsync(namedRange, "table", { id: id },
                function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        app.showNotification('Error: ' , asyncResult.error.message);
                    }
                });
            Office.select(bnding).setDataAsync(myTable, { coercionType: "table" },
                function (asyncResult) {
                    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                        app.showNotification('Error: ' , asyncResult.error.message);
                    } else {
                        app.showNotification('Bound data: ', asyncResult.value);
                    }
                });
        }

    If I define the parameter like this:

    var car1 = "['RecordCode','TypeCode','CodeDesc','SummCode','SummCodeDesc','Sign','Amount','FundsType','BankRefNum','CustRefNum','DescText']\r\n['16','169','1','2','3','4','5','6','7','8','9']\r\n['16','169','1','2','3','4','5','6','7','8','9']";

    the result:

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 30, 2014 3:24 AM
    Moderator
  • Thanks George. This is definitely the right stuff. However, I get a table that has no headers and it has what should be the headers showing up as data in the first row as follows:

    I'm not proficient enough to debug this quite yet so your help would be greatly appreciated.


    John Donnelly

    Friday, May 30, 2014 10:57 AM
  • Hi John,

    Suppose the data you got is not generated by yourself.

    you could contact with the engineer to add headers into data.

    Also, if the header is fixed and you have reached a consensus, you ould set header like this:

    	function createTablefromTemplate(optionVal) {
    	        var tableData = new Office.TableData();
    	        var res = optionVal.split("\r\n");
    	        var headerlist = "RecordCode,TypeCode,CodeDesc,SummCode,SummCodeDesc,Sign,Amount,FundsType,BankRefNum,CustRefNum,DescText";
    	        tableData.Header = jQuery.makeArray(datasource);
    	        for (var i = 0; i < res.length; i++) {
    	            var datasource = res[i].substring(1, res[i].length - 1).replace(/'/g, '').split(",");
    	            tableData.rows.push(jQuery.makeArray(datasource));
    	        }
    	        return tableData;
        }
    Hope this helps.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 30, 2014 6:11 PM
    Moderator
  • Thanks George. The headers change depending on selectbox optionVal. I can pull the headers with the switch statement mentioned above and use your code for the data rows. Thanks for your help on this, it really taught me a lot about javascript and the API.

    John Donnelly


    • Edited by J_Donnelly Friday, May 30, 2014 8:46 PM
    Friday, May 30, 2014 8:45 PM
  • Hi John,
    You're welcome.

    Glad to hear you got it to work. Good luck with your project.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 2, 2014 1:55 AM
    Moderator