- source spreadsheet id<\/li>
- source sheet name<\/li>
- complex filter – limit data with combination of simpler filters<\/li>
- limit range of data to be processed<\/li>
- target spreadsheet id<\/li>
- target sheet name<\/li>
- target cell position – initial place for copied data<\/li>
- arrangement method – insert as new rows or replace existing cell values<\/li><\/ul>\n\n\n\n
Let\u2019s try this function on the example we have available.<\/p>\n\n\n\n
Working example<\/h2>\n\n\n\n
Open example spreadsheet<\/a> and wait for 5 seconds for these menus to appear:
![\"2013-10-21_1311\"](\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/10\/2013-10-21_1311.png\")
<\/a>
Our example has 3 sheets:<\/p>\n\n\n\n- config_data – keeps all settings for our example<\/li>
- source_data – data which will be copied<\/li>
- target_data – contains result of our example execution<\/li><\/ul>\n\n\n\n
To copy data use “Copy data with config_data settings”<\/i> menu item from “Run Functions Examples”<\/i>.
You can change settings in B column of config_data<\/i> sheet and see how it affects target_data<\/i> sheet. But in order to be able to customize them properly, let’s discuss how to cook filter conditions.<\/p>\n\n\n\nComplex Filter<\/h2>\n\n\n\n
Complex filter combines ordinary filters with AND, OR operators. Ordinary filter is an array – [column, filteringValue, filteringCondition]<\/i>. Where column<\/i> – number of column (starts from 0), which is compared to filteringValue<\/i> with filteringCondition<\/i> (\u2018==\u2019, \u2018!=\u2019, \u2018>\u2019, \u2018<\u2019, \u2018>=\u2019, \u2018=<\u2019, \u2018contains\u2019). For example:<\/p>\n\n\n\n
var ordinaryFilter = [0,\u201dsolar\u201d, \u201ccontains\u201d];<\/pre>\n\n\n\n
This condition leaves only rows containing “solar” word in A column (0 index).
Array of ordinary filters is combined with AND operators:<\/p>\n\n\n\nvar condition1withAND = [ordinaryFilter1, ordinaryFilter2, ordinaryFilter3];<\/pre>\n\n\n\n
Array of different conditionswithAND is combined with OR operator:<\/p>\n\n\n\n
var complexfilter = [condition1withAND, condition2withAND, condition3withAND]<\/pre>\n\n\n\n
It means that returned data will meet condition1withAND<\/i> OR condition2withAND<\/i> OR condition3withAND<\/i>.
![\"2013-10-21_1744\"](\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/10\/2013-10-21_1744.png\")
<\/a><\/p>\n\n\n\nThus, we can build any complex condition. And there is another powerful option – limit source data by range.<\/p>\n\n\n\n
Source Data Range Limit<\/h2>\n\n\n\n
Next array limits source data by range:<\/p>\n\n\n\n
var sourceDataRangeLimit = [startRow, startColumn, numRows, numColumns];<\/pre>\n\n\n\n
Currently we support only one range limit.<\/p>\n\n\n\n
Two ways to copy prepared data to the target sheet<\/h2>\n\n\n\n
Filtered and limited data can be inserted (default):<\/p>\n\n\n\n
var isReplace = false;<\/pre>\n\n\n\n
or replaced:<\/p>\n\n\n\n
var isReplace = true;<\/pre>\n\n\n\n
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.<\/p>\n\n\n\nConclusion<\/h2>\n\n\n\n
We’ve described all the key points of our example and you can start playing with it<\/a>. In the next post I’m going to describe DeleteByRangeWithComplexFilter<\/i> function.<\/p>\n\n\n\n
Related Posts<\/h2>\n\n\n\n
- Intro to Google Application Scripts (GAS)<\/a><\/li>
- Useful Triggers in Google Application Scripts<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":54,"featured_media":9455,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[],"coauthors":["Oleg Shaydenko"],"class_list":["post-6022","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development"],"acf":[],"aioseo_notices":[],"categories_data":[{"name":"Engineering","link":"https:\/\/railsware.com\/blog?category=development"}],"post_thumbnails":"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/10\/2013-10-21_1311.png","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/6022","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/users\/54"}],"replies":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/comments?post=6022"}],"version-history":[{"count":114,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/6022\/revisions"}],"predecessor-version":[{"id":13983,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/6022\/revisions\/13983"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media\/9455"}],"wp:attachment":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media?parent=6022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/categories?post=6022"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/tags?post=6022"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/coauthors?post=6022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Useful Triggers in Google Application Scripts<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
- Intro to Google Application Scripts (GAS)<\/a><\/li>