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
In order to solve the task, I’ve created CopyDataFromSStoAnotherSSWithComplexFilter function. It receives the following parameters:
- source spreadsheet id
- source sheet name
- complex filter – limit data with combination of simpler filters
- limit range of data to be processed
- target spreadsheet id
- 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.