Script inside Google Sheets for next available row

Question

I'm running this within a spreadsheet, ideally to put the values of today's date and then 'value1' into the next available row in the A and B column. It can't be to insert a new row, as I've got other columns set up with formulas to work off this:

function daily() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Name of Spreadsheet");
var date = Utilities.formatDate(new Date(), "GMT", "YYYY-MM-DD");
sh.appendRow;
sh.getRange("A8").setValue(date);
sh.getRange("B8").setValue('value1');
}

My main question is: how should I configure the sh.getRange("A8") line of code so that it really does:

sh.getRange("Next available A cell").setValue(date);
sh.getRange("Next available B cell").setValue('value1');

Right now I can only get it to write into specific cells, ie. A8, instead of the next row.

NaziA NaziA · Accepted Answer · 2021-01-15T15:14:17

From what I understand, you want to get the last row of A or B, not including the other columns? If so, then this should be it.

function daily() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Name of Spreadsheet");
  var date = Utilities.formatDate(new Date(), "GMT", "YYYY-MM-DD");
  var aValues = sh.getRange("A1:A").getValues();
  var aLast = aValues.filter(String).length;

  // Assuming A and B have same last row, no need for B
  // If different, need to calculate separately
  var bValues = sh.getRange("B1:B").getValues();
  var bLast = bValues.filter(String).length;
  
  // If A and B are the same, use setValues
  // sh.getRange(aLast + 1, 1, 1, 2).setValues([[date,'value1']]);
  
  // If A and B doesn't have the same last row, assign separately.
  sh.getRange('A' + (aLast + 1)).setValue(date);
  sh.getRange('B' + (bLast + 1)).setValue('value1');
}

Sample data:

Sample output:

Script inside Google Sheets for next available row

1 Answers