Google Sheets – Function to Copy Array

google sheetsgoogle-sheets-arrays

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently.

An example of when this is useful: you have a worksheet of raw data, and another worksheet with formulas applied to those values.

Doing it this way means that if a row is inserted in the first spreadsheet, it will be inserted in the second.

Best Answer

Short answer

Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter.

Long answer

An alternative to ARRAYFORMULA is the use of embedded arrays also named

inline arrays
literal arrays
array parameters
"your own arrays"

Example of embedded array of constants

={"A","B";1,2}

={A1:B2;D1:E2;G1:H2}

Example of embedded array of non-adjacent ranges.

It's worth to say that for spreadsheets with regional settings that use:

. as the decimal separator should use , as the column delimiter
, as the decimal separator should use \ as the column delimiter

In both cases use ; as the row delimiter.

Reference

Using arrays in Google Sheets - Docs editors Help

Related Solutions

Google Sheets – Equivalent of Excel CLEAN() Formula

As of 11-12-2013 the CLEAN() function is available in Google Spreadsheet:

https://support.google.com/docs/answer/3267340

I wasn't able to reproduce the results (now and back then, as did @Jonny on the 18th of April) but the CLEAN() function should remove the non-printable characters.

See following reference, on how to activate the new Google Spreadsheet:

https://support.google.com/drive/answer/3541068

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total