Skip to content

Instantly share code, notes, and snippets.

@chgrossMSFT

chgrossMSFT/Create and retrieve rich data tool (Update).EXCEL.yaml

Last active April 26, 2022 21:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chgrossMSFT/b6a16817a4cfc138a5c597aefaba3e0a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chgrossMSFT/b6a16817a4cfc138a5c597aefaba3e0a to your computer and use it in GitHub Desktop.
Download ZIP
Create and retrieve rich data tool - building, retrieving and modifying rich data using valuesAsJson. This tool was created as a one-off example demonstrating the capabilities of the valuesAsJson API and it is not supported or maintained by Microsoft
Raw
Create and retrieve rich data tool (Update).EXCEL.yaml
name: Create and retrieve rich data tool (Update)
description: >-
Create and retrieve rich data tool - building, retrieving and modifying rich
data using valuesAsJson. This tool was created as a one-off example
demonstrating the capabilities of the valuesAsJson API and it is not supported
or maintained by Microsoft
host: EXCEL
api_set: {}
script:
content: >
/** Copyright (c) Microsoft Corporation. Licensed under the MIT License. */
const specificFieldContents = `
<div id="specificField" class="specificFieldContents formContents">
<table><tbody><tr><td>
<select id="dataTypeSelectEntity" name="dataType" class="dataTypeSelectEntity ms-Button ms-Button-label buttons" onchange="selectEntityType(this)" style="display:block">
<option class="typeSelect" value = "select" disabled selected> Select Data Type </option>
<option class="typeString" value="string">String</option>
<option class="typeDouble" value="double">Double</option>
<option class="typeBoolean" value="boolean">Boolean</option>
<option class="typeImage" value="image">Web Image</option>
<option class="typeFNV" value="FNV">Formatted Number</option>
<option class="typeUnsupported" value="unsupported" disabled>Unsupported</option>
</select>
</td></tr></tbody></table>
<div id="entityContents"></div>
</div>`;
const imageTypeContent = `<div id="image" class="fieldValueContents">
<tr class="columnTitle">
<td><label for="url" class="labels">Image url: </label></td>
<td><input class="inputBox url" type="text" name="url" id="url" alt="Image url input box"></td>
</tr>
<tr class="columnTitle">
<td><label for="altText" class="labels">[Recommended] Alt-text: </label></td>
<td><input class="inputBox altText" type="text" name="altText" id="altText" alt="Alt-text input box"></td>
</tr>
</div>`;
const basicTypeContent = `<tr class="columnTitle">
<td><label class="labels">Value:</label></td>
<td><input id="basicValue" class="inputBox basicValue" alt="value input box"/></td>
</tr>`;
const FNVTypeContent = `<div id="FNV" class="fieldValueContents">
<tr class="columnTitle">
<td><label for="number" class="labels">number: </label></td>
<td><input class="inputBox number" type="text" name="number" id="number" alt="Number input box"></td>
</tr>
<tr class="columnTitle">
<td><label for="format" class="labels">Format: </label></td>
<td><input class="inputBox format" type="text" name="format" id="format" alt="Format input box"></td>
</tr>
</div>`;
const unsupportedTypeContent = `<div id="unsupported"
class="fieldValueContents">
<tr class="columnTitle">
<td><label for="unsupportedValue" class="labels">unsupportedValue: </label></td>
<td><input class="inputBox unsupportedValue" disabled type="text" name="unsupportedValue" id="unsupportedValue" alt="unsupportedValue input box"></td>
</tr>
</div>`;
const stringTypeContent = `<div id="string" class="fieldValueContents">` +
basicTypeContent + `</div>`;
const doubleTypeContent = `<div id="double" class="fieldValueContents">` +
basicTypeContent + `</div>`;
const booleanTypeContent = `<div id="boolean" class="fieldValueContents">` +
basicTypeContent + `</div>`;
const sectionContents =
`<div class="sectionContents formContents">
<table id="sectionTable">
<tbody>
<tr class="columnTitle">
<td>
<button class="ms-Button ms-Button-label arrows" onclick="moveSectionUp(this)" alt="button to move section up" title="move section up">&#9650</button>
<button class="ms-Button ms-Button-label arrows" onclick="moveSectionDown(this)" alt="button to move section down" title="move section down">&#9660</button>
</td>
<td><label class="sectionHeader">Section Title:</label></td>
<td><input class="inputBox sectionTitle" alt="section title input box"/></td>
<td>
<button class="ms-Button ms-Button-label arrows sectionToggle" onclick="collapseSection(this)" alt="collapse section" title="collapse section" style="visibility:visible">&#x2228</button>
</td>
</tr>
</tbody>
</table>
<div class="collapsibleSection" aria-expanded="true">
<div class="fields">` +
specificFieldContents +
`</div>
<table>
<tbody>
<tr>
<td><button id="addField" onclick="addField(this)" class="ms-Button ms-Button-label buttons" alt="add another field to current section" style="margin-left:20px;">Add another field</button></td>
<td><button class="ms-Button ms-Button-label buttons" onclick="removeSection(this)" alt="delete current section and its contents">Delete Section and its Contents</button></td>
</tr>
</tbody>
</table>
</div>
</div>`;
/*Entity caching features
*
* Generic caching mechanism for properties which we just want to roundtrip
* e.g. Provider info and ReferencedValues
*/
/*cache*/
var cachedReferences = {};
/*keys to cache by*/
var cachedRefKeys = [
{ type: "root", keys: ["referencedValues", "provider"] },
{ type: "layouts", keys: ["compact"] }
];
/*add values in cachedRefKeys to the cache*/
function storeCachedRefs(val) {
cachedRefKeys.forEach(function(cache) {
var keysToCache = cache.keys;
keysToCache.forEach(function(key) {
var valueToCache = undefined;
if (cache.type == "root") {
valueToCache = val[key];
} else if (val[cache.type] != undefined) {
valueToCache = val[cache.type][key];
}
if (valueToCache != undefined) {
if (cachedReferences[cache.type] == undefined) cachedReferences[cache.type] = {};
cachedReferences[cache.type][key] = valueToCache;
}
});
});
}
/*write cached values in cachedReferences to the value*/
function addCachedRefs(val) {
Object.keys(cachedReferences).forEach(function(cacheKey) {
var cache = cachedReferences[cacheKey];
var keysToCache = Object.keys(cache);
keysToCache.forEach(function(key) {
var valueToRetrieve = cache[key];
var cacheType = cacheKey;
if (cacheType == "root") {
val[key] = valueToRetrieve;
} else if (val[cacheType] != undefined) {
val[cacheType][key] = valueToRetrieve;
}
});
});
}
/** When data type is selected */ $(document).ready(function() {
$("#dataTypeSelect").change(function() {
var values = $("#dataTypeSelect option:selected");
tryCatch(dataTypeSelect);
switch (values.val()) {
case "string":
typeBasic(stringTypeContent);
break;
case "double":
typeBasic(doubleTypeContent);
break;
case "boolean":
typeBasic(booleanTypeContent);
break;
case "entity":
tryCatch(typeEntity);
break;
case "FNV":
tryCatch(typeFNV);
break;
case "image":
tryCatch(typeImage);
break;
}
});
});
/** Html for entity contents section */ function entityContents(
specificTypeContents: string,
jqEntityContentsElement: JQuery<HTMLElement>
) {
jqEntityContentsElement.replaceWith(
`<div id="entityContents"
<div>
<table id="fieldTable">
<tbody>
<tr class="columnTitle">
<td rowspan="3">
<button class="ms-Button ms-Button-label arrows" onclick="moveFieldUp(this)" alt="button to move field up" title="move field up">&#9650</button>
<button class="ms-Button ms-Button-label arrows" onclick="moveFieldDown(this)" alt="button to move field down" title="move field down">&#9660</button>
</td>
<td><label class="labels">Key:</label></td>
<td><input class="inputBox fieldName" alt="key input box"/></td>
</tr>
<div>` +
specificTypeContents +
`</div>
<tr class="metadata">
<td colspan="4" class="center settings">
<div class="checkboxes">
<input type="checkbox" class="cardView" checked="true" alt="cardview checkbox"/>
<label>Card View</label>
</div>
<div class="checkboxes">
<input type="checkbox" class="autoComplete" checked="true" alt="autocomplete checkbox"/>
<label>Autocomplete</label>
</div>
<br>
<div class="checkboxes">
<input type="checkbox" class="calcCompare" checked="true" alt="calc compare checkbox"/>
<label>Calc Compare</label>
</div>
<div class="checkboxes">
<input type="checkbox" class="dotNotation" checked="true" alt="dot notation checkbox"/>
<label>Dot Notation</label>
</div>
<br><label>Sublabel: </label><input class="sublabel" alt="sublabel input box"/>
</td>
</tr>
<tr>
<td colspan="4">
<button class="ms-Button ms-Button-label buttons" onclick="removeField(this)" alt="delete current field">Delete Field</button>
<button class="ms-Button ms-Button-label buttons" onclick="toggleMetadata(this)" alt="toggle to expand or collapse metadata properties of field">More Settings</button>
</td>
</tr>
</tbody>
</table>
</div>
</div>`
);
}
/** Html for type entity */ function typeEntity() {
$(".backgroundColorForm").replaceWith(
`<div class="backgroundColorForm">
<div class="contentPadding">
<label class="labels"> Entity Display Text: </label>
<input class="inputBox" id="displayString" alt="display text input box"/>
</div>
<label class="labels contentPadding">Entity Contents:</label>
<div class="sections">` +
sectionContents +
`</div>
<button id="addSection" onclick="addSection()" class="ms-Button ms-Button-label buttons" style="margin-left:20px;" alt="add another section to entity contents">Add another section</button>
</div>`
);
}
/** Html for after data type contents */ function dataTypeSelect() {
$("#dataTypeContents").replaceWith(
`<div id="dataTypeContents">
<div class="backgroundColorForm">
</div>
<button id="setData" onclick="setData()" class="ms-Button ms-Button-label buttons" alt="set entered data into active cell">Set</button>
<button id="clearForm" onclick="clearForm()" class="ms-Button ms-Button-label buttons" alt="clear current contents of input boxes">Clear</button>
</div>`
);
}
/** Html for when type image is selected */ function typeImage() {
$(".backgroundColorForm").replaceWith(
`<div class="backgroundColorForm">
<table id="fieldTable">
<tbody>` +
imageTypeContent +
`</tbody>
</table>
</div>`
);
}
/** Html for when type FNV is selected */ function typeFNV() {
$(".backgroundColorForm").replaceWith(
`<div class="backgroundColorForm">
<table id="fieldTable">
<tbody>` +
FNVTypeContent +
`</tbody>
</table>
</div>`
);
}
/** Html for when boolean, string or double are selected */ function
typeBasic(typeContent) {
$(".backgroundColorForm").replaceWith(
`<div class="backgroundColorForm">
<table id="fieldTable">
<tbody>` +
typeContent +
`</tbody>
</table>
</div>`
);
}
/** Add another section in entity contents */ function addSection() {
$(".sections").append(sectionContents);
}
/** Add a new field to the entity contents */ function addField(element:
HTMLButtonElement) {
//append specificFieldContents to fields div
const fieldsDiv = element.parentElement.parentElement.parentElement.parentElement.previousElementSibling;
$(fieldsDiv).append(specificFieldContents);
}
/** Move a field down in the entity contents order */ function
moveFieldDown(element: HTMLButtonElement) {
const curSpecificField = element.parentElement.parentElement.parentElement.parentElement.parentElement
.parentElement as HTMLDivElement;
const nextSpecificField = curSpecificField.nextElementSibling as HTMLDivElement;
if (nextSpecificField) {
$(curSpecificField).remove();
$(nextSpecificField).after(curSpecificField);
} else {
const curSectionContents = curSpecificField.parentElement.parentElement.parentElement as HTMLDivElement;
const nextSectionContents = curSectionContents.nextElementSibling as HTMLDivElement;
if (nextSectionContents) {
const nextSectionFields = $(nextSectionContents).find(".fields");
$(curSpecificField).remove();
nextSectionFields.prepend(curSpecificField);
}
}
}
/** Move a section down in the entity contents order */ function
moveSectionDown(element: HTMLButtonElement) {
const curSpecificSection = element.parentElement.parentElement.parentElement.parentElement
.parentElement as HTMLDivElement;
const nextSpecificSection = curSpecificSection.nextElementSibling as HTMLDivElement;
if (nextSpecificSection) {
$(curSpecificSection).remove();
$(nextSpecificSection).after(curSpecificSection);
}
}
/** Move a field up in the entity contents order */ function
moveFieldUp(element: HTMLButtonElement) {
const curSpecificField = element.parentElement.parentElement.parentElement.parentElement.parentElement
.parentElement as HTMLDivElement;
const prevSpecificField = curSpecificField.previousElementSibling as HTMLDivElement;
if (prevSpecificField) {
$(curSpecificField).remove();
$(prevSpecificField).before(curSpecificField);
} else {
const curSectionContents = curSpecificField.parentElement.parentElement.parentElement as HTMLDivElement;
const prevSectionContents = curSectionContents.previousElementSibling as HTMLDivElement;
if (prevSectionContents) {
const prevSectionFields = $(prevSectionContents).find(".fields");
$(curSpecificField).remove();
prevSectionFields.append(curSpecificField);
}
}
}
/** Move a section up in the entity contents order */ function
moveSectionUp(element: HTMLButtonElement) {
const curSpecificSection = element.parentElement.parentElement.parentElement.parentElement
.parentElement as HTMLDivElement;
const prevSpecificSection = curSpecificSection.previousElementSibling as HTMLDivElement;
if (prevSpecificSection) {
$(curSpecificSection).remove();
$(prevSpecificSection).before(curSpecificSection);
}
}
/** Function that processes when the data type of a property within an
entity is changed and calls the appropriate function */ function
selectEntityType(
selectionElement: HTMLSelectElement
) {
const entityContentsElement =
selectionElement.parentElement.parentElement.parentElement.parentElement.nextElementSibling;
const values = selectionElement.value;
switch (values) {
case "string":
entityContents(stringTypeContent, $(entityContentsElement));
break;
case "double":
entityContents(doubleTypeContent, $(entityContentsElement));
break;
case "boolean":
entityContents(booleanTypeContent, $(entityContentsElement));
break;
case "FNV":
entityContents(FNVTypeContent, $(entityContentsElement));
break;
case "image":
entityContents(imageTypeContent, $(entityContentsElement));
const firstMetadataElement =
selectionElement.parentElement.parentElement.parentElement.parentElement.nextElementSibling.firstElementChild
.nextElementSibling.firstElementChild.firstElementChild.nextElementSibling.nextElementSibling
.nextElementSibling.firstElementChild.firstElementChild;
$(firstMetadataElement).before(`<div class="checkboxes">
<input type="checkbox" class="mainImage" alt="main image checkbox"/>
<label>Make Main Image</label>
</div><br>`);
break;
}
}
/** Function for expanding or collapsing additional metadata contents of a
particular input field in the entity contents */ function toggleMetadata(
element: HTMLButtonElement
) {
var visibility = element.parentElement.parentElement.previousElementSibling.style.visibility;
if (visibility != "collapse") {
visibility = "collapse";
} else {
visibility = "visible";
}
element.parentElement.parentElement.previousElementSibling.style.visibility = visibility;
}
/** Function for expanding section contents in the entity contents */
function expandSection(
element: HTMLButtonElement
) {
element.parentElement.parentElement.parentElement.parentElement.nextElementSibling.style.visibility = "visible";
$(element).replaceWith(
`<button class="ms-Button ms-Button-label arrows sectionToggle" onclick="collapseSection(this)" alt="collapse section" title="collapse section">&#x2228</button>`
);
}
/** Function for collapsing section contents in the entity contents */
function collapseSection(
element: HTMLButtonElement
) {
element.parentElement.parentElement.parentElement.parentElement.nextElementSibling.style.visibility = "collapse";
$(element).replaceWith(
`<button class="ms-Button ms-Button-label arrows sectionToggle" onclick="expandSection(this)" alt="expand section" title="expand section" >&#x2227 </button>`
);
}
/** Function for assigning the inputted data to the active cell as the
appropriate data type */ async function setData() {
await Excel.run(async (context) => {
const activeCell = context.workbook.getActiveCell();
var values = $("#dataTypeSelect option:selected");
switch (values.val()) {
case "string":
var stringValue = $(".basicValue")
.val()
.toString();
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.string,
basicValue: stringValue
}
]
];
break;
case "double":
var doubleValue = $(".basicValue").val();
if (!isNaN(Number(doubleValue))) {
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.double,
basicValue: doubleValue
}
]
];
} else {
alert("type 'Double' selected but input was not a double");
}
break;
case "boolean":
var booleanValue = $(".basicValue")
.val()
.toString();
if (booleanValue.toLowerCase() === "true") {
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.boolean,
basicValue: true
}
]
];
} else if (booleanValue.toLowerCase() === "false") {
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.boolean,
basicValue: false
}
]
];
} else {
alert("type 'Boolean' selected but input was not a boolean");
}
break;
case "entity":
activeCell.valuesAsJson = [[setEntity()]];
break;
case "image":
var url = $(".url")
.val()
.toString();
var altText = $(".altText")
.val()
.toString();
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.webImage,
address: url,
altText: altText
}
]
];
break;
case "FNV":
var doubleValue = $("#number").val();
var format = $("#format").val();
if (!isNaN(Number(doubleValue))) {
activeCell.valuesAsJson = [
[
{
type: Excel.CellValueType.formattedNumber,
basicValue: parseFloat(doubleValue),
numberFormat: format
}
]
];
} else {
alert("type 'Double' selected but input was not a double");
}
break;
}
console.log(activeCell.valuesAsJson)
await context.sync();
});
}
/** Function for assigning the inputted entity contents to an entity */
function setEntity() {
const display: string = $("#displayString")
.val()
.toString();
const fields = valuesFromQuery(".fieldName");
const values = fieldValuesContentsFromQuery();
const cardViews = valuesFromQuery(".cardView");
const autoCompletes = valuesFromQuery(".autoComplete");
const calcCompares = valuesFromQuery(".calcCompare");
const dotNotation = valuesFromQuery(".dotNotation");
const sublabels = valuesFromQuery(".sublabel");
var mainImage = valuesFromQuery(".mainImage");
var mainImageExists = false;
var mainImageKey;
var sectionArray = [];
var jqSectionContents = $(".sectionContents");
while (jqSectionContents.length > 0) {
const first = jqSectionContents.first();
var children = first.find(".fieldName");
var sectionTitle = first.find(".sectionTitle");
var properties = [];
for (var i = 0; i < children.length; ++i) {
var val = $(children[i]).val();
properties.push(val);
}
var sectionEntry = {
layout: "List",
title: sectionTitle.val(),
properties: properties
};
sectionArray.push(sectionEntry);
jqSectionContents = jqSectionContents.slice(1);
}
var entity: Excel.EntityCellValue = {
type: Excel.CellValueType.entity,
text: display,
properties: {},
layouts: { card: {} }
};
for (var i = 0; i < fields.length; ++i) {
var curSectionFields = [];
const field = fields[i];
var value = values[i];
if (field == "" || value == "") {
break;
}
var featureIntegration = {};
if (!cardViews[i]) {
featureIntegration["cardView"] = true;
}
if (!autoCompletes[i]) {
featureIntegration["autoComplete"] = true;
}
if (!calcCompares[i]) {
featureIntegration["calcCompare"] = true;
}
if (!dotNotation[i]) {
featureIntegration["dotNotation"] = true;
}
var propertyMetadata = {};
if (Object.keys(featureIntegration).length > 0) {
propertyMetadata["excludeFrom"] = featureIntegration;
}
if (sublabels[i] != "") {
propertyMetadata["sublabel"] = sublabels[i];
}
if (value.type == "WebImage") {
if (mainImage[0] && !mainImageExists) {
mainImageKey = field;
mainImageExists = true;
}
mainImage = mainImage.slice(1);
}
if (Object.keys(propertyMetadata).length > 0) {
value["propertyMetadata"] = propertyMetadata;
}
entity.properties[field] = value;
}
if (mainImageExists) {
entity.layouts.card = {
mainImage: {
property: mainImageKey
},
sections: sectionArray
};
} else {
entity.layouts.card = {
sections: sectionArray
};
}
addCachedRefs(entity);
return entity;
}
/** Helper Function for retrieving jquery values for setEntity() */ function
valuesFromQuery(query: string) {
var jq = $(query);
var result = [];
while (jq.length > 0) {
const first = jq.first();
if (first.is("input[type=checkbox]")) {
result.push(first.prop("checked"));
} else {
result.push(first.val());
}
jq = jq.slice(1);
}
return result;
}
/** Helper Function for retrieving fieldValue contents for jquery for
setEntity() */ function fieldValuesContentsFromQuery() {
var jqFieldContents = $(".fieldValueContents");
var jqBasicValue = $(".basicValue");
var jqUrl = $(".url");
var jqNumber = $(".number");
var jqFormat = $(".format");
var jqAltText = $(".altText");
var jqUnsupportedValue = $(".unsupportedValue");
var values = [];
while (jqFieldContents.length > 0) {
const first = jqFieldContents.first();
var valueType = first.attr("id");
var value;
switch (valueType) {
case "string":
value = {
type: Excel.CellValueType.string,
basicValue: jqBasicValue
.first()
.val()
.toString()
} as Excel.StringCellValue;
jqBasicValue = jqBasicValue.slice(1);
break;
case "FNV":
value = {
type: Excel.CellValueType.formattedNumber,
basicValue: Number(jqNumber.first().val()),
numberFormat: jqFormat
.first()
.val()
.toString()
};
jqNumber = jqNumber.slice(1);
jqFormat = jqFormat.slice(1);
break;
case "double":
var doubleValue = jqBasicValue.first().val();
if (!isNaN(Number(doubleValue))) {
value = {
type: Excel.CellValueType.double,
basicValue: Number(doubleValue)
} as Excel.DoubleCellValue;
} else {
alert("type 'Double' selected but input was not a double");
}
jqBasicValue = jqBasicValue.slice(1);
break;
case "boolean":
var booleanValue = jqBasicValue
.first()
.val()
.toString();
if (booleanValue.toLowerCase() === "true") {
value = {
type: Excel.CellValueType.boolean,
basicValue: true
} as Excel.BooleanCellValue;
} else if (booleanValue.toLowerCase() === "false") {
value = {
type: Excel.CellValueType.boolean,
basicValue: false
} as Excel.BooleanCellValue;
} else {
alert("type 'Boolean' selected but input was not a boolean");
}
jqBasicValue = jqBasicValue.slice(1);
break;
case "image":
value = {
type: Excel.CellValueType.webImage,
address: jqUrl.first().val(),
altText: jqAltText.first().val()
} as Excel.WebImageCellValue;
jqUrl = jqUrl.slice(1);
jqAltText = jqAltText.slice(1);
break;
case "unsupported":
value = JSON.parse(
jqUnsupportedValue
.first()
.val()
.toString()
);
jqUnsupportedValue = jqUnsupportedValue.slice(1);
break;
}
values.push(value);
jqFieldContents = jqFieldContents.slice(1);
}
return values;
}
/** Function for removing a selected input field in the entity contents */
function removeField(
element: HTMLButtonElement
) {
// removes specificField
element.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.remove();
}
/** Function for removing a selected section in the entity contents */
function removeSection(
element: HTMLButtonElement
) {
// removes specific sectionContents
element.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.remove();
}
/** Function for retrieving the contents of a selected entity and putting
them in the form boxes */ function getEntity(
value
) {
// If there is no section, make one
var sections;
storeCachedRefs(value);
sections = value.layouts.card.sections;
if (sections == undefined) {
const propertyKeys = Object.keys(value.properties);
sections = [
{
layout: "List",
properties: propertyKeys
}
];
}
var propertyKeysOrdered = [];
$(".sectionContents").remove();
for (var i = 0; i < sections.length; ++i) {
addSection();
$(".sectionTitle")
.last()
.val(sections[i].title);
var sectionKeys = sections[i].properties;
$(".specificFieldContents")
.last()
.remove();
for (var j = 0; j < sectionKeys.length; ++j) {
$(".fields")
.last()
.append(specificFieldContents);
const propertyName = sectionKeys[j];
propertyKeysOrdered.push(propertyName);
const propertyValue = value.properties[propertyName];
const entityContentsDiv = $(".dataTypeSelectEntity")
.last()
.parent()
.parent()
.parent()
.parent()
.next();
switch (propertyValue.type) {
case "Double":
$(".dataTypeSelectEntity")
.last()
.val("double");
entityContents(doubleTypeContent, entityContentsDiv);
break;
case "Boolean":
$(".dataTypeSelectEntity")
.last()
.val("boolean");
entityContents(booleanTypeContent, entityContentsDiv);
break;
case "FormattedNumber":
$(".dataTypeSelectEntity")
.last()
.val("FNV");
entityContents(FNVTypeContent, entityContentsDiv);
break;
case "WebImage":
$(".dataTypeSelectEntity")
.last()
.val("image");
entityContents(imageTypeContent, entityContentsDiv);
const firstMetadataElement = $(".cardView")
.last()
.parent();
firstMetadataElement.before(`<div class="checkboxes">
<input type="checkbox" class="mainImage" alt="main image checkbox"/>
<label>Make Main Image</label>
</div><br>`);
break;
case "String":
$(".dataTypeSelectEntity")
.last()
.val("string");
entityContents(stringTypeContent, entityContentsDiv);
break;
default:
/*unsupported*/
$(".dataTypeSelectEntity")
.last()
.val("unsupported");
entityContents(unsupportedTypeContent, entityContentsDiv);
break;
}
}
}
// write the entity's data into the table
$("#displayString").val(value.text);
var jqFields = $(".fieldName");
var jqCardView = $(".cardView");
var jqAutoComplete = $(".autoComplete");
var jqCalcCompare = $(".calcCompare");
var jqDotNotation = $(".dotNotation");
var jqSublabel = $(".sublabel");
var jqBasicValue = $(".basicValue");
var jqUrl = $(".url");
var jqNumber = $(".number");
var jqFormat = $(".format");
var jqAltText = $(".altText");
var jqMainImage = $(".mainImage");
var jqUnsupportedValue = $(".unsupportedValue");
for (var i = 0; i < propertyKeysOrdered.length; ++i) {
const propertyName = propertyKeysOrdered[i];
const propertyValue = value.properties[propertyName];
jqFields.first().val(propertyName);
switch (propertyValue.type) {
case "Double":
case "Boolean":
case "String":
jqBasicValue.first().val(propertyValue.basicValue);
jqBasicValue = jqBasicValue.slice(1);
break;
case "WebImage":
jqUrl.first().val(propertyValue.address);
jqAltText.first().val(propertyValue.altText);
jqUrl = jqUrl.slice(1);
jqAltText = jqAltText.slice(1);
if (value.layouts.card["mainImage"] != undefined) {
if (value.layouts.card.mainImage.property == propertyName) {
jqMainImage.first().prop("checked", true);
}
}
break;
case "FormattedNumber":
jqNumber.first().val(propertyValue.basicValue);
jqFormat.first().val(propertyValue.numberFormat);
jqNumber = jqNumber.slice(1);
jqFormat = jqFormat.slice(1);
break;
default:
jqUnsupportedValue.first().val(JSON.stringify(propertyValue));
jqUnsupportedValue = jqUnsupportedValue.slice(1);
break;
}
var featureIntegration: Excel.CellValuePropertyMetadataExclusions = {
cardView: false,
autoComplete: false,
calcCompare: false,
dotNotation: false
};
var sublabel = "";
if (typeof propertyValue.propertyMetadata == "object") {
if (typeof propertyValue.propertyMetadata.excludeFrom == "object") {
featureIntegration = Object.assign(featureIntegration, propertyValue.propertyMetadata.excludeFrom);
}
if (typeof propertyValue.propertyMetadata.sublabel == "string") {
sublabel = propertyValue.propertyMetadata.sublabel;
}
}
jqCardView.first().prop("checked", !featureIntegration.cardView);
jqAutoComplete.first().prop("checked", !featureIntegration.autoComplete);
jqCalcCompare.first().prop("checked", !featureIntegration.calcCompare);
jqDotNotation.first().prop("checked", !featureIntegration.dotNotation);
jqSublabel.first().val(sublabel);
jqFields = jqFields.slice(1);
jqCardView = jqCardView.slice(1);
jqAutoComplete = jqAutoComplete.slice(1);
jqCalcCompare = jqCalcCompare.slice(1);
jqDotNotation = jqDotNotation.slice(1);
jqSublabel = jqSublabel.slice(1);
}
}
/** Function for retrieving the contents of a selected cell and putting them
in the form boxes */ async function getData() {
await Excel.run(async (context) => {
const activeCell = context.workbook.getActiveCell();
activeCell.load("valuesAsJson");
await context.sync();
const value = activeCell.valuesAsJson[0][0];
clearForm();
switch (value.type) {
case "String":
$("#dataTypeSelect").val("string");
tryCatch(dataTypeSelect);
typeBasic(stringTypeContent);
$("#basicValue").val(value.basicValue);
break;
case "Double":
$("#dataTypeSelect").val("double");
tryCatch(dataTypeSelect);
typeBasic(doubleTypeContent);
$("#basicValue").val(value.basicValue);
break;
case "Boolean":
$("#dataTypeSelect").val("boolean");
tryCatch(dataTypeSelect);
typeBasic(booleanTypeContent);
const basicValue = value.basicValue;
if (basicValue) {
$("#basicValue").val("true");
} else {
$("#basicValue").val("false");
}
break;
case "Entity":
$("#dataTypeSelect").val("entity");
tryCatch(dataTypeSelect);
tryCatch(typeEntity);
getEntity(value);
break;
case "WebImage":
$("#dataTypeSelect").val("image");
tryCatch(dataTypeSelect);
tryCatch(typeImage);
$("#url").val(value.address);
$("#altText").val(value.altText);
break;
case "FormattedNumber":
$("#dataTypeSelect").val("FNV");
tryCatch(dataTypeSelect);
tryCatch(typeFNV);
$("#format").val(value.numberFormat);
$("#number").val(value.basicValue);
break;
case "LinkedEntity":
$("#dataTypeSelect").val("entity");
tryCatch(dataTypeSelect);
tryCatch(typeEntity);
getEntity(value);
break;
}
});
}
/** Function for clearing the input boxes */ async function clearForm() {
$(".inputBox").val("");
$(".cardView").prop("checked", true);
$(".autoComplete").prop("checked", true);
$(".calcCompare").prop("checked", true);
$(".dotNotation").prop("checked", true);
$(".mainImage").prop("checked", false);
$(".sublabel").val("");
$(".specificFieldContents").remove();
$(".sectionContents").remove();
}
/** Default helper for invoking an action and handling errors. */ async
function tryCatch(callback) {
try {
await callback();
} catch (error) {
console.error(error);
alert("Error in running script:\n\n" + error);
}
}
language: typescript
template:
content: "<section class=\"ms-font-m main\">\n\t<h1>\n\t\tCreate and Retrieve Data\n\t</h1>\n\t<p>\n\t\tChoose the type of data you want to insert from the dropdown below or select a cell and press <b>Get Data</b> to\n\t\tretrieve its data:\n\t\t<p>\n\n\n\t\t\t<select id=\"dataTypeSelect\" name=\"dataType\" class=\"ms-Button ms-Button-label buttons\">\n\t\t\t\t<option class=\"typeSelect\" value=\"select\" disabled selected>Select Data Type</option>\n\t\t\t\t<option class=\"typeString\" value=\"string\">String</option>\n\t\t\t\t<option class=\"typeDouble\" value=\"double\">Double</option>\n\t\t\t\t<option class=\"typeBoolean\" value=\"boolean\">Boolean</option>\n\t\t\t\t<option class=\"typeEntity\" value=\"entity\">Entity</option>\n\t\t\t\t<option class=\"typeImage\" value=\"image\">Web Image</option>\n\t\t\t\t<option class=\"typeFNV\" value=\"FNV\">Formatted Number</option>\n\t\t\t</select>\n\n\t\t\t<div id=\"dataTypeContents\"></div>\n\n\n\t\t\t<button id=\"getData\" onclick=\"getData()\" class=\"ms-Button ms-Button-label buttons\">Get Data</button>\n</section>\n\n<section class=\"ms-font-m main\">\n\t<br><a\n\t\thref=\"https://docs.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#valuesAsJson\">Learn\n\t\tmore about valuesAsJson</a><br>\n</section>"
language: html
style:
content: |
section.samples .ms-Button, section.setup, .main {
display: block;
margin-bottom: 5px;
margin-left: 20px;
margin-right: 20px;
min-width: 50px;
}
.backgroundColorForm {
background-color: #E0E0E0;
padding: 20px 0px;
border-radius: 5px;
border-style: solid;
border-width: thin;
border-color: #D0D0D0;
width: 530px;
}
.contentPadding {
padding: 0 10px;
}
.arrows {
background-color: #E0E0E0;
border-color: #E0E0E0;
margin: 0px;
min-width: 0px;
padding: 5px 5px;
display: block;
}
.checkboxes {
display: inline;
}
.buttons {
margin: 10px;
}
.center {
text-align: center;
}
.centerObject {
margin: 5px auto;
}
.columnTitle {
text-align: center;
font-weight: bold;
}
.formContents {
margin: 5px 10px;
padding: 10px;
border-style: solid;
border-width: thin;
border-color: #C0C0C0;
border-radius: 5px;
}
.metadata {
visibility: collapse;
}
.inputBox {
display: block;
margin: 0px auto;
width: 90%;
padding: 5px 2px;
min-width: 200px;
}
.labels {
display: block;
margin: 2px 10px;
min-width: 175px;
font-weight: bold;
}
.sectionHeader {
display: block;
margin-left: 5px;
margin-right: 15px;
font-weight: bold;
}
.sectionTitle {
min-width: 290px;
}
.sectionToggle {
text-align: right;
padding: 10px 15px;
}
language: css
libraries: |
https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
https://appsforoffice.microsoft.com/lib/beta/hosted/office.d.ts
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css
core-js@2.4.1/client/core.min.js
@types/core-js
jquery@3.1.1
@types/jquery@3.3.1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment