TL;DR

Today I am going to build a contacts management system:

You can add all your contacts from different resources from any type/size of file 🤯
Edit them inline dynamically - like an Excel sheet 📝
Get live updates when somebody else changes the worksheet ⤴️

Let's do it 🚀

Manage your contacts live 🖖🏻

We will build a cool Excel spreadsheet that can be updated live

For that, we must use Websockets or Server-Sent Events (SSE).

To simplify the process, we will use Supabase real-time.

What is Supabase real-time?

Supabase real-time is pretty neat.

It’s basically a Postgres database living up there in the cloud, and when something changes there, it sends an event through WebSockets about the new change.

You can learn more about WebSockets here.

Let’s set it up 🔥

Let’s start by initiating a new NextJS project.



npx create-next-app@latest contacts

We will not use the new app router for that project, so please select that you don’t want it.

To use Spreadsheets, let’s install react-spreadsheet. It’s a young library, but I have high hopes for it!



npm install react-spreadsheet --save

Let’s open our index.tsx inside of pages and add our data state and react-spreadsheet.



import Spreadsheet from "react-spreadsheet";

export default function Home() {
  const [data, setData] = useState<{ value: string }[][]>([]);

    return (
        <div className="flex justify-center items-stretch">
            <div className="flex flex-col">
                <Spreadsheet darkMode={true} data={data} />
            </div>
        </div>
    );
}

Ok, so not much to see, be we will get there.

The react-spreadsheet comes out of the box with the option to modify the column within.

But it’s missing the option to:

Add new columns
Add new rows
Remove columns
Remove rows

So let’s add those, but before we do, there is a small thing we have to take care of.

We don’t want to spam Supabase with changes on every single word change.

The easiest way is to use a debouncer.

Debouncer who?

A debouncer is a way to tell our function - activate me after X time has passed since I was triggered.

So if the users try to change the text, it will only trigger the function 1 second after they finish.

Let’s install the debouncer:



npm install use-debounce --save

And import it to our project:



import { useDebouncedCallback } from "use-debounce";

Not we can create our update function



const debouncer = useDebouncedCallback((newData: any, diff) => {
  setData((oldData) => {
        // update the server with our new data
    updateServer(diff);
    return newData;
  });
}, 500);

As you can see, the debouncer updates our data from the state, but the function will only activate 500ms after the user triggers the function.

The main problem is that the debouncer is unaware of the data mutation (data) by reference.
And because it doesn’t know, it’s better to check it before.

So here is the function that gets the new data from the <Spreadsheet />, and if something really changes, it will trigger our debouncer.



const setNewData = (newData: {value: string}[][], ignoreDiff?: boolean) => {
    // This function will tell us what actually changed in the data (the column / row)
  const diff = findDiff(data, newData);

  // Only if there was not real change, or we didn't ask to ignore changes, trigger the debouncer.
  if (diff || ignoreDiff) {
    return debouncer(newData, diff);
  }
};

Now, let’s write the findDiff function.

It’s a simple comparison between 2 two-dimensional arrays.



const findDiff = useCallback(
    (oldData: { value: string }[][], newData: { value: string }[][]) => {
      for (let i = 0; i < oldData.length; i++) {
        for (let y = 0; y < oldData[i].length; y++) {
          if (oldData[i][y] !== newData[i][y]) {
            return {
              oldValue: oldData[i][y].value,
              value: newData[i][y].value,
              row: i,
              col: y,
            };
          }
        }
      }
    },
    []
  );

And now 🥁, we can let our Spreadsheet update our data!



<Spreadsheet
  darkMode={true}
  data={data}
  className="w-full"
  onChange={setNewData}
/>

As I said before, react-spreadsheet is not mature enough yet, so let’s build our missing functions.



// Add a new column
const addCol = useCallback(() => {
  setNewData(
    data.length === 0
      ? [[{ value: "" }]]
      : data.map((p: any) => [...p, { value: "" }]),
    true
  );
}, [data]);

// Add a new row
const addRow = useCallback(() => {
  setNewData(
    [...data, data?.[0]?.map(() => ({ value: "" })) || [{ value: "" }]],
    true
  );
}, [data]);

