Directus 9 Episode 4

Add Data From Excel Using Flows

In this episode I'm going to show you how you can add large number of data to your Directus database using Excel and Flows.

Ok, let’s do one more Directus Flow, well actually two flows that are going to work in conjunction with one another.

So imagine you have a movie database type of website, and you want to add a large number of movies to it. Currently, you have those movies stored in a Google Sheets or an Excel Document. Of course, you don’t wanna do that by hand, because it will take a long time to do it.

We can use Flows for this type of scenario, we will first save our Google Sheets or Excel document to a CSV file. We are going to parse that file on the front end and create a JSON object which we will send to a Directus Webhook, that will in turn trigger our first Flow. Since we are receiving an array of objects we can create a kind of for each loop by triggering another Flow for every object in that array. That other Flow is just simply going to add the data from the object it receives to the Movie collection.

Ok, so let’s do this.


Before we start creating our Flows, let’s just take a quick look at the setup.

First of all, we need a Movies collection, I already have a simple one setup. It has just 3 fields title, year, and director.

An important thing to note here is that we have to set permissions for the public role so that it can add items to the Movies collection. Of course in a real-world scenario you would give that permission only to authenticated users, but to keep it simple we will allow everyone to be able to add Movies.

Then we of course need a list of movies in Excel or Google Sheets format. I have one in Google Sheets here. We can save this list to a CSV file right away so we have it ready.

We also need some kind of frontend so that the user can upload that list to our Directus backend.

I already have this ready, it’s just a simple Next.js app with Tailwind for styling, that has a page on which we can upload our CSV file.

The code for uploading CSV looks like this.

I have a form that is going to do something on submit. Most of the code is just layout and styling for the file input field, which I stole from the Tailwind UI site.

When the user chooses a CSV file and clicks Submit, we are taking that file and parsing it using papaparse package - so don’t forget to install papaparse.

Once the CSV has been parsed, it’s going to save the resulting JSON to the moviesList state.

And then we have useEffect that has moviesList dependency, so once the moviesList state changes it will send our JSON movie list to the Webhook in Directus using fetch.

We will need to add a Flow ID to fetch the URL once we create our Flow.

The link to this code will be in the description below.

The Flow

Now we have to create our Flow, or I should better say, Flows because we are going to have two of them.

One flow is going to accept our data using a Webhook, and another one is going to add the data to our collection.

First, let’s create a Flow for getting our data. I will call it “Get Movie List”.

Click the right arrow to create the trigger.

The trigger is going to be a Webhook.

Set the method to POST, and check the “All data” checkbox.

Now click the checkmark to save the trigger, and then once again, to save the Flow.

We are not going to add the operation right away, because it is dependent on the other Flow. And since we don’t have it, we won’t be able to select it.

Ok, so now let’s create another Flow, I will name this one “Add Items to Movies Collection”.

And for the trigger, we are going to choose “Another Flow”. We are doing this because another flow - in our case “Get Movie List” - is going to trigger this flow.

Check the “All data” checkbox, and click on the checkmark to save our operation.

Now let’s create our operation, I will name it “Add Movie To Collection” and choose “Create Data” for the operation type.

I used a singular Movie for the name instead of Movies because this Flow is going to trigger as many times as you have movies in your list. And this is because we are going to pass an array of objects to this Flow. When you do that Directus is going to trigger this Flow for every object in that array. You can think of it like a for each loop for Directus Flows.

Ok, so for collection we are of course going to select “Movies”, Permissions are going to come from the trigger. And for Payload, we just need to define the fields that are set for our movie collection - title, year, and director.

Very simple, it will look like this:

    "title": "{{$trigger.title}}",
    "year": "{{$trigger.year}}",
    "director": "{{$trigger.director}}"

We are of course getting our data from the trigger. Notice that we don’t have $trigger.payload.something here, but just $trigger.name_of_the_field this is because we are going to be receiving just an array of objects from that other Flow.

Speaking of which, click the checkmark to save this operation, and once again to save the Flow, and let’s finish setting up “Get Movie List Flow”.

Finish “Get Movie List Flow”

Go to Flows and select “Get Movie List” Flow.

We are going to add an operation here and name it “Send Movie”. The operation type is going to be “Trigger Flow”. Because remember, once we receive our array of Movies, we want to trigger the “Add Items to Movies Collection” Flow.

For Flow select “Add Items to Movies Collection”.

We have to add our payload now, but there is a very important thing to note here, you don’t add it directly to Payload input but click on the dropdown next to the label and select “Edit Raw Value”. Now in here add just this simple line of code.


And click “Done”. We need to do it this way because if we don’t Directus is going to screw up our string with escape characters and then our payload will not work correctly.

Also, note that we are saying $trigger.body - body is the body of the POST request that we are sending from our frontend, and that body is going to hold our array of movie objects.

Save this operation by clicking on the checkmark, and once again to save the Flow.

Test it out

Now we have to test this out. But before that, you need to copy the ID of this Flow from the URL.

And then go to your frontend app and add the ID after /flows/trigger

Save it and let’s test if this works.

Let’s add our Movie List CSV file, and click submit.

Ok, now let’s go to the Movies Collection and see if this worked.

And sure enough, it works, and all our movies from the Movie list are now in the collection, with essentially one click.


As I’ve said, once you send an array of objects to the “Add Items to Movies Collection” Flow, this flow is going to trigger as many times as we have Movies in our list. And to check that, you can go to that Flow, and click on Logs on the right-hand side.

You will see that we have logged five operations here, and if you click on one of them and then check the payload, you can see that the payload is just one movie object. So this Flow has run five times to add five movies.

Wanna ask a question about video?

Like This Video?

Support it by sharing it ;)

© Watch And Learn,2024