Office Script - Converts a worksheet into a json format with nested properties

excel-worksheet-to-json.ts

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet and used range.
    let selectedSheet = workbook.getActiveWorksheet();
    let range = selectedSheet.getUsedRange();
    let texts = range.getTexts();
    let returnObjects: unknown[] = [];
    returnObjects = returnObjectFromValues(texts)
    console.log(JSON.stringify(returnObjects));
}    

function returnObjectFromValues(values: string[][]): unknown[] {
    let objectArray: unknown[] = [];
    let objectKeys: string[] = [];
    for (let i = 0; i < values.length; i++) {
        if (i === 0) {
            objectKeys = values[i]
            continue;
        }

        let object: { [key: string]: unknown } = {}
        for (let j = 0; j < values[i].length; j++) {
            setObjectValue(object, objectKeys[j], values[i][j])
        }

        objectArray.push(object as unknown);
    }

    return objectArray;
}

function setObjectValue(obj: unknown, key: string, value: unknown) {
    let actualValue: unknown = value
    if (actualValue == "") { actualValue = null }
    else if (actualValue == "NULL") { actualValue = null }
    else if (actualValue == "TRUE") { actualValue = true }
    else if (actualValue == "FALSE") { actualValue = false }
    else if (!isNaN(Number(value))) { actualValue = Number(value) }

    if (!key.match("\\.")) {
      obj[key] = actualValue;
        return
    }

    let parts: string[] = key.split(".")
    let current = obj
    for (let n = 0; n < parts.length; n++) {
        if (n == (parts.length - 1)) {
          current[parts[n]] = actualValue;
        }
        else {
            if (current[parts[n]] == null) {
                let newObject: { [key: string]: unknown } = {}
                current[parts[n]] = newObject
            }
            current = current[parts[n]]
        }
    }
}