Created
October 26, 2021 04:40
-
-
Save jiju-MS/b2a19b30f9f91c9fcfdea1a1569272c0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: custom function custom data sample | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: |- | |
/** | |
* Create custom data. | |
* @customfunction | |
* @param {string} dataType | |
* @returns custom data by different type. | |
*/ | |
function createCustomData(dataType) { | |
switch (dataType) { | |
case "Entity": { | |
return { | |
type: "Entity", | |
basicType: "Error", | |
basicValue: "#VALUE!", | |
text: "My Entity", | |
properties: { | |
TestDouble: { type: "Double", basicType: "Double", basicValue: 1}, | |
TestString: { type: "String", basicType: "String", basicValue: "Hello World!" } | |
} | |
}; | |
} | |
case "FormattedNumber": { | |
return { | |
type: "FormattedNumber", | |
basicValue: 1.23, | |
numberFormat: "0.00%" | |
} | |
} | |
case "WebImage": { | |
return { | |
type: "WebImage", | |
basicType: "Error", | |
basicValue: "#VALUE!", | |
address: "https://upload.wikimedia.org/wikipedia/en/c/cd/Marioparty1.jpg" | |
}; | |
} | |
case "Error":{ | |
return { | |
type: "Error", | |
basicValue: "#DIV/0!", | |
errorType: "Div0" | |
} | |
} | |
default: { | |
return "Invalid data type"; | |
} | |
} | |
} | |
/** | |
* get custom data. | |
* @customfunction | |
* @param {any} value | |
* @param {string} attribute | |
* @returns {any} The volume of the sphere. | |
*/ | |
function getCustomData(value, attribute) { | |
if (value.type == "Entity") { | |
if (attribute == "text") return value.text; | |
else { | |
return value.properties[attribute].basicValue; | |
} | |
} else if (value.type == "FormattedNumber") { | |
return value[attribute]; | |
} else if (value.type == "Error") { | |
return value[attribute]; | |
} else if (value.type == "WebImage") { | |
return value[attribute]; | |
} else { | |
return JSON.stringify(value); | |
} | |
} | |
/** | |
* Create formatted number. | |
* @customfunction | |
* @param {number} value | |
* @param {string} format(e.g. "0.00%") | |
* @returns custom data by different type. | |
*/ | |
function createFormattedNumber(value, format) { | |
return { | |
type: "FormattedNumber", | |
basicValue: value, | |
numberFormat: format | |
} | |
} | |
/** | |
* get entity attribute. | |
* @customfunction | |
* @param {any} value | |
* @param {string} attribute | |
* @returns {any} The volume of the sphere. | |
*/ | |
function getEntityAttribute(value, attribute) { | |
if (value.type == "Entity") { | |
if (attribute == "text") return value.text; | |
else { | |
return value.properties[attribute].basicValue; | |
} | |
} else { | |
return JSON.stringify(value); | |
} | |
} | |
language: typescript | |
libraries: | | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
@types/office-js | |
core-js@2.4.1/client/core.min.js |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment