I am new to the Office-JS API, but trying to develop a POC to demonstrate the ability to, with the click of a button, replace all fields in a Word document with corresponding data retrieved from an API.
So I have developed an API as an Azure Function, which I can call, passing a value, and it will return the field names and values as JSON for the record that matches the passed value. I have tested this already using Postman.
Now I am trying to get the Office-JS piece working. I started with the VS-2022 template, which I have converted to TypeScript.
My intended approach is that when the user clicks the button, the application will call the API and obtain the record (data) fields as a set of name/value pairs.
Then, I want loop through all of the (data) fields returned, and for each (data) field name, check to see if there is a (document) field in the document by that name; if so, replace the (document) field with the (data) field's value.
This code is successfully finding the search criteria, and queueing up the replacements, in preparation for the context.sync call... But then during the context.sync call, is reporting an error "ItemNotFound."
I am following the recommended approach documented here https://learn.microsoft.com/en-us/office/dev/add-ins/concepts/correlated-objects-pattern under the "Process objects in the document with the correlated objects pattern" section.
My code follows:
(function () {
"use strict";
var messageBanner;
// The initialize function must be run each time a new page is loaded.
Office.initialize = function (reason) {
(window as any).Promise = OfficeExtension.Promise;
$(document).ready(function () {
// Initialize the notification mechanism and hide it
var element = document.querySelector('.MessageBanner');
messageBanner = new components.MessageBanner(element);
messageBanner.hideBanner();
OfficeExtension.config.extendedErrorLogging = true;
// If not using Word 2016, use fallback logic.
if (!Office.context.requirements.isSetSupported('WordApi', '1.1')) {
$("#template-description").text("This sample displays the selected text.");
$('#button-text').text("Display!");
$('#button-desc').text("Display the selected text");
$('#highlight-button').click(loadCaseData("08-CA-123456"));
//$('#highlight-button').click(simpleTest());
//$('#highlight-button').click(dataCompareTest("08-CA-123456"));
return;
}
$("#template-description").text("This POC demonstrates template capabilities for NLRB within WORD.");
$('#button-text').text("Merge Template!");
$('#button-desc').text("Replaces fields with appropriate data.");
// Add a click event handler for the highlight button.
$('#highlight-button').click(loadCaseData("08-CA-123456"));
//$('#highlight-button').click(simpleTest());
//$('#highlight-button').click(dataCompareTest("08-CA-123456"));
});
};
//CaseNum=08-CA-123456&DataType=1
async function loadCaseData(caseNum) {
Word.run(async (context) => {
const
url = `[My API URL]`;
fetch(url)
.then(resp => resp.json())
.then(resp => {
console.log('Server Response : ' + JSON.stringify(resp));
let fieldList = Object.entries(resp).map(([k, v]) => {
let fieldNameString = '«' + k + '»';
return { fieldName: fieldNameString, fieldValue: v };
});
console.log("Dataset in first part: " + JSON.stringify(fieldList));
return fieldList;
})
.then(async fieldList => await searchDocForFields(fieldList, context))
.catch(errorHandler);
return context.sync();
});
}
async function simpleTest() {
Word.run(async (context) => {
let fieldList = [
{ fieldName: "«StartLogSeqNo»", fieldValue: "AAA5fgABbpgACw==" },
{ fieldName: "«EndLogSeqNo»", fieldValue: null },
{ fieldName: "«SeqVal»", fieldValue: "AAA5fgABbpgAAw==" },
{ fieldName: "«DmlOperation»", fieldValue: 4 },
{ fieldName: "«DmlUpdateFields»", fieldValue: "AAAAAAAAAUAgADg=" },
{ fieldName: "«CaseAssgnedDt»", fieldValue: "2011-07-22T17:12:19" },
{ fieldName: "«AsgnUsrExcldFlg»", fieldValue: "N" },
{ fieldName: "«BuId»", fieldValue: "0-R9NH" },
{ fieldName: "«CaseFiledDt»", fieldValue: "2006-03-09T00:00:00" },
{ fieldName: "«InquiryId»", fieldValue: "08-CA-123456" },
{ fieldName: "«ChgofccmReqFlg»", fieldValue: "N" },
{ fieldName: "«DisputeUnitCity»", fieldValue: "Cleveland" },
{ fieldName: "«CaseClasification»", fieldValue: "Unclassified" },
{ fieldName: "«CaseClosedDt»", fieldValue: null },
{ fieldName: "«Created»", fieldValue: "2010-10-07T15:32:29" },
{ fieldName: "«CreatedBy»", fieldValue: "0-1" },
{ fieldName: "«CrimeSubTypeCd»", fieldValue: null },
{ fieldName: "«TypeCd»", fieldValue: null },
{ fieldName: "«DbLastUpd»", fieldValue: "2019-10-20T17:17:14.31" },
{ fieldName: "«DbLastUpdSrc»", fieldValue: "ScriptingService_PreInvokeMethod" },
{ fieldName: "«CaseDescription»", fieldValue: null },
{ fieldName: "«LastUpd»", fieldValue: "2019-10-20T17:17:14" },
{ fieldName: "«LastUpdBy»", fieldValue: "1-CGA" },
{ fieldName: "«LocalSeqNum»", fieldValue: 1 },
{ fieldName: "«ModificationNum»", fieldValue: 11 },
{ fieldName: "«CaseName»", fieldValue: "This is the case name from the data" },
{ fieldName: "«ParCaseId»", fieldValue: null },
{ fieldName: "«PrAgencyId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrAgentId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrPostnId»", fieldValue: "1-5D2V9F " },
{ fieldName: "«PrPrtnrId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrRepDnrmFlg»", fieldValue: "Y" },
{ fieldName: "«PrRepManlFlg»", fieldValue: "Y" },
{ fieldName: "«PrRepSysFlg»", fieldValue: "Y" },
{ fieldName: "«PrSgroupId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrSubjectId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrSuspctId»", fieldValue: "No Match Row Id" },
{ fieldName: "«IaCategory»", fieldValue: "2" },
{ fieldName: "«RewardExchangeDt»", fieldValue: "2010-10-07T11:09:02" },
{ fieldName: "«RowId»", fieldValue: "1-2DCA-1327" },
{ fieldName: "«CaseNumber»", fieldValue: "08-CA-123456" },
{ fieldName: "«CaseSource»", fieldValue: "Visit" },
{ fieldName: "«DisputeUnitState»", fieldValue: "OH" },
{ fieldName: "«CaseStatus»", fieldValue: "Open" },
{ fieldName: "«CaseSubType»", fieldValue: "CA" },
{ fieldName: "«CaseSubTypeCd»", fieldValue: null },
{ fieldName: "«TerritoryTypeCd»", fieldValue: "08" },
{ fieldName: "«ThreatLvlCd»", fieldValue: "Batch" },
{ fieldName: "«CaseType»", fieldValue: "C" },
{ fieldName: "«BlockedFlag»", fieldValue: null },
{ fieldName: "«CaseLongName»", fieldValue: "This is the case name from the data" },
{ fieldName: "«XCaseNumCi»", fieldValue: null },
{ fieldName: "«DojCaseType»", fieldValue: null },
{ fieldName: "«ElectionTargetDt»", fieldValue: null },
{ fieldName: "«HearingTargetDt»", fieldValue: null },
{ fieldName: "«MethodType»", fieldValue: null },
{ fieldName: "«XNameCi»", fieldValue: null },
{ fieldName: "«Num8a3Discriminatees»", fieldValue: null },
{ fieldName: "«Num8b2Discriminatees»", fieldValue: null },
{ fieldName: "«NumOfEmployees»", fieldValue: 146 },
{ fieldName: "«PostElectionSelfCertification»", fieldValue: null },
{ fieldName: "«Potential10j»", fieldValue: "N" },
{ fieldName: "«XPrPostnBrdId»", fieldValue: "1-4P8HN1" },
{ fieldName: "«XPrPostnSpvId»", fieldValue: "1-1RAQT2" },
{ fieldName: "«ElectionSelfCertification»", fieldValue: null },
{ fieldName: "«XTypeCdCi»", fieldValue: null },
{ fieldName: "«Moved2dh»", fieldValue: 1 },
{ fieldName: "«IdentityVal»", fieldValue: 5792070 },
{ fieldName: "«CdcRecordedFields»", fieldValue: null },
{ fieldName: "«NxgenTestCase»", fieldValue: "N" },
{ fieldName: "«InquiryChargePetition»", fieldValue: null },
{ fieldName: "«ChangeCaptureDatetime»", fieldValue: "2019-10-20T13:17:15.83" },
{ fieldName: "«RegionRecommendsPursuing10j»", fieldValue: "N" },
{ fieldName: "«SurrogateKey»", fieldValue: 1731295 }
]
await searchDocForFields(fieldList, context);
})
}
async function searchDocForFields(fieldList, context) {
console.log("Dataset: " + JSON.stringify(fieldList));
const allSearchResults = [];
console.log('fieldList.Length = ' + fieldList.length);
// console.log("Dataset in second part: " + JSON.stringify(fieldList));
for (let fieldCount = 0; fieldCount < fieldList.length; fieldCount++) {
let options = Word.SearchOptions.newObject(context);
options.matchWildCards = false;
options.matchCase = true;
options.matchWholeWord = true;
// Check the document to see if this field from the database is found
// console.log("Checking for " + fieldList[fieldCount].fieldName + " to be replaced with " + fieldList[fieldCount].fieldValue);
let searchResults = context.document.body.search(fieldList[fieldCount].fieldName, options);
//Load all of the items that meet the search criteria
searchResults.load('items');
let correlatedSearchResult = {
rangesMatchingFieldName: searchResults,
fieldValue: fieldList[fieldCount].fieldValue
}
allSearchResults.push(correlatedSearchResult);
}
await context.sync();
// Now we have all of the search results in the correlatedSearchResult array.
for (let searchResultCount = 0; searchResultCount < allSearchResults.length; searchResultCount++) {
let correlatedObject = allSearchResults[searchResultCount];
console.log("Checking for search result " + searchResultCount + " of " + allSearchResults.length);
for (let rangeCount = 0; rangeCount < correlatedObject.rangesMatchingFieldName.items.length; rangeCount++) {
let targetRange = correlatedObject.rangesMatchingFieldName.items[rangeCount];
let replacementValue = correlatedObject.fieldValue;
targetRange.insertText(replacementValue, Word.InsertLocation.replace);
console.log("Replacing found instance " + rangeCount + " of " + correlatedObject.rangesMatchingFieldName.items.length + " (" + JSON.stringify(targetRange) + ") with (" + replacementValue + ")");
}
}
return await context.sync();
}
function dataCompareTest(caseNum) {
Word.run((context) => {
const
url = `[My API URL]`;
const codedFieldList = [
{ fieldName: "«StartLogSeqNo»", fieldValue: "AAA5fgABbpgACw==" },
{ fieldName: "«EndLogSeqNo»", fieldValue: null },
{ fieldName: "«SeqVal»", fieldValue: "AAA5fgABbpgAAw==" },
{ fieldName: "«DmlOperation»", fieldValue: 4 },
{ fieldName: "«DmlUpdateFields»", fieldValue: "AAAAAAAAAUAgADg=" },
{ fieldName: "«CaseAssgnedDt»", fieldValue: "2011-07-22T17:12:19" },
{ fieldName: "«AsgnUsrExcldFlg»", fieldValue: "N" },
{ fieldName: "«BuId»", fieldValue: "0-R9NH" },
{ fieldName: "«CaseFiledDt»", fieldValue: "2006-03-09T00:00:00" },
{ fieldName: "«InquiryId»", fieldValue: "08-CA-123456" },
{ fieldName: "«ChgofccmReqFlg»", fieldValue: "N" },
{ fieldName: "«DisputeUnitCity»", fieldValue: "Cleveland" },
{ fieldName: "«CaseClasification»", fieldValue: "Unclassified" },
{ fieldName: "«CaseClosedDt»", fieldValue: null },
{ fieldName: "«Created»", fieldValue: "2010-10-07T15:32:29" },
{ fieldName: "«CreatedBy»", fieldValue: "0-1" },
{ fieldName: "«CrimeSubTypeCd»", fieldValue: null },
{ fieldName: "«TypeCd»", fieldValue: null },
{ fieldName: "«DbLastUpd»", fieldValue: "2019-10-20T17:17:14.31" },
{ fieldName: "«DbLastUpdSrc»", fieldValue: "ScriptingService_PreInvokeMethod" },
{ fieldName: "«CaseDescription»", fieldValue: null },
{ fieldName: "«LastUpd»", fieldValue: "2019-10-20T17:17:14" },
{ fieldName: "«LastUpdBy»", fieldValue: "1-CGA" },
{ fieldName: "«LocalSeqNum»", fieldValue: 1 },
{ fieldName: "«ModificationNum»", fieldValue: 11 },
{ fieldName: "«CaseName»", fieldValue: "This is the case name from the data" },
{ fieldName: "«ParCaseId»", fieldValue: null },
{ fieldName: "«PrAgencyId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrAgentId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrPostnId»", fieldValue: "1-5D2V9F " },
{ fieldName: "«PrPrtnrId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrRepDnrmFlg»", fieldValue: "Y" },
{ fieldName: "«PrRepManlFlg»", fieldValue: "Y" },
{ fieldName: "«PrRepSysFlg»", fieldValue: "Y" },
{ fieldName: "«PrSgroupId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrSubjectId»", fieldValue: "No Match Row Id" },
{ fieldName: "«PrSuspctId»", fieldValue: "No Match Row Id" },
{ fieldName: "«IaCategory»", fieldValue: "2" },
{ fieldName: "«RewardExchangeDt»", fieldValue: "2010-10-07T11:09:02" },
{ fieldName: "«RowId»", fieldValue: "1-2DCA-1327" },
{ fieldName: "«CaseNumber»", fieldValue: "08-CA-123456" },
{ fieldName: "«CaseSource»", fieldValue: "Visit" },
{ fieldName: "«DisputeUnitState»", fieldValue: "OH" },
{ fieldName: "«CaseStatus»", fieldValue: "Open" },
{ fieldName: "«CaseSubType»", fieldValue: "CA" },
{ fieldName: "«CaseSubTypeCd»", fieldValue: null },
{ fieldName: "«TerritoryTypeCd»", fieldValue: "08" },
{ fieldName: "«ThreatLvlCd»", fieldValue: "Batch" },
{ fieldName: "«CaseType»", fieldValue: "C" },
{ fieldName: "«BlockedFlag»", fieldValue: null },
{ fieldName: "«CaseLongName»", fieldValue: "This is the case name from the data" },
{ fieldName: "«XCaseNumCi»", fieldValue: null },
{ fieldName: "«DojCaseType»", fieldValue: null },
{ fieldName: "«ElectionTargetDt»", fieldValue: null },
{ fieldName: "«HearingTargetDt»", fieldValue: null },
{ fieldName: "«MethodType»", fieldValue: null },
{ fieldName: "«XNameCi»", fieldValue: null },
{ fieldName: "«Num8a3Discriminatees»", fieldValue: null },
{ fieldName: "«Num8b2Discriminatees»", fieldValue: null },
{ fieldName: "«NumOfEmployees»", fieldValue: 146 },
{ fieldName: "«PostElectionSelfCertification»", fieldValue: null },
{ fieldName: "«Potential10j»", fieldValue: "N" },
{ fieldName: "«XPrPostnBrdId»", fieldValue: "1-4P8HN1" },
{ fieldName: "«XPrPostnSpvId»", fieldValue: "1-1RAQT2" },
{ fieldName: "«ElectionSelfCertification»", fieldValue: null },
{ fieldName: "«XTypeCdCi»", fieldValue: null },
{ fieldName: "«Moved2dh»", fieldValue: 1 },
{ fieldName: "«IdentityVal»", fieldValue: 5792070 },
{ fieldName: "«CdcRecordedFields»", fieldValue: null },
{ fieldName: "«NxgenTestCase»", fieldValue: "N" },
{ fieldName: "«InquiryChargePetition»", fieldValue: null },
{ fieldName: "«ChangeCaptureDatetime»", fieldValue: "2019-10-20T13:17:15.83" },
{ fieldName: "«RegionRecommendsPursuing10j»", fieldValue: "N" },
{ fieldName: "«SurrogateKey»", fieldValue: 1731295 }
]
fetch(url)
.then(resp => resp.json())
.then(resp => {
console.log('Server Response : ' + JSON.stringify(resp));
let fetchFieldList = Object.entries(resp).map(([k, v]) => {
let fieldNameString = '«' + k + '»';
return { fieldName: fieldNameString, fieldValue: v };
});
console.log("Dataset in first part: " + JSON.stringify(fetchFieldList));
return fetchFieldList;
})
.then(fieldList => {
console.log('Comparing datasets');
const jsonFieldList = JSON.stringify(fieldList);
const jsonCodedFieldList = JSON.stringify(codedFieldList);
const same = jsonFieldList === jsonCodedFieldList;
console.log("Are the arrays the same? " + same);
})
.catch(errorHandler);
return context.sync();
});
}
//$$(Helper function for treating errors, $loc_script_taskpane_home_js_comment34$)$$
function errorHandler(error) {
// $$(Always be sure to catch any accumulated errors that bubble up from the Word.run execution., $loc_script_taskpane_home_js_comment35$)$$
showNotification("Error:", error);
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
}
// Helper function for displaying notifications
function showNotification(header, content) {
$("#notification-header").text(header);
$("#notification-body").text(content);
messageBanner.showBanner();
messageBanner.toggleExpansion();
}
})();
And the sample JSON returned from my data API:
{
"CaseAssgnedDt": "2011-07-22T17:12:19",
"AsgnUsrExcldFlg": "N",
"BuId": "0-R9NH",
"CaseFiledDt": "2006-03-09T00:00:00",
"InquiryId": "08-CA-123456",
"ChgofccmReqFlg": "N",
"DisputeUnitCity": "Cleveland",
"CaseClasification": "Unclassified",
"CaseClosedDt": null,
"Created": "2010-10-07T15:32:29",
"CreatedBy": "0-1",
"CrimeSubTypeCd": null,
"TypeCd": null,
"DbLastUpd": "2019-10-20T17:17:14.31",
"DbLastUpdSrc": "ScriptingService_PreInvokeMethod",
"CaseDescription": null,
"LastUpd": "2019-10-20T17:17:14",
"LastUpdBy": "1-CGA",
"LocalSeqNum": 1,
"ModificationNum": 11,
"CaseName": "This is the case name from the data",
"ParCaseId": null,
"PrAgencyId": "No Match Row Id",
"PrAgentId": "No Match Row Id",
"PrPostnId": "1-5D2V9F ",
"PrPrtnrId": "No Match Row Id",
"PrRepDnrmFlg": "Y",
"PrRepManlFlg": "Y",
"PrRepSysFlg": "Y",
"PrSgroupId": "No Match Row Id",
"PrSubjectId": "No Match Row Id",
"PrSuspctId": "No Match Row Id",
"IaCategory": "2",
"RewardExchangeDt": "2010-10-07T11:09:02",
"RowId": "1-2DCA-1327",
"CaseNumber": "08-CA-123456",
"CaseSource": "Visit",
"DisputeUnitState": "OH",
"CaseStatus": "Open",
"CaseSubType": "CA",
"CaseSubTypeCd": null,
"TerritoryTypeCd": "08",
"ThreatLvlCd": "Batch",
"CaseType": "C",
"BlockedFlag": null,
"CaseLongName": "This is the case name from the data",
"XCaseNumCi": null,
"DojCaseType": null,
"ElectionTargetDt": null,
"HearingTargetDt": null,
"MethodType": null,
"XNameCi": null,
"Num8a3Discriminatees": null,
"Num8b2Discriminatees": null,
"NumOfEmployees": 146,
"PostElectionSelfCertification": null,
"Potential10j": "N",
"XPrPostnBrdId": "1-4P8HN1",
"XPrPostnSpvId": "1-1RAQT2",
"ElectionSelfCertification": null,
"XTypeCdCi": null,
"Moved2dh": 1,
"IdentityVal": 5792070,
"CdcRecordedFields": null,
"NxgenTestCase": "N",
"InquiryChargePetition": null,
"ChangeCaptureDatetime": "2019-10-20T13:17:15.83",
"RegionRecommendsPursuing10j": "N",
"SurrogateKey": 1731295
}
I appreciate any and all help to get this working, please.
Thank you.
-- UPDATE: Per Rick Kirkham's recommendation, I created a new function ("simpleTest", included now in the code above) to simplify and validate the approach, and was surprised to see that it worked (Though I'll bet Rick isn't surprised, LOL).
I also added a function "dataCompare", also in the code above), which loads one dataset manually, and the other using the Fetch call, and then compares the two, and it responds that the two arrays are the same.
So I am at a loss given that the data is the same, and both the loadCaseData and the simpleTest functions use the same searchDocForFields function to do the search-and-replace, why would the simpleTest function work, and the loadCaseData function fail with the ItemNotFound error?
Thanks. Mark.
fieldListvariable. In other words, remove all the external data fetching. See if that works or reproduces the same errors.