How to Transfer Data from Multiple Sheets to a Single Sheet

In this article we will focus on the general flow of transferring data from multiple sheets (from multiple suppliers) to one single sheet (master). 

In this example we will use 3 different supplier sheets as an example to keep the article short and concise, however, you are free to use as many as you need.

Sheet 1 - Supplier 1

Sheet 2 - Supplier 2

Sheet 3 - Supplier 3

Sheet 4 - Intermediary Sheet (one that gathers all products and chooses the lowest priced ones to send further)

Sheet 5 - Master Sheet (one that receives filtered data from Sheet 4)

* Keep in mind that the structure (headers and their position) should be the same for every sheet. We recommend you use our Custom CSV module, and select SyncSpider sFTP as a means of storage. This way the max CSV size is 50 Mb and you can store up to 2 Gb of data. 

** Price fields' value type should be changed to Number on all sheets, so we can manipulate them later on:

Step 1.

Transfer data from Sheet 1 to Sheet 4. Select "overwrite file each time" as the export behavior while configuring the target integration:

Let the task run on a schedule, every 3 hours for example:

Step 2. 

Transfer data from Sheet 2 to Sheet 4. Select "Append data to existing file" as the method in the target configuration screen this time:

The task trigger should be "on event" - "task succeeded". The task we need to select is the one from the first step.

Step 3.

Similarly, now we need to transfer the data from Sheet 3 to Sheet 4. Once again, the "Append data to existing file" option should used. The trigger should be "on event" - "task succeeded". The task we need to select is the one from the previous (second) step.

Step 4.

Now we need to transfer the data from Sheet 4 to Sheet 5. This is the part where we create a new field that will choose only the products that have the lowest price and exclude the rest while sending the data to Master Sheet (Sheet 5).

In the source configuration, scroll down and select the "compare field":

Configure this new field in the following way:

IMPORTANT: In the mapping section, we need to map this newly generated field with the price field of our Sheet 5 (Master Sheet).

The trigger should be "on event" - "task succeeded". The task we need to select as a trigger is the one from the previous (third) step.

To check the list of the tools and features we support please click here!

If you don't see your favorite tool on our list, please take the time and send us the request here to create an integration for you or UP-vote if it's already on the list :) 

We develop integrations based on the requests and up-votes :) 

We hope that this covers everything. If you have any further questions or need additional clarification please reach out to us via [email protected] or our chat widget!