MS Excel – Copy As Array Constant

Macro Library
1

Recent builds of Microsoft Excel have introduced a Dynamic Array type into the formula language, and with it an Array Constant notation.

For the moment they don't have a Copy As > Array Constant option under the Edit menu, so here, in the interim, is a pair of macros for copying Excel selections (columns or rows, or grids of both) either in a single line format:

ExcelCopyAsArrayConstant.kmmacros.zip (12.5 KB)

Screenshot 2021-05-02 at 14.52.18Screenshot 2021-05-02 at 14.55.56

{8,1,6;3,5,7;4,9,2}

{"alpha",1;"beta",2;"gamma",4;"delta",8;"epsilon",16;"zeta",32;"eta",64}

or a pretty-printed format:

{
    8, 1, 6;
    3, 5, 7;
    4, 9, 2
}

{
    "alpha", 1;
    "beta", 2;
    "gamma", 4;
    "delta", 8;
    "epsilon", 16;
    "zeta", 32;
    "eta", 64
}
Expand disclosure triangle to view JS Source 
(() => {
    "use strict";

    ObjC.import("AppKit");

    // Rob Trew @2021
    // Copy as Excel Array Constant
    // (In a single line, or a pretty-printed format)

    // Ver 0.2
    // Double quotes added only to Excel date type 2 (text)

    // main :: IO ()
    const main = () => {
        const
            singleLine = 0 === (
                Application("Keyboard Maestro Engine")
                .getvariable("PrettyPrint")
                .trim()
            ).length;

        return bindLR(
            clipTextLR()
        )(
            compose(
                copyText,
                excelArrayConstantFromTSV(
                    singleLine
                )
            )
        );
    };

    // --- EXCEL ARRAY CONSTANT STRING FROM \T AND \N ----

    // excelType :: String -> Int
    const excelType = x =>
        // number = 1; text = 2;
        // logical value = 4; error value = 16;
        isNaN(x) ? (
            ["TRUE", "FALSE"].includes(x) ? (
                4
            ) : [
                "#DIV/0!", "#NAME?", "#N/A",
                "#NUM!", "#VALUE!", "#REF!",
                "#NULL!", "####", "#SPILL!"
            ].includes(x) ? (
                16
            ) : 2
        ) : 1;


    // excelArrayConstantFromTSV :: String -> String
    const excelArrayConstantFromTSV = singleLine =>
        // Excel Array Constant format.
        s => {
            const
                delim = {
                    brackets: [
                        ["{", "}"],
                        ["{\n\t", "\n}"]
                    ],
                    cells: [",", ", "],
                    rows: [";", ";\n\t"]
                },
                pretty = singleLine ? (
                    0
                ) : 1;

            return (
                delim.brackets[pretty]
            ).join(
                lines(s).map(
                    line => line.split("\t")
                    .map(
                        x => 2 === excelType(x) ? (
                            `"${x}"`
                        ) : x
                    )
                    .join(delim.cells[pretty])
                )
                .join(delim.rows[pretty])
            );
        };

    // ----------------------- JXA -----------------------

    // clipTextLR :: () -> Either String String
    const clipTextLR = () => (
        v => Boolean(v) && 0 < v.length ? (
            Right(v)
        ) : Left("No utf8-plain-text found in clipboard.")
    )(
        ObjC.unwrap($.NSPasteboard.generalPasteboard
            .stringForType($.NSPasteboardTypeString))
    );


    // copyText :: String -> IO String
    const copyText = s => {
        const pb = $.NSPasteboard.generalPasteboard;

        return (
            pb.clearContents,
            pb.setStringForType(
                $(s),
                $.NSPasteboardTypeString
            ),
            s
        );
    };

    // --------------------- GENERIC ---------------------

    // Left :: a -> Either a b
    const Left = x => ({
        type: "Either",
        Left: x
    });


    // Right :: b -> Either a b
    const Right = x => ({
        type: "Either",
        Right: x
    });


    // bindLR (>>=) :: Either a ->
    // (a -> Either b) -> Either b
    const bindLR = m =>
        mf => m.Left ? (
            m
        ) : mf(m.Right);


    // compose (<<<) :: (b -> c) -> (a -> b) -> a -> c
    const compose = (...fs) =>
        // A function defined by the right-to-left
        // composition of all the functions in fs.
        fs.reduce(
            (f, g) => x => f(g(x)),
            x => x
        );


    // lines :: String -> [String]
    const lines = s =>
        // A list of strings derived from a single
        // string delimited by newline and or CR.
        0 < s.length ? (
            s.split(/[\r\n]+/u)
        ) : [];

    return main();
})();

Footnote :: within the Excel formula language, you can obtain the single-line version of an Array Constant string with the format option 1 in an application of ARRAYTOTEXT, e.g.:

=ARRAYTOTEXT(L6:M12, 1)
3 Likes