Mark Jeweler
Reputation: 89
Office-JS API with Fetch to get data from server, replace fields in Word - ItemNotFound error
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.
Upvotes: 1
Views: 1104
Answers (1)
Mark Jeweler
Reputation: 89
This all, ultimately, turned out to be caused by lack of a single await... Note the line below with the FETCH statement, which previously did not have an AWAIT before it.
async function loadCaseData(caseNum) {
Word.run(async (context) => {
const ul = document.getElementById('caseData'),
url = `[URL to my API]`;
const createNode = element => { return document.createElement(element); };
const append = (parent, el) => { return parent.appendChild(el); };
/*----->*/ await 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 newLoadCaseData(caseNum) {
Word.run(async (context) => {
const
url = `[URL to my API]`;
let resp = await (await fetch(url)).json();
//let data = resp.json();
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));
await searchDocForFields(fieldList, context);
});
}
I discovered this by trying to further simplify my code, and thus created the "newLoadCaseData" function also shown. When writing the FETCH statement in this version, VS2022 inserted an await for me, and opened my eyes to the problem.
This is now working, and I hope that my research and mistakes/fixes turn out to help someone else as well.
Enjoy!
Mark.
Upvotes: 1
Related Questions
- Unable to read Content Controls in existing Word document using office.js APIs
- word add-in using OfficeJs
- Replace text in MS Word Add-in JS API
- Word add-in Error Code 13004
- MS-Office word add-in SSO
- Word add-in won't install
- MS Word API: table values not set
- Office JS API - settings.getItem is not a function
- Office 365 API crashing Add-in on Word for Mac
- Office 365 API Search replace on Word online (fault?)