setDataAsync with Office.CoercionType.Table

Question

My first day hacking with Napa and I'm struggling...

I'm struggling to understand the binding process for TableData. I've worked out a simple example with the Matrix type and can read data from an existing table:

Office.context.document.bindings.addFromNamedItemAsync("Sheet1!Table1", Office.BindingType.Table, { id: "bindingTable" }, function (asyncResult) {
	if (asyncResult.status === Office.AsyncResultStatus.Succeeded) {
	    asyncResult.value.getDataAsync(function (asyncResult) {
	        if (asyncResult.status === Office.AsyncResultStatus.Succeeded) {
	            console.log(asyncResult.value);
	        }
	    });
	}
});

but I am unable to write data to a table:

Office.context.document.bindings.addFromNamedItemAsync("Sheet1!Table1", Office.BindingType.Table, { id: "bindingTable" }, function (asyncResult) {
	if (asyncResult.status === Office.AsyncResultStatus.Succeeded) {
	    var myTable = new Office.TableData;
	    myTable.headers = [["ID"],["Name"]];
	    myTable.rows = [[1,"Fred"],[2,"Jane"]];
	    asyncResult.value.setDataAsync(myTable, { coercionType: Office.CoercionType.Table }, function (asyncResult) {
	        console.log(asyncResult.status);
	    });
	}
});

Specifically, the error thrown is "Data Write Error", "The supplied data object type is not supported".

What am I doing wrong?

Also, can I programmatically generate tables on a sheet?

Thanks!

Answer 1

Aha! I stumbled upon the answer... the binding returned from a Office.BindingType.Table is an abstract class BindingTable that has additional methods including addRowsAsync.

Office.context.document.bindings.addFromNamedItemAsync("Sheet1!Table1", Office.BindingType.Table, { id: "bindingTable" }, function (asyncResult) {
    if (asyncResult.status === Office.AsyncResultStatus.Succeeded) {
        asyncResult.value.addRowsAsync([[3, "Henry"]], null, function (asyncResult) {
        console.log(asyncResult.status);
    });
}

Answer 2

Hello there, I'm glad you found the addRowsAsyncMethod :) If instead, you'd like to update existing rows and columns, you can use the setDataAsync. For the TableData object, you need to make sure that the shape (number of columns and rows) matches the existing table to update the entire table. If you just want to update specific rows/columns, you can add a startColumn and startRow as optional parameters. To demonstrate, I added a quick snippet: The setTable() method sets a 3x3 table and binds to it. The updateTable() methods, finds the binding and updates the 3rd column with new data. The code doesn't handle error cases so please use with caution :)

function setTable() {

    var myTable = new Office.TableData();
    myTable.headers = ["First Name", "Last Name", "Show"];
    myTable.rows = [["Steve", "Carell", "The Office"], ["Liz", "Lemon", "30 Rock"], ["Amy", "Poehler", "Parks and Recreation"]];
    Office.context.document.setSelectedDataAsync(myTable,  function (asyncResult) {
       Office.context.document.bindings.addFromSelectionAsync(Office.BindingType.Table, { id: "myBinding" }, function (result) {});
    });
  
}

// Replace last column with different data.
function updateTable() {
    var newTable = new Office.TableData();
    newTable.headers = ["Gender"];
    newTable.rows = [["M"],["F"],["F"]];
    Office.select("bindings#myBinding").setDataAsync(newTable, { coercionType: "table", startColumn:2 }, function (asyncResult) {});   
}

Answer 3

Hila

I really appreciate your taking the time to answer and help me with this

Now that I've found the 'addRowsAsync' method, the 'setDataSync' methods makes more sense; it's really 'update' rather than a general write/update.

I blogged about my experience thus far and looking forward to continuing the project.

Is there a way to create Excel tables using the API? It would appear that the various table-related methods in the API all expect the table to be extant.

Answer 4

Great blog post! Exciting to read about your first steps with the new API :) The way to insert a new table into the document is by using the setSelectedDataAsync method. It will insert the data into the user current selection in the document. We have a great topic in our documentation that can help: Reading and Writing Data to the Active Selection in a Document or a Spreadsheet. Here is a quick snippet of the API:

 Office.context.document.setSelectedDataAsync(myTable,  function (asyncResult) {
       //error handling, do something once the table is set...

 });

myTable is a TableData object that can be easily created:

  var myTable = new Office.TableData();
    myTable.headers = ["First Name", "Last Name", "Show"];
    myTable.rows = [["Steve", "Carell", "The Office"], ["Liz", "Lemon", "30 Rock"], ["Amy", "Poehler", "Parks and Recreation"]];

Let me know if you have any more questions! 

Hila

Answer 5

Terrific! Thank you.

Answer 6

Great! We're actually working on improving our documentation and I was wondering if you'd be willing to provide feedback? I'm mostly interested in the documentation/blog posts/samples you found the most effective.

Thanks,

Hila

Answer 7

I would be happy to help. Clearly the documentation is in some state of flux as it tries to remain sync'd with the API's evolution but, on the whole, the documentation's up to Microsoft's customary high standards.

Have valued the overview articles and the code samples have been helpful. Tried one of the Bing maps samples but failed at it and didn't persevere. Honestly, reverted to Google Maps with which I'm more familiar. \

Would value some 'next level' examples. Is there deployment guidance? Am confused as to how that would work and all I've found starts with "when you're ready to publish".

Would value some guidance on deployment best practices etc.

You can mail me at first.last at live.com too.

Answer 8

Hila

I believe there's a bug in the version of the JavaScript API that I'm using. I followed your code samples above for the creation of a table.

However, contrary to the documentation, if the rows propoerty is null or empty ([]) when the table is created with setSelectedDataSync, the AsyncResultStatus is Failed and the table is not created.

http://msdn.microsoft.com/en-us/library/fp160994(v=office.15).aspx

It would appear that this *only* works if row data is passed. I'm having to pass an array of empty columns to achieve the intended result:

rows: [["","","","","","","",""]]

Daz