## Problem

You need to copy a bunch of data from one spreadsheet to another with the following custom conditions: limited range and filters on different cell values. This is a very boring task to perform manually, but the good news is that it can be automated.

## Solution

• source sheet name
• complex filter – limit data with combination of simpler filters
• limit range of data to be processed
• target sheet name
• target cell position – initial place for copied data
• arrangement method – insert as new rows or replace existing cell values

Let’s try this function on the example we have available.

## Working example

Open example spreadsheet and wait for 5 seconds for these menus to appear:
Our example has 3 sheets:

• config_data – keeps all settings for our example
• source_data – data which will be copied
• target_data – contains result of our example execution

To copy data use “Copy data with config_data settings” menu item from “Run Functions Examples”.
You can change settings in B column of config_data sheet and see how it affects target_data sheet. But in order to be able to customize them properly, let’s discuss how to cook filter conditions.

## Complex Filter

Complex filter combines ordinary filters with AND, OR operators. Ordinary filter is an array – [column, filteringValue, filteringCondition]. Where column – number of column (starts from 0), which is compared to filteringValue with filteringCondition (‘==’, ‘!=’, ‘>’, ‘<’, ‘>=’, ‘=<’, ‘contains’). For example:

`var ordinaryFilter = [0,”solar”, “contains”];`

This condition leaves only rows containing “solar” word in A column (0 index).
Array of ordinary filters is combined with AND operators:

`var condition1withAND = [ordinaryFilter1, ordinaryFilter2, ordinaryFilter3];`

Array of different conditionswithAND is combined with OR operator:

`var complexfilter = [condition1withAND, condition2withAND, condition3withAND]`

It means that returned data will meet condition1withAND OR condition2withAND OR condition3withAND.

Thus, we can build any complex condition. And there is another powerful option – limit source data by range.

## Source Data Range Limit

Next array limits source data by range:

`var sourceDataRangeLimit = [startRow, startColumn, numRows, numColumns];`

Currently we support only one range limit.

## Two ways to copy prepared data to the target sheet

Filtered and limited data can be inserted (default):

`var isReplace = false;`

or replaced:

`var isReplace = true;`

For inserting, bunch of empty rows is created starting from specified target position and data is copied there.
For replacing, keep in mind that existing data will be overwritten.

## Conclusion

We’ve described all the key points of our example and you can start playing with it. In the next post I’m going to describe DeleteByRangeWithComplexFilter function.