MacacaEAFE.txt

GetDataRange = 
/*
Get address range from start cell to end column
startCell: Start Cell 
endColumn: End Column 
*/
LAMBDA(startCell, endColumn,
    LET(
        startRow, ROW(startCell),
        startCol, COLUMN(startCell),
        endCol, COLUMN(endColumn),
        lastRow, LOOKUP(2, 1 / (INDEX(INDIRECT(ADDRESS(startRow, startCol) & ":" & ADDRESS(1048576, startCol)), 0)<>""), ROW(INDIRECT(ADDRESS(startRow, startCol) & ":" & ADDRESS(1048576, startCol)))),
        ADDRESS(startRow, startCol) & ":" & ADDRESS(lastRow, endCol)
    )
);

JSON_VALUE_TRANSFORM = 
/*
Transform a cell value into its JSON representation
value: Cell value to transform
*/
LAMBDA(value,
    SWITCH(
      TRUE,
      ISBLANK(value), QUOTE(""),
      ISNUMBER(value), value,
      HASJSONSTRUCTURE(value), value,
      value = TRUE(), "true",
      value = FALSE(), "false",
      value = NoData_Number(), "0",
      value = NoData_ENUM(), "0",
      value = NoData_Bool(), "false",
      value = NoData_Json(), "{}",
      value = NoData_Array(), "[]",
      value = NoData_Text(), QUOTE(""),
      QUOTE(value)
    )
);

TOJSON = 
/*
Convert table headers and data into a JSON array of objects
headers: Table headers
data: Table data
*/
LAMBDA(headers, data,
  LET(
    jsonArray,
    BYROW(data,
      LAMBDA(row,
        LET(
          jsonObject,
          TEXTJOIN(",", TRUE, 
            BYCOL(row, LAMBDA(value,
              LET(
                colIndex, XMATCH(value, row, 0, 1),
                key, INDEX(headers, 1, colIndex),
                jsonValue, JSON_VALUE_TRANSFORM(value),
                QUOTE(key) & ":" & jsonValue
              )
            ))
          ),
          "{" & jsonObject & "}"
        )
      )
    ),
    "[" & TEXTJOIN(",", TRUE, jsonArray) & "]"
  )
);

QUOTE = 
/*
Add double quotes around a text value
text: Text value to quote
*/
LAMBDA(text, CHAR(34) & text & CHAR(34));

HASJSONSTRUCTURE = 
/*
Check if a text value has JSON structure
text: Text value to check
*/
LAMBDA(text,
    LET(
        firstChar, LEFT(text, 1),
        lastChar, RIGHT(text, 1),
        AND(
            OR(firstChar = "{", firstChar = "["),
            OR(lastChar = "}", lastChar = "]")
        )
    )
);

ToCSharpEnum = 
/*
Convert two lists into a C# enum definition
items: List of enum names
values: List of enum values
*/
LAMBDA(items, values,
    LET(
        itemsCount, COUNTA(items),
        valuesCount, COUNTA(values),
        IF(
            itemsCount <> valuesCount,
            "length not equal!",
            LET(
                result,
                TEXTJOIN(", " & CHAR(10), TRUE,
                    BYROW(SEQUENCE(itemsCount), LAMBDA(rowIndex,
                        INDEX(items, rowIndex) & " = " & INDEX(values, rowIndex)
                    ))
                ),
                result
            )
        )
    )
);

TOJSONARRAY = 
/*
Convert a range of values into a JSON array
range: Range of values to convert
*/
LAMBDA(range,
  LET(
    data, range,
    jsonArray,
    BYROW(data,
      LAMBDA(row,
        TEXTJOIN(",", TRUE,
          BYCOL(row, LAMBDA(value,
            JSON_VALUE_TRANSFORM(value)
          ))
        )
      )
    ),
    "[" & TEXTJOIN(",", TRUE, jsonArray) & "]"
  )
);

TOJSONDICTIONARY = 
/*
Convert a key-value pair into a JSON dictionary
key: Dictionary key
value: Dictionary value
*/
LAMBDA(key, value,
  LET(
    jsonKey, QUOTE(key),
    jsonValue, JSON_VALUE_TRANSFORM(value),
    "{" & jsonKey & ":" & jsonValue & "}"
  )
);

TOJSONOBJECT = 
/*
Convert two ranges of keys and values into a JSON object
keysRange: Range of keys
valuesRange: Range of values
*/
LAMBDA(keysRange, valuesRange,
    LET(
        keys, keysRange,
        values, valuesRange,
        numKeys, COUNTA(keys),
        numValues, COUNTA(values),
        IF(
            numKeys <> numValues,
            "Number of keys and values do not match",
            LET(
                jsonPairs,
                BYCOL(SEQUENCE(1, numKeys), LAMBDA(colIndex,
                    LET(
                        key, INDEX(keys, colIndex),
                        value, INDEX(values, colIndex),
                        jsonValue, JSON_VALUE_TRANSFORM(value),
                        QUOTE(key) & ":" & jsonValue
                    )
                )),
                "{" & TEXTJOIN(",", TRUE, jsonPairs) & "}"
            )
        )
    )
);

TOISO8601 = 
/*
Convert a datetime value into ISO 8601 format
dateTime: Datetime value
timezone: Timezone offset in hours
*/
LAMBDA(dateTime, timezone,
    LET(
        adjustedDateTime, dateTime - timezone/24,  // Adjust dateTime by timezone offset to convert to UTC
        iso8601Format, "yyyy-mm-ddThh:MM:ss.000Z",  // ISO 8601 format string with milliseconds and UTC indicator
        iso8601DateTime, TEXT(adjustedDateTime, iso8601Format),  // Convert adjusted dateTime to ISO 8601 formatted date and time
        iso8601DateTime  // Return the ISO 8601 formatted string ending with .000Z in UTC
    )
);

CHECKINVALIDDATA = 
/*
Check a range for invalid data (empty cells or cells containing whitespace)
range: Range of data to check
*/
LAMBDA(range,
  LET(
    invalidCells,
    TEXTJOIN(", ", TRUE,
      IF(ISBLANK(range), "Empty cells: " & ADDRESS(ROW(range), COLUMN(range)),
        IF(ISNUMBER(FIND(" ", range)), "Cells containing whitespace: " & ADDRESS(ROW(range), COLUMN(range)), "")
      )
    ),
    IF(invalidCells = "", "Data valid!", invalidCells)
  )
);

/* NoData define */
NoData_Number = LAMBDA("NoData_Number");
NoData_Text = LAMBDA("NoData_Text");
NoData_ENUM = LAMBDA("NoData_ENUM");
NoData_Bool = LAMBDA("NoData_Bool");
NoData_Json = LAMBDA("NoData_Json");
NoData_Array = LAMBDA("NoData_Array");