ProblemYou 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.
SolutionIn 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
Working exampleOpen 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
Complex FilterComplex 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:
This condition leaves only rows containing “solar” word in A column (0 index). Array of ordinary filters is combined with AND operators:
var ordinaryFilter = [0,”solar”, “contains”];
Array of different conditionswithAND is combined with OR operator:
var condition1withAND = [ordinaryFilter1, ordinaryFilter2, ordinaryFilter3];
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.
var complexfilter = [condition1withAND, condition2withAND, condition3withAND]
Source Data Range LimitNext array limits source data by range:
Currently we support only one range limit.
var sourceDataRangeLimit = [startRow, startColumn, numRows, numColumns];
Two ways to copy prepared data to the target sheetFiltered and limited data can be inserted (default):
var isReplace = false;
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.
var isReplace = true;