Syntax<\/h3>\n
ARRAY_CONSTRAIN(input_range, num_rows, num_cols)<\/strong><\/span><\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate beyond importrange – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50462″ theme_builder_area=”post_content”] Automate data transfers beyond Importrange<\/span><\/p>\n Find out how<\/a>[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Examples always help us digest the concepts better. Following is a sample data set on which we will try the ARRAY_CONSTRAIN formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-1.png” alt=”ARRAY_CONSTRAIN formula 1″ title_text=”ARRAY_CONSTRAIN formula 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, we keyed in the formula in cell H1, as shown in the snapshot below.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-2.png” alt=”ARRAY_CONSTRAIN formula 2″ title_text=”ARRAY_CONSTRAIN formula 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The\u00a0<\/span>input_range<\/strong><\/span>\u00a0<\/span>is A1 The output data\u00a0<\/span> [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-3.png” alt=”ARRAY_CONSTRAIN formula 3″ title_text=”ARRAY_CONSTRAIN formula 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Suppose that we need only the Vegetables\u2019 data corresponding to Energy column only. Here\u2019s how we can get creative with the formula.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-4.png” alt=”ARRAY_CONSTRAIN formula 4″ title_text=”ARRAY_CONSTRAIN formula 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Unlike the previous example, we have entered the formula in H2 here. We considered only the data rows and not the header. The\u00a0<\/span>FILTER formula<\/a>\u00a0<\/span>evaluated the range for the first parameter, whereas the\u00a0<\/span>COUNTIF<\/a>\u00a0<\/span>formula determined the value for the second parameter.<\/p>\n To take this even further we can try this formula on the \u2018data import\u2019 related functions as well. For example, here is how a normal IMPORTDATA would have looked like.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-5.png” alt=”ARRAY_CONSTRAIN formula 5″ title_text=”ARRAY_CONSTRAIN formula 5″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the snapshot below, we embedded this within the ARRAY_CONSTRAIN formula. And, we constrained the data to 10 rows and 10 columns.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/ARRAY_CONSTRAIN-formula-6.png” alt=”ARRAY_CONSTRAIN formula 6″ title_text=”ARRAY_CONSTRAIN formula 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" In Google Sheets, the\u00a0ARRAY_CONSTRAIN\u00a0formula returns a subset of the input range, characterized by the specified number of rows and columns. In other words, the formula restricts or constraints the size of input range by the specified number of rows and columns. Syntax ARRAY_CONSTRAIN(input_range, num_rows, num_cols) input_range\u00a0\u2013 can either be an address reference to the range […]<\/p>\n","protected":false},"author":40,"featured_media":4016,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"In Google Sheets, the ARRAY_CONSTRAIN<\/a> formula returns a subset of the input range, characterized by the specified number of rows and columns. In other words, the formula restricts or constraints the size of input range by the specified number of rows and columns.\n\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Add-on-sm_sheets-connected-new-connection.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nUsage: ARRAY_CONSTRAIN Formula<\/h3>\n
Syntax<\/h3>\nARRAY_CONSTRAIN(input_range, num_rows, num_cols)<\/strong><\/span>\n
\n \t
Usage: ARRAY_CONSTRAIN Formula<\/h3>\nExamples always help us digest the concepts better. Following is a sample data set on which we will try the ARRAY_CONSTRAIN formula.\n\n
![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-36.png\")
\n\nNow, we keyed in the formula in cell H1, as shown in the snapshot below.\n\n![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-37.png\")
\n\nThe input_range<\/strong><\/span> is A1:G10, while the size defining parameters num_rows<\/strong><\/span> and num_columns<\/strong><\/span> are 7 and 4 respectively. We see that the output consists of 7 rows and 4 columns, exactly as we defined. It ignored the data falling beyond row 7 and column 4.\n\nThe output data flows from H1 towards right and further down. So, it is very important that we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error, as demonstrated in the screenshot below.\n\n![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-38.png\")
\n\nSuppose that we need only the Vegetables' data corresponding to Energy column only. Here's how we can get creative with the formula.\n\n![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-39.png\")
\n\nUnlike the previous example, we have entered the formula in H2 here. We considered only the data rows and not the header. The FILTER<\/a> formula evaluated the range for the first parameter, whereas the COUNTIF<\/a> formula determined the value for the second parameter.\n\nTo take this even further we can try this formula on the 'data import' related functions as well. For example, here is how a normal IMPORTDATA would have looked like.\n\n![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-40.png\")
\n\nIn the snapshot below, we embedded this within the ARRAY_CONSTRAIN formula. And, we constrained the data to 10 rows and 10 columns.\n\n![\"ARRAY_CONSTRAIN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-41.png\")
\n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3938","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3938","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=3938"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3938\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/4016"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=3938"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=3938"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=3938"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}