// Remove a column by index
const removeCol = useCallback(
  (index: number) => (event: any) => {
    setNewData(
      data.map((current) => {
        return [
          ...current.slice(0, index),
          ...current.slice((index || 0) + 1),
        ];
      }),
      true
    );
    event.stopPropagation();
  },
  [data]
);

// Remove a row by index
const removeRow = useCallback(
  (index: number) => (event: any) => {
    setNewData(
      [...data.slice(0, index), ...data.slice((index || 0) + 1)],
      true
    );
    event.stopPropagation();
  },
  [data]
);

Now let’s add the buttons to add a new row and column



<div className="flex justify-center items-stretch">
  <div className="flex flex-col">
    <Spreadsheet
      darkMode={true}
      data={data}
      className="w-full"
      onChange={setNewData}
    />
    <div
      onClick={addRow}
      className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
    >
      +
    </div>
  </div>
  <div
    onClick={addCol}
    className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
  >
    +
  </div>
</div>

The next part is tricky 😲

As I said before, the library is a little immature:



<div className="flex justify-center items-stretch">
  <div className="flex flex-col">
    <Spreadsheet
      columnLabels={data?.[0]?.map((d, index) => (
        <div
          key={index}
          className="flex justify-center items-center space-x-2"
        >
          <div>{String.fromCharCode(64 + index + 1)}</div>
          <div
            className="text-xs text-red-500"
            onClick={removeCol(index)}
          >
            X
          </div>
        </div>
      ))}
      rowLabels={data?.map((d, index) => (
        <div
          key={index}
          className="flex justify-center items-center space-x-2"
        >
          <div>{index + 1}</div>
          <div
            className="text-xs text-red-500"
            onClick={removeRow(index)}
          >
            X
          </div>
        </div>
      ))}
      darkMode={true}
      data={data}
      className="w-full"
      onChange={setNewData}
    />
    <div
      onClick={addRow}
      className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
    >
      +
    </div>
  </div>
  <div
    onClick={addCol}
    className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
  >
    +
  </div>
</div>

The columnLabels and rowLabels expect to get back an array of strings, but we give it an array of components 😈

You might need to use it with @ts-ignore, so this is how it should look now:

Everything runs locally (ATM), let’s send a request to the server with our update 🆙

First, let’s install axios



npm install axios --save

import it:



import axios from "axios";

And write our updateServer function!



const updateServer = useCallback(
  (serverData?: { value: string; col: number; row: number }) => {
    if (!serverData) {
      return;
    }
    console.log(serverData);
    return axios.post("/api/update-record", serverData);
  },
  []
);

Supabase time! ⏰

Head to Supabase and register.

Go to projects and add a new Project.

Now go to the SQL editor and run the following query.



CREATE TABLE public."values" (
    "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "row" smallint DEFAULT '0'::smallint,
    "column" smallint DEFAULT '0'::smallint,
    "value" text,
    UNIQUE ("row", "column")
);

This query creates the table values that contain the row and column numbers in our Spreadsheet. We also added a UNIQUE key on both (together) rows and columns because we can only have one match in our DB. We can upsert to the table since we mark them both in UNIQUE. So if the value exists, we just update it.

Since we will do SELECT queries from the client, let’s give the SELECT permission to everybody and then enable RLS.

Now let’s review our settings and copy our anon public and service role secret keys.

Create a new file inside of your project called .env



touch .env

And add the keys inside



SECRET_KEY=service_role_key
NEXT_PUBLIC_ANON_KEY=anon_key

Now let’s install supabase-js



npm install @supabase/supabase-js

Create a new folder called helpers, add a new file called supabase.ts, and add the following code:



import {createClient} from "@supabase/supabase-js";

// You can take the URL from the project settings
export const createSupabase = (key: string) => createClient('https://IDENTIFIER.supabase.co', key);

Now create a new folder inside of pages called api (most likely, the folder exists already).

Create a new file called update-record.ts and add the following code:



