react.js · 2022Copy Paste - Glide Data Grid

At work, I was tasked to add the copy-paste functionality to our app. We are currently using Glide Data Grid in displaying records of a remote database. Luckily the grid has a built-in copy paste support so I was able to implement it with a custom callback.

Role

Individual Contributor

Tech Stack

• ReactJS
• Glide Data Grid v5.0.0
• Rails
• PostgreSQL

Glide Data Grid is a React Data Grid that can render millions of rows, has a smooth scroll experience, and open source.

Copy Functionality

For the copy functionality, it was the easiest thing to do. To enable it, you just need to set getCellsforSection to true.

<DataEditor {...rest} getCellsForSelection />

However, this only works when you are using the built in Cell Kinds in the Glide Data Grid. If you are using custom cell kinds, you need to add a little something.

Rather than finding it in the documentation, I found the solution to enabling the copy functionality for custom cell kinds from digging through the Glide Data Grid's repository. All you need to do was add in a copyData in the getCellContent callback for those custom cell kinds like so:

const getCellContent = React.useCallback((cell: Item): GridCell => {
    const [col, row] = cell;
    const data = records[row][col];

    return {
      kind: GridCellKind.Text,
      allowOverlay: false,
      displayData: data,
      data,
+     copyData: data,
    };

}, []);

return <DataEditor getCellContent={getCellContent} getCellsForSelection {..rest} />;

Paste Functionality

The trickiest to implement between the two is the paste function. Although Glide Data Grid has paste support, the problem lies in implementing it in our app, making it work well with our codebase.

Based on the docs, you can implement the paste functionality by setting onPaste to true. On paste, this will trigger onCellEdited for every cell that has been affected.

return <DataEditor {...rest} onCellEdited={onCellEdited} onPaste={true} />

This method will do well for some, but for our app this means running an SQL UPDATE statement on each and every cell update which I think is inefficient. Luckily, Glide Data Grid also supports passing your own callback to the onPaste function. Returning false will prevent the function to call the corresponding onCellEdited.

<DataEditor
  {...rest}
  onCellEdited={onCellEdited}
  onPaste={(target, value) => {
    // do stuff here
    return false;
  }}
/>

The parameters for the onPaste function are:

• target - A tuple of column index and row index. Returns [column, row].
• value - An array of pasted records. Returns something like [ ['Name', 'Description'], ['Name', 'Description'] ].

In implementing this in our app, there are multiple things I have to consider:

1. Identifying which records are affected with the change.
2. Grouping pasted data - we need to group the data by whether the data updates or inserts records.
3. Permissions - we need to check whether the user has a edit field data and/or add record permission.
4. Validating the pasted data - we need to check whether a certain data should be required, has compatible data type or has a specified format.
5. Doing update/insert records in the back end.
6. Updating the records state in UI, based on the updated/inserted records (ex. columns such as updated_at will be updated for existing records, while columns that uses default_values and auto_increment might also be updated when inserting records).

Other considerations are about the magic data which we will not support for now.

I already have code for cell validation and checking the permissions so I don't really have to write those. I just need to use the functions that are already there. The only points that I needed to do are grouping the data, doing the update/insertion of multiple records, and updating the UI state.

Updating multiple records

In doing the update, all I needed to know was what records and columns that are affected by the paste function. Since the onPaste function has a target parameter, I used it in getting the existing record. Then I went on to loop through the pasted record and replace the existing record's value.

const [col, row] = target;
...
for (let rowIndex = 0; rowIndex < pastedRecords.length; rowIndex++) {
  const pastedRecord = pastedRecords[rowIndex];
  const existingRecord = records[row + rowIndex];
  const updatedRecord = { ..existingRecord };
  ...

  pastedRecord.forEach((pastedCellValue, colIndex) => {
    const column = columns[col + colIndex];
    ...
    updatedRecord[column.name] = pastedCellValue;
  });
}

After that, I get the primary keys for each record and used it to update the state like so:

const recordsToUpdate = updatedRecords.map((record) => {
  const primaryKeys = fields.filter((item) => item.isPrimaryKey);
  ...
  return ({
    primaryKeys,
    data: record,
  })
});

setRecords(updatedRecords);

try {
  const result = await batchUpdateRecords({ tableId, records: recordsToUpdate });
  setRecords(recordsToUpdate.map(updateDataBasedOnResult));
} catch (ex) {
  setRecords(records);
  setError(ex);
}

In the back-end, I did the permissions checker, data validation, then I construct each record its own query based on the primary keys. After that, I used transactions in doing the update:

...
sequel_connect(@database) do |db|
  table_query = db.from(@table_name)

  records.each do |record|
    table_query
      .yield_self(&record[:query])
      .update(record[:data])
  end
end

Inserting multiple records

In doing the insert, I checked which pasted records doesn't have a corresponding existing record based on the target parameter. Since I was already looping through the pasted record for getting the updated record in which I validate and do other stuff with the data, I decided to also use it also for mapping the new record.

const [col, row] = target;
...
for (let rowIndex = 0; rowIndex < pastedRecords.length; rowIndex++) {
  const pastedRecord = pastedRecords[rowIndex];
+ const existingRecord = records[row + rowIndex];
  ...

  pastedRecord.forEach((pastedCellValue, colIndex) => {
    const column = columns[col + colIndex];
    ...
+   if (!existingRecord) {
+     newRecord[column.name] = pastedCellValue;
    } else {
      updatedRecord[column.name] = pastedCellValue;
    }
  });
}

In the back-end, I did the same method as what I did in the update records API, the difference was I was doing inserts. I also used the last inserted id for querying records that uses auto increment fields:

...
incremented_field = @fields.find {|field| field.is_primary_key && field.is_auto_increment}
inserted_records = []

sequel_connect(@database) do |db|
  db.transaction do
    table_query = db.from(@table_name.to_sym)
    records.each do |record|
      last_inserted_id = table_query.insert(record[:data])
      
      # For auto-incremented fields, use last inserted id as primary key if empty
      if last_inserted_id != nil && incremented_field && record[:primary_keys].length == 0
        record[:primary_keys][incremented_field.name.to_sym] = last_inserted_id
      end
      
      # Query inserted field to get updated record (esp default values fields)
      # Construct sequel_query based on the primary_keys
      inserted_record = table_query.yield_self(&sequel_query).first
      inserted_records.push(inserted_record)
    end
  end
end

inserted_records

Finishing the Paste Functionality

Combining the inserts and updates we get the proper paste functionality.

Though I think what I've written can be optimized. For now, this solution that I first come up with is good enough especially that I don't really have a lot of time to implement this since I need to go back to the other project, the sandbox game.