How can I store a range of cells to an array?

Question

If I have a list of data in cells A1:A150 (but the amount can vary), is there a way to push that into an array without looking at each cell individually to determine if it is empty? I exceed my execution time by doing this and I need a faster way to store the data and stop when it hits an empty cell.

Below is how I currently do it:

for (var i = 1; i < 500; i++) {
  if(datasheet.getRange("A" + i).getValue() == ""){
   break;   
  }

  else{
     addedlist_old.push(datasheet.getRange("A" + i).getValue())
    }

Answer 1

If you don't have empty cells in between it's actually pretty easy.

var lastRow = sheet.getLastRow();
var array = sheet.getRange('A1:A' + lastRow).getValues();

If you need to weed out empty entries after that, you can use a for statement, or to be faster, filter like an earlier answer shows.

var filteredArray = array.filter(function(n){ return n != '' });

The main difference between this answer and the one posted earlier that I mentioned is that getValues() will give you an array.

I've tested this and it works in google apps script, and it does not time out when I use the array, or even when I put in large amounts of data (I tested it with an array that has about 20-50 characters per entry and about 500 entries). Just make sure to define the var sheet or put in your own variable.

Answer 2

Try this:

    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    var lastRow = sheet.getLastRow();

    var data = sheet.getRange(1, 1, lastRow, 1).getValues(); //getRange(starting Row, starting column, number of rows, number of columns)

    for(var i=0;i<(lastRow-1);i++)
    {
      Logger.log(data[0][i]);
    }

the variable data stores all the cells of column A.

Cell A1 is stored in data[0][0], cell A2 is stored in data[0][1], cell A3 is stored in data[0][2] and so on.

The getRange(starting Row, starting column, number of rows, number of columns) is a batch operation so it is much faster when you have a large dataset.

Answer 3

If you're using only one column, I'd suggest:

  // my2DArrayFromRng = sh.getRange("A2:A10").getValues();
  var my2DArrayFromRng = [["A2"],["A3"],["A4"],["A5"],[],[],["A8"],["A9"],[]];
  var a = my2DArrayFromRng.join().split(',').filter(Boolean);

The methods .join() and .split(',') together convert the 2D array to a plain array (["A2","A3","A4","A5",,,"A8","A9",]). Then the method .filter(Boolean) strips the empty elements. The code above returns [A2, A3, A4, A5, A8, A9].

Answer 4

Try this:

It will allow you to select any column on the sheet.

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {

  ui.createMenu('Sheet Functions')
 .addItem('Get values from column', 'getVals')
 .addToUi();    
 }

function getVals() {
var sheet = ss.getActiveSheet();
var getColumnLetter = ui.prompt('Select column..' , 'Enter the letter of the target column..', ui.ButtonSet.OK_CANCEL);
  if(getColumnLetter.getSelectedButton() == ui.Button.CANCEL) { 
  return } else {
  getColumnLetter = getColumnLetter.getResponseText().toUpperCase();
  }

var columnNo = getColumnLetter.charCodeAt(0) - 64;

try { var data = sheet.getRange(1, columnNo, sheet.getMaxRows()).getValues().filter(String); } catch (e) { ui.alert('Invalid input please try again.', ui.ButtonSet.OK); return;}

/*
*
* Do what ever you need to do down here
*
*/
}