import type { NextApiRequest, NextApiResponse } from "next";
import { createSupabase } from "@contacts/helpers/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse
) {
  if (
    req.method !== "POST" ||
    typeof req.body.col === "undefined" ||
    typeof req.body.row === "undefined" ||
    typeof req.body.value === "undefined"
  ) {
    res.status(400).json({ valid: false });
    return;
  }
  const { data, error } = await supabase
    .from("values")
    .upsert(
      {
        column: req.body.col,
        row: req.body.row,
        value: req.body.value,
      },
      {
        onConflict: "row,column",
      }
    )
    .select();

  res.status(200).json({ valid: true });
}

Let’s see what’s going on here.

We import the previously created supabase.ts file and initiate a new instance with our SECRET_KEY - this is important because only with our SECRET_KEY can we mutate the database.

In the route, we check that the method is POST and that we have values in col, row, and value.

It’s important to check for undefined because we might get 0 or empty values.

Then, we do an upsert query that basically adds the row, column, and value, but if it exists, it just updates it.

Now let’s listen to changes on the client side and update our spreadsheet.

Import superbase again, but this time we will use the ANON key



import { createSupabase } from "@contacts/helpers/supabase";
const supabase = createSupabase(process.env.NEXT_PUBLIC_ANON_KEY!);

And now, let’s add a useEffect to our component:



useEffect(() => {
    supabase
      .channel("any")
      .on<any>(
        "postgres_changes",
        { event: "*", schema: "public", table: "values" },
        (payload) => {
          console.log(payload.new);
          setData((odata) => {
            const totalRows =
              payload?.new?.row + 1 > odata.length
                ? payload.new.row + 1
                : odata.length;

            const totalCols =
              payload.new?.column + 1 > odata[0].length
                ? payload.new?.column + 1
                : odata[0].length;

            return [...new Array(totalRows)].map((_, row) => {
              return [...new Array(totalCols)].map((_, col) => {
                if (payload.new.row === row && payload.new?.column === col) {
                  return { value: payload?.new?.value || "" };
                }

                return { value: odata?.[row]?.[col]?.value || "" };
              });
            });
          });
        }
      )
      .subscribe();
  }, []);

We subscribe to the values table and update our data anytime we get changes.

Let’s look at a few highlights here.

data format usually looks something like this:



[
    [row1_col1, row1_col2, row1_col3, row1_col4],
    [row2_col1, row2_col2, row2_col3, row2_col4]
]

But what happens if we get row2_col4 but we don’t have row2_col1, row2_col2, row2_col3?
So to solve that, we just need to check for the highest row and the highest col and create a 2-dimensional array with the values.

The […new Array(value)] is a cool trick to create an array with empty values in your desired size.

Awesome 💃🏻 we have built the entire contacts system, but that’s not the end!

Let’s import all your contacts from other resources 🚀

Even if you have thousands of contacts, we can easily add them using FlatFile!

FlatFile is the easiest, fastest, and safest way for developers to build the ideal data file import experience. Those are the steps we are going to take:

We add the FlatFile React Component to load any file type (CSV / XSLX / XML, etc.)
We create a function that processes this file and insert the contacts into our database.
We deploy the function to the cloud, and FlatFile will take care of everything without our need to maintain it anymore 🤯

So go ahead and register to Flatfile, head to settings and copy the Environment ID, Publishable Key, and Secret Key

And paste them into our .env file.



NEXT_PUBLIC_FLAT_ENVIRONMENT_ID=us_env_
NEXT_PUBLIC_FLAT_PUBLISHABLE_KEY=pk_
FLATFILE_API_KEY=sk_

The Space 👽

FlatFile has a concept called Spaces, which are micro-applications, each with its own database, filestore, and auth.

Inside each space are different WorkBooks, which are basically a group for different spreadsheets.

Each time we want to load contacts, we will create a new space with one workbook and one sheet.

Now let’s install the FlatFile React component!



npm install @flatfile/react --save

Let’s create a new folder called components, and create our file importer.



mkdir components
cd components
touch file.importer.tsx

And then create a button to import our contacts



