Google Spreadsheet API - find the first blank cell in a column?

Question

Is there a good way to get the first blank cell in a column from a Google spreadsheet service through Java?

I know I can use:

public CellFeed CheckColumn(int row, int col) 
    throws IOException, ServiceException {
    CellQuery query = new CellQuery(cellFeedUrl);
    query.setMinimumRow(row);
    query.setMaximumRow(row);
    query.setMinimumCol(col);
    query.setMaximumCol(col);
    query.setReturnEmpty(true);

    CellFeed feed = service.query(query, CellFeed.class);
    int cell_loc[];

    for (CellEntry entry : feed.getEntries()) {
       cell_loc=CheckIfEmpty(entry);   
    }
    return cell_loc;
}

And go through the entries, but I would rather not load the entire column at a time, it is slow for my users, and it seems like just going through the entire column

Any thoughts?

Answer 1

This small snippet will create a function in Google Spreadsheet using Google Apps Script:

function emptySpace(array) {
  // set counter
  var counter = 0;

  // itterate through values
  for (i in array){
    if (array[i].length > 1) {
      throw ("Only single column of data");
    } else {
      if(array[i][0] != "") {
        counter++;
      } else {
        break;
      }
    }
  }

  // return value + 1 
  return counter + 1;
}

Add this script through the script editor in your spreadsheet, and emptySpace feature is available on all of the worksheet, for example: =emptySpace(A1:A7).

. , :

Answer 2

- Google Appscript, , Tools → script editor:

function myFunction()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR_SHEET_NAME");  //get your active sheet for various operations
  var data = sheet.getDataRange().getValues();  //whole sheet as 2D array

  for(var i=0; i<data.length; i++)
  {
    if(data[i][YOUR_COLUMN].trim() == "")  //trim() is used to remove blank spaces
    {
      Logger.log(i+1);  //Returns empty first empty cell row #
      break;
    }
  }
}

View- > logs ctrl + enter , :

sheet.getRange("S8").setValue(i+1);