GMH Data Model Excel Workbook Modules

README.md

GMH Data Model Excel Workbook Modules

Note

These modules are meant to be managed via the Excel Labs Add-In using the Advanced Formula Environment.

Modules

• Workbook Module
• SQL Module
• Address Module

Address.Module.txt

/*
 * Extract the Street from an Address
 */
AddressStreet = LAMBDA(address, LEFT(address,FIND(",",address)-1));

/*
 * Extract the City from an Adderss
 */
AddressCity = LAMBDA(address, TRIM(MID(address,FIND(",",address)+1,FIND(",",address,FIND(",",address)+1)-FIND(",",address)-1)));

/*
 * Extract the State from an Address
 */
AddressState = LAMBDA(address, TRIM(MID(address,FIND(",",address,FIND(",",address)+1)+2,2)));

/*
 * Extrct the Post Code from an Address
 */
AddressPostalCode = LAMBDA(address, RIGHT(address, 5));

SQL.Module.txt

// --- SQL Module ---
// Module containing Lambdas for generating SQL DDL Statements.

/**
 * Generates SQL ENUM statements from header and values
 */
SQLEnum = LAMBDA(header_cell, values_range, 
    "CREATE TYPE ""survey""." & CHAR(34) & header_cell & CHAR(34) & " AS ENUM (" & CHAR(10) & 
    TEXTJOIN("," & CHAR(10), TRUE, "  '" & FILTER(values_range, values_range <> "") & "'") & CHAR(10) & 
    ");"
);

/**
 * Formats a value properly for SQL based on its data type
 */
SQLValue = LAMBDA(value,
    LET(
        fmt, CELL("format", value),
        is_date, LEFT(fmt, 1)="D",
        formatted,
            IFS(
                ISTEXT(value), "'" & SUBSTITUTE(value, "'", "''") & "'",
                ISBLANK(value), "NULL",
                is_date, "'" & TEXT(value, "yyyy-mm-dd hh:mm:ss") & "'",
                ISLOGICAL(value), IF(value, "TRUE", "FALSE"),
                TRUE, value
            ),
        formatted
    )
);

/**
 * Formats a row of data as SQL VALUES tuple
 */
SQLValues = LAMBDA(row_data,
    "(" & TEXTJOIN(", ", FALSE, MAP(row_data, SQLValue)) & ")"
);

/**
 * Generates a complete SQL INSERT statement for a range of data
 */
SQLInsert = LAMBDA(table_name, columns, data,
    LET(
        col_list, "(" & TEXTJOIN(", ", FALSE, columns) & ")",
        values, BYROW(data, SQLValues),
        values_with_commas, MAP(
            values, 
            SEQUENCE(ROWS(values)), 
            LAMBDA(v, r, v & IF(r = ROWS(values), "", ", "))
        ),
        all_values, TEXTJOIN(CHAR(10), FALSE, values_with_commas),
        "INSERT INTO " & table_name & " " & col_list & " VALUES " & CHAR(10) & all_values & ";"
    )
);

/**
 * Generates a SQL CREATE TABLE statement based on column names and types
 */
SQLCreateTable = LAMBDA(table_name, columns, types, 
    LET(
        columns_and_types, MAP(columns, types, LAMBDA(col, type, "  " & col & " " & type)),
        columns_text, TEXTJOIN("," & CHAR(10), FALSE, columns_and_types),
        "CREATE TABLE " & table_name & " (" & CHAR(10) & columns_text & CHAR(10) & ");"
    )
);

/**
 * Generates a SQL SELECT statement with optional WHERE clause
 */
SQLSelect = LAMBDA(columns, table, [where_clause],
    LET(
        cols_text, IF(columns = "*", "*", TEXTJOIN(", ", FALSE, columns)),
        where_part, IF(ISOMITTED(where_clause), "", " WHERE " & where_clause),
        "SELECT " & cols_text & " FROM " & table & where_part & ";"
    )
);

/**
 * Get SQL Data Types
 */
SQLType =LAMBDA(in_list,
  IF(
    AND(ROWS(in_list)<>1,COLUMNS(in_list)<>1),
    NA(),
    LET(
     _list,in_list,
     _txt,ISTEXT(_list),
     _formats,MAP(_list,LAMBDA(r,CELL("format",INDEX(r,1,1)))),
     _isdates,LEFT(_formats,1)="D",
     _out,MAKEARRAY(
            ROWS(_list),
            COLUMNS(_list),
            LAMBDA(r,c,
              IFS(
                INDEX(_txt,r,c),"text",
                INDEX(_isdates,r,c),"date",
                TRUE,"number"
              )
            )
          ),
     FILTER(_out,in_list<>"")
    )
  )
);

/**
 * Determines if the current row is the last row in a range
 */
SQLLastRow = LAMBDA(data, current_row,
    current_row = (ROWS(data) + MIN(ROW(data)) - 1)
);

/**
 * Generate the created_at & updated_at SQL
 */
SQLCreatedAtUpdatedAt = "  created_at TIMESTAMPTZ DEFUALT NOW()," & CHAR(10) & "  updated_at TIMESTAMPTZ DEFUALT NOW()" & CHAR(10);

/**
 * Generate the created_by & updated_by SQL
 */
SQLCreatedByUpdatedBy = "  created_by UUID," & CHAR(10) & "  updated_by UUID" & CHAR(10);

Workbook.Module.txt

// --- Workbook Module ---
// Module containing named range definitions for the GMH Data Model Workbook.

/**
 * Get Sheet Names of Current Workbook
 */
Workbook.GetSheetNames = TRANSPOSE(SUBSTITUTE(GET.WORKBOOK(1), "[" & GET.WORKBOOK(16) & "]", ""));

/**
  * Define SheetNames
 */
Workbook.SheetNames = Workbook.GetSheetNames;

/** 
 * Get Sheet Names Filtering for a Pattern
 */
Workbook.GetSheetNamesByPattern = LAMBDA(pattern, 
  FILTER(SheetNames, REGEXTEST(SheetNames, pattern, 0))
);

/**
 * Define the Default Property Image URL
 */
Workbook.DefaultPropertyImageURL = "https://placehold.co/600x400.png";