- Zero-based, no need to worry about Lbound as in VBA arrays.
- To access an element of an array use the square bracket syntax, e.g. array_var[numeric index] (definitely not () like VBA).
- Can grow and shrink as required. No Need for the tedious Redim Preserve statements as in VBA.
- Support a variety of methods that can be used to implement queues and stacks.
- Arrays play well with the Google Spreadsheet API where many methods accept arrays as arguments and return arrays.
- Enter some test data (anything you wish) into range “B1:M11” of Sheet 1. My demo used the month names Jan-Dec in row 1 with the numbers 1 to 10 beneath each month.
- Add a new sheet called “Sheet2”.
- Copy the following code into the script editor.
- Execute test_copyColumnNumbers.
- Check Sheet2 and see how the columns have been re-ordered in the output.
Notes On Code Above
- Re-ordering or selecting just a sub-set of columns in a spreadsheet is tedious. There is nothing equivalent to an SQL SELECT statement and repeated copy and paste is not very pleasant.
- The function copyColumnNumbers() above can be used to process an input range so that the output contains a subset and/or a re-ordering of the input columns.
- The function takes an input range and uses an input array of column indices, where the first column has index zero, and uses the offset() method of the output range argument to copy all values from specified columns.
- This function can also be used to re-order columns, this is also potentially useful functionality. In the test data used here, there were 12 input columns and the array of column indices to keep includes all the columns in reverse order. This produces output where the column order is the reverse of the input.
- The key range method used here is “getValues()”. This method always returns an array of arrays, even if it is invoked on a range object of just one cell!
- Once again, there are no checks or error trapping in the function copyColumnNumbers(). An exception is thrown if either of the range arguments is not a range object. However, if the columns_keep_num array contains a a column index that is greater than or equal to the input range column count, no exception is thrown, it simply results in a column with the value "undefined" which is the string representation of the undefined type. No "Subscript out of range" errors here as one would see when accessing a non-existent array element in VBA.