google apps script - How to copy column to the first blank cell of another column?

Question

I have some values in column A that I want to copy the first empty cell of column B.

I managed to get the first part of the problem:

function CopyValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("MySheet!A4:A10");

  source.copyTo (ss.getRange ("MySheet!B32"), {contentsOnly: true});

}

where B32 is the first empty cell in column B.

This works perfectly but of course B32 will not always be first empty cell and i need to get that first empty cell dynamically. I tried a few things by merging other scripts found online, but no success so far...

Any help would be greatly appreciated! Thank you!

Answer 1

Here's a function that will return the first empty cell (as a string eg. 'B32') in a given column and sheet:

function getFirstEmptyCellInCol(sheet, column) {
  var values = sheet.getRange(column + ':' + column).getValues(); // get all the data of the column
  var rowCount = 0;
  while ( values[rowCount] && values[rowCount][0] != "" ) {
    rowCount++;
  }
  return column + parseInt(rowCount+1).toString();
}

You can call it like this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MySheet");
emptyCell = getFirstEmptyCellInCol(sheet,'B');

With your code:

function CopyValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MySheet");
  var emptyCell = getFirstEmptyCellInCol(sheet,'B');
  var source = sheet.getRange ("A4:A10");
  source.copyTo (sheet.getRange(emptyCell), {contentsOnly: true});
}