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.
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.
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 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
(‘==’, ‘!=’, ‘>’, ‘<’, ‘>=’, ‘=<’, ‘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
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;
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.
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