const FileImporterComponent: FC<{ data: string[] }> = (props) => {
  const { data } = props;
  const [showSpace, setShowSpace] = useState(false);

  return (
    <div className="flex justify-center py-5">
      <button
        className="bg-violet-900 p-3 rounded-3xl"
        onClick={() => {
          setShowSpace(!showSpace);
        }}
      >
        Import Contacts
      </button>
      {showSpace && (
        <div className="fixed w-full h-full left-0 top-0 z-50 text-black">
          <div className="w-[80%] m-auto top-[50%] absolute left-[50%] -translate-x-[50%] -translate-y-[50%] text-black space-modal">
            <FlatFileComponent
              data={data}
              closeSpace={() => setShowSpace(false)}
            />
          </div>
        </div>
      )}
    </div>
  );
};

export default FileImporterComponent;

As you can see, we are passing a parameter called data, that’s basically the name of all our headers (the first row in our spreadsheet) from the previous step.

We will send them to FlatFile, and FlatFile will try to guess which field belongs to which field 😎

Once we click on the Import Contacts button, it will open the FlatFile components.

Now let’s create our FlatFile component:



const FlatFileComponent: FC<{ data: string[]; closeSpace: () => void }> = (
  props
) => {
    const { data, closeSpace } = props;
  const theme = useMemo(() => ({
      name: "Dynamic Space",
      environmentId: "us_env_nSuIcnJx",
      publishableKey: process.env.NEXT_PUBLIC_FLAT_PUBLISHABLE_KEY!,
      themeConfig: makeTheme({ primaryColor: "#546a76", textColor: "#fff" }),
      workbook: {
        name: "Contacts Workbook",
        sheets: [
          {
            name: "ContactSheet",
            slug: "ContactSheet",
            fields: data.map((p, index) => ({
              key: String(index),
              type: "string",
              label: p,
            })),
          },
        ],
        actions: [
          {
            label: "Submit",
            operation: "contacts:submit",
            description: "Would you like to submit your workbook?",
            mode: "background",
            primary: true,
            confirm: true,
          },
        ],
      },
    } as ISpace), [data]);

  const space = useSpace({
    ...theme,
    closeSpace: {
      operation: "contacts:close",
      onClose: () => closeSpace(),
    },
  });

  return <>{space}</>;
};

Let’s see what’s going on here:

We use the React hook of useSpace to initiate a new FlatFile wizard.
We pass the environmentId and publishableKey that we got from the settings.
We map the fields from the name of our headers. In the key I pass the header index, so when I insert it later to Supabase I know the column number.
We set an action of submit, and we set the mode to be background because we don’t want to process the data over the front (we basically can’t because our Anon user doesn’t have access to INSERT into our database).

Let’s add our component to our main page.

FlatFile uses the window object. Since we are using NextJS, we cannot access the window object during server rendering. We have to use a dynamic import to add it:



import dynamic from "next/dynamic";

const FileImporterComponent = dynamic(() => import("../components/file.importer"), {
  ssr: false,
});

return (
    <>
      {!!data.length && <SpaceComponent data={data[0].map((p) => p.value)} />}
      <div className="flex justify-center items-stretch">
        <div className="flex flex-col">
          <Spreadsheet
            columnLabels={data?.[0]?.map((d, index) => (
              <div
                key={index}
                className="flex justify-center items-center space-x-2"
              >
                <div>{String.fromCharCode(64 + index + 1)}</div>
                <div
                  className="text-xs text-red-500"
                  onClick={removeCol(index)}
                >
                  X
                </div>
              </div>
            ))}
            rowLabels={data?.map((d, index) => (
              <div
                key={index}
                className="flex justify-center items-center space-x-2"
              >
                <div>{index + 1}</div>
                <div
                  className="text-xs text-red-500"
                  onClick={removeRow(index)}
                >
                  X
                </div>
              </div>
            ))}
            darkMode={true}
            data={data}
            className="w-full"
            // @ts-ignore
            onChange={setNewData}
          />
          <div
            onClick={addRow}
            className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
          >
            +
          </div>
        </div>
        <div
          onClick={addCol}
          className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
        >
          +
        </div>
      </div>
    </>
  );

Once you save everything, you should see something like this:

Awesome!

The only thing left is to load everything into our database.

Let’s install some FlatFile dependencies



npm install @flatfile/listener @flatfile/api --save

Create a new file called listener.ts

This is a special file that listens to file imports.

