In my add-in I am making an HTTP request and receiving an output. I want to place that output into a binding and have it expand the binding if necessary because the user won't necessarily know how many rows x columns the output will be. How would I go about doing this? Currently I am binding to a range, but if that range does not match the size of the [[]] that I am providing, then the data is not displayed in the sheet. So, this ends up requiring the user to know the size of the output.
What I'm doing currently using Angular is as follows (the problem with this being that the output isn't always the same size as the Office.BindingType.Matrix that the user selected in the spreadsheet):
I create the binding to where the output should be placed as follows:
inputBindFromPrompt(parameterId: number): Promise<IOfficeResult> {
let bindType: Office.BindingType;
if(this.inputBindings[parameterId].type != 'data.frame' && this.inputBindings[parameterId].type != 'vector') {
bindType = Office.BindingType.Text;
} else {
bindType = Office.BindingType.Matrix;
}
return new Promise((resolve, reject) => {
this.workbook.bindings.addFromPromptAsync(bindType, { id: this.inputBindings[parameterId].name },
(addBindingResult: Office.AsyncResult) => {
if(addBindingResult.status === Office.AsyncResultStatus.Failed) {
reject({
error: 'Unable to bind to workbook. Error: ' + addBindingResult.error.message
});
} else {
this.inputBindings[parameterId].binding = addBindingResult.value;
resolve({
success: 'Created binding ' + addBindingResult.value.type + ' on ' + addBindingResult.value.id
});
}
})
})
}
Then when the user submits via a button, the inputs are passed to a HTTP request service which then receives an output that I process into an array of arrays so that it can go into an Office.BindingType.Matrix:
this.isBusy = true;
this.feedback = 'submitted';
// Grab the values from the form
// Send as a POST and receive an output
// Put the output in the Excel sheet
this.webServicesService.postWebServices(this.service, this.inputParameters)
.subscribe(
(data: any) => {
// Correctly received data
// Access the data by name while looping through output parameters
this.error = false;
this.feedback = 'received data';
let i = 0;
this.outputParameters.forEach(element => {
// temporary name to identify the parameter
let name = element.name;
// Set the data value in the parameter
if(element.type == 'data.frame') {
let parameter = data[name];
this.feedback = parameter;
let excelData = [];
for(var key in parameter) {
if(parameter.hasOwnProperty(key)) {
var val = parameter[key];
excelData.push(val);
}
}
element.value = excelData;
}
else {
element.value = data[name];
}
// Set value in the form
let param = (<FormArray>this.serviceForm.controls['outputParameters']).at(i);
param.patchValue({
value: element.value
});
// Set value in the spreadsheet
this.excelService.outputSetText(i, element.value)
.then((result: IOfficeResult) => {
this.onResult(result);
i++;
});
}, (result: IOfficeResult) => {
this.onResult(result);
});
},
(error) => {
if(error.status == 400 || error.status == 401) {
// Return user to authentication page
this.authService.logout();
this.router.navigate(['/']);
} else {
// Tell user to try again
this.error = true;
}
}
);
The line above that is setting the value to the Office.Matrix.Binding is this.excelService.outputSetText(i, element.value), which calls this method in the Excel Service:
outputSetText(parameterId: number, data: any): Promise<IOfficeResult> {
return new Promise((resolve, reject) => {
if(this.outputBindings[parameterId].binding) {
this.outputBindings[parameterId].binding.setDataAsync(data, function (result: Office.AsyncResult) {
if(result.status == Office.AsyncResultStatus.Failed) {
reject({ error: 'Failed to set value. Error: ' + result.error.message });
} else {
let test: Office.Binding;
resolve({
success: 'successfully set value'
});
}
})
} else {
reject({
error: 'binding has not been created. bindFromPrompt must be called'
});
}
})
}
It's essentially using addFromPromptAsync() to set an output spot for the HTTP request. Then the user submits which sends the request, receives the data back and processes it into an array of arrays [[]] so that it can be the correct data format for Office.BindingType.Matrix. However, unless this is the same number of rows and columns as the binding originally selected, it won't display in the sheet. So, is there a binding type that will dynamically grow based on the data I give it? Or would I just need to release the current binding and make a new binding according to the size of the HTTP response data?
So long as you're using the "shared" (Office 2013) APIs, you will have this issue.
However, in the host-specific (2016+) APIs, you can easily solve the problem by resizing the range to suit your needs. Or more precisely, getting the binding, then asking for its range, then getting just the first (top-left) cell, and then resizing it:
await Excel.run(async (context) => {
let values = [
["", "Price"],
["Apple", 0.99],
["Orange", 1.59],
];
let firstCell = context.workbook.bindings.getItem("TestBinding").getRange().getCell(0, 0);
let fullRange = firstCell.getResizedRange(
values.length - 1, values[0].length - 1);
fullRange.values = values;
await context.sync();
});
You can try this snippet live in literally five clicks in the new Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/Zlatkovsky/5a2fc743bc9c8556d3eb3234e287d7f3. See more info about importing snippets to Script Lab.