Google Appscript: Place text in the first empty row of a specific column

2
votes

I would like to place text inside the first empty row of a specific column. I've tried following the link Faster way to find the first empty row in a Google Sheet column and I believe that instead of using return I should use something different as I want to place text inside that column instead of going to that column.

I tried the code to place text but I can only do that for a specific row and column

var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange(1,3);
cell.setValue('hello');

How do I implement the above to the code below i.e. once it iterates finish, place text in the first empty row of column C

var sheet = SpreadsheetApp.getActiveSheet();
var column = sheet.getRange('C:C');//specific column
var values = column.getValues();//get all data in that column 

var row = 0;
for (var row=0; row<values.length; row++) {
  if (!values[row].join("")) break;
}
return ('hello');
2
google-apps-scriptgoogle-sheets

2 Answers

1
votes

How about this modification?

Modification points:

  • In your script, when the for loop is finished, row is the 1st empty row number. This value can be used like sheet.getRange(row + 1, 3).setValue('hello');.
  • When C:C is modified to 'C1:C' + sheet.getLastRow(), the search range can be reduced.

When above points are reflected to your script, it becomes as follows.

Modified script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = sheet.getRange('C1:C' + sheet.getLastRow());  // Modified
  var values = column.getValues();
  var row = 0;
  for (row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  sheet.getRange(row + 1, 3).setValue('hello'); // Added
}
  • When you run the modified script, hello is put to the column "C" of the 1st empty row in the active sheet.

Other pattern:

As other method, in your case, you might be able to use getNextDataCell as follows.

SpreadsheetApp
  .getActiveSheet()
  .getRange('C1')
  .getNextDataCell(SpreadsheetApp.Direction.DOWN)
  .offset(1, 0)
  .setValue('hello');

Note:

  • If you want to put the value to the last row of the column "C", please test the following script.

    var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(sheet.getLastRow() + 1, 3).setValue('hello');

References:

0
votes

I think You don't need to check the row whether empty or not in spreadsheet before storing

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(A:A).setValue("some_data");

by this using this you can simply store the value in first empty row in sheet. Please check and repond me after trying