SQLTools Excel Labs Module (LAMBDAs)

SQL.txt

/**
 * 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 & ";"
    )
);

/*
SQL.TYPE

Returns an array of values from the set {"text","date","integer","number"} indicating how a cell's value should be formatted for use in a SQL statement

Inputs:
	- in_list: a 1-dimensional array or range, which can be horizontal or vertical

Return:
  an array the same size as in_list, containing:
    "text" if the element is text
    "date" if the element is from a cell formatted as a date (i.e. LEFT(CELL("format",A1),1)="D")
    "number" otherwise
  
  If the input array is not 1-dimensional, returns NA()
*/
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)
);