Let’s import FlatFile and Supabase.



import { FlatfileEvent, Client } from "@flatfile/listener";
import api from "@flatfile/api";
import { createSupabase } from "./src/database/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);

We can add our listener of contacts:submit that we have coded in the previous steps:



export default function flatfileEventListener(listener: Client) {
  listener.filter({ job: "workbook:contacts:submit" }, (configure) => {
    configure.on(
      "job:ready",
      async ({ context: { jobId, workbookId }, payload }: FlatfileEvent) => {
                // add to supabase
            }
    );
  });
}

To insert the new values, we need to take the highest row currently in the DB and increment it.



const row = await supabase
          .from("values")
          .select("row")
          .order("row", { ascending: false })
          .limit(1);

        let startRow = row.data?.length ? row.data[0].row + 1 : 0;

Then we take all the records in the imported file



const { data: sheets } = await api.sheets.list({ workbookId });
const records = (await api.records.get(sheets[0].id))?.data?.records || [];

We fetch and add them to our database.

We also use the api.jobs.ack call to inform the user of the front about the progress of the import.



for (const record of records) {
    await api.jobs.ack(jobId, {
      info: "Loading contacts",
      progress: Math.ceil((index / records.length) * 100),
    });
    await Promise.all(
      Object.keys(record.values).map((key) => {
        console.log({
          row: startRow,
          column: +key,
          value: record.values[key].value,
        });
        return supabase
          .from("values")
          .upsert(
            {
              row: startRow,
              column: +key,
              value: record?.values?.[key]?.value || '',
            },
            {
              onConflict: "row,column",
            }
          )
          .select();
      })
    );
    startRow++;
    index++;
}

Once the import is completed, we can finish the job on the client side.



await api.jobs.complete(jobId, {
  outcome: {
    message: "Loaded all contacts!",
  },
});

The full listener.ts file should look like this:



import { FlatfileEvent, Client } from "@flatfile/listener";
import api from "@flatfile/api";
import { createSupabase } from "./src/database/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);

export default function flatfileEventListener(listener: Client) {
  listener.filter({ job: "workbook:contacts:submit" }, (configure) => {
    configure.on(
      "job:ready",
      async ({ context: { jobId, workbookId }, payload }: FlatfileEvent) => {
        const row = await supabase
          .from("values")
          .select("row")
          .order("row", { ascending: false })
          .limit(1);

        let startRow = row.data?.length ? row.data[0].row + 1 : 0;

        const { data: sheets } = await api.sheets.list({ workbookId });

        // loading all the records from the client
        const records =
          (await api.records.get(sheets[0].id))?.data?.records || [];
        let index = 1;
        try {
          for (const record of records) {

            // information the client about the amount of contacts loaded
            await api.jobs.ack(jobId, {
              info: "Loading contacts",
              progress: Math.ceil((index / records.length) * 100),
            });

            // inserting the row to the table (each cell has a separate insert)
            await Promise.all(
              Object.keys(record.values).map((key) => {
                console.log({
                  row: startRow,
                  column: +key,
                  value: record.values[key].value,
                });
                return supabase
                  .from("values")
                  .upsert(
                    {
                      row: startRow,
                      column: +key,
                      value: record?.values?.[key]?.value || "",
                    },
                    {
                      onConflict: "row,column",
                    }
                  )
                  .select();
              })
            );
            startRow++;
            index++;
          }
        } catch (err) {
            // failing the job in case we get an error
            await api.jobs.fail(jobId, {
                info: 'Could not load contacts'
            });

            return ;
        }

        // Finishing the job
        await api.jobs.complete(jobId, {
          outcome: {
            message: "Loaded all contacts!",
          },
        });
      }
    );
  });
}

To recap everything:

We created a new file called listener.ts that listens to new imports.
We added a filter called workbook:contacts:submit to catch all the contacts imports (you can have multiple filters in case you have files import in different places).
We iterate over the contacts and add them to our DB.
We inform the client about the percentage of our progress with api.jobs.ack
If there is a failure, we will inform the client with [api.jobs.fail](http://api.jobs.fail)
If everything is okay, we will inform the client with api.jobs.complete