Clean up your Tables Quick and Easy!

Sometimes you import some data that looks like this:

But you want table data that looks like this:

This script does that:

let settings = input.config({
    title:'Table Optimizer',
    description: "Converts data that's stored over many fields, into data that's stored in one field with multiple parameters in a new table",
    items: [
            label: 'Source Table',
            description: 'The originating table'

            label: 'Fields to Convert',
            description: "Type out the fields you'd like to convert separated by a comma. Example: Field 1,Field 2,Field 3. All convert fields must be of the same type."

            label: 'Destination Table',
            description: 'The desitination table'

            label: 'Data Field',
            parentTable: 'destinationTable',
            description: 'Field where the table data will go. Must match field type of Conver Fields',

            label: 'Field Name Field',
            parentTable: 'destinationTable',
            description: 'Field where the original field names will be transposed',

            label: 'Linked Record Field',
            parentTable: 'destinationTable',
            description: 'Linked record which will link back to the desination table',


let sourceTable = settings.sourceTable
let fields = settings.fieldsConvert.split(',')
let destinationTable = settings.destinationTable
let fieldNameField =
let dataField =
let linkedField =

let query = await sourceTable.selectRecordsAsync({fields: fields})
let records = query.records

let arr = []

for (let field of fields){

    if( sourceTable.getField(field).type != settings.field1.type){

        output.markdown('## 🚫 Error: Please change your Data Field-Type to match your Convert Field Types')

    } else{

            records.filter( record => record.getCellValue(field)).map(record => Array.isArray(record.getCellValue(field)) 
            record.getCellValue(field).map( element => 
                [linkedField]: [record],
                [dataField]: [element],
                [fieldNameField]: field
                [linkedField]: [record],
                [dataField]: record.getCellValue(field),
                [fieldNameField]: field
            }}) )

await batchAnd('Create',destinationTable,arr.flat())

    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to

    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                records = records.slice(50);

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);

            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    return recordsActedOn;

Thanks God we have you back… (your last post was June 25)

1 Like