{"id":863,"date":"2012-06-08T05:34:27","date_gmt":"2012-06-08T11:34:27","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=863"},"modified":"2012-05-08T05:42:27","modified_gmt":"2012-05-08T11:42:27","slug":"iterating-over-a-range-in-google-spreadsheets","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/863","title":{"rendered":"Iterating over a range in Google Spreadsheets"},"content":{"rendered":"<p>Using the macro facility in Google Spreadsheets can be quite powerful, but I&#8217;ve found the documentation difficult to navigate.<\/p>\n<p>For future reference, here is how to iterate over a range of cells that has been predefined (using a <a href=\"http:\/\/support.google.com\/docs\/bin\/answer.py?hl=en&amp;answer=63175\">named range<\/a> or in some other manner).<br \/>\n<code><br \/>\nvar rangeName = 'mailingdate';<br \/>\nvar range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(mailingDateRangeName);<br \/>\nvar arr = mailingDateRange.getValues();<br \/>\nfor (var i = 0; i &lt; arr.length; i++) {<br \/>\nvar rowVal = arr[i][0];<br \/>\nif (rowVal.length &lt; 2) { \/\/ 2 because of spaces<br \/>\ncontinue;<br \/>\n}<br \/>\n\/\/... process non empty rows.<br \/>\n}<br \/>\n<\/code><\/p>\n<p>Note that this only processes a range with one column (hence the <code>arr[i][0]<\/code>. Ranges with multiple columns are left as an exercise for the reader.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Using the macro facility in Google Spreadsheets can be quite powerful, but I&#8217;ve found the documentation difficult to navigate. For future reference, here is how to iterate over a range [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53],"tags":[],"class_list":["post-863","post","type-post","status-publish","format-standard","hentry","category-google-apps"],"_links":{"self":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/863","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/comments?post=863"}],"version-history":[{"count":1,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/863\/revisions"}],"predecessor-version":[{"id":864,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/863\/revisions\/864"}],"wp:attachment":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/media?parent=863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/categories?post=863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/tags?post=863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}