toHexColor and getSentiment - Shared with Script Lab

Custom Functions demo.yaml

name: Custom Functions demo
description: toHexColor and getSentiment
author: Zlatkovsky
host: EXCEL
api_set: {}
script:
    content: |
        /** @customfunction */
        function toHexColor(input: string): string {
            try {
                // Using open-source library from https://github.com/bgrins/TinyColor
                const color = tinycolor(input);
                if (!color.isValid()) {
                    throw new Error();
                }
                return "#" + color.toHex();
            } catch (e) {
                throw new Error("Invalid color");
            }
        }

        // Forward-declare the TypeScript definition of the "tinycolor" library that's used by this snippet
        declare function tinycolor(input: string): IColor;

        declare interface IColor {
            toHex: () => string;
            isValid: () => boolean;
        };



        ///////////////////////////////////////////////////
        ///////////////////////////////////////////////////



        /** @customfunction */
        async function getSentiment(text: string): Promise<number> {

            // NOTE: Get your own cognitive services key by:
            // 1. Going to https://azure.microsoft.com/en-us/try/cognitive-services/
            // 2. Selecting "Language APIs"
            // 3. Clicking "Get API Key" under "Text Analytics"
            // 4. Fill out both the key AND the url in the next two lines:
            const key = "3f9255484d384f14a971fa1b463edd04";
            const baseUrl = "https://westcentralus.api.cognitive.microsoft.com/text/analytics/v2.0"

            const body = {
                "documents": [
                    {
                        "language": "en",
                        "id": 1,
                        "text": text
                    }
                ]
            };

            const url = baseUrl + "/sentiment";

            try {

                let response: ICognitiveServicesResponse = await $.ajax({
                    url: url,
                    beforeSend: function (xhrObj) {
                        xhrObj.setRequestHeader("Content-Type", "application/json");
                        xhrObj.setRequestHeader("Ocp-Apim-Subscription-Key", key);
                    },
                    type: "POST",
                    data: JSON.stringify(body),
                });

                if (response.errors && response.errors.length > 0) {
                    throw new Error(response.errors[0]);
                }

                return response.documents[0].score;
            } catch (e) {
                console.error(e);
                return -1;
            }
            
        }

        interface ICognitiveServicesResponse {
            "documents": [
                {
                    "score": number
                    "id": string
                }
            ],
            "errors": any[] | null
        }
    language: typescript
libraries: |-
    https://appsforoffice.microsoft.com/lib/1/hosted/office.js
    https://appsforoffice.microsoft.com/lib/1/hosted/office.d.ts

    core-js@2.4.1/client/core.min.js
    @types/core-js

    jquery@3.1.1
    @types/jquery

    tinycolor2