CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jose
Jose

Reputation: 1

How to create a button that writes the date at the first empty cell of column K

I have the physical button already done, but I don't know what to write in the script in order for it to type the date in the first empty cell of column K. Appreciate the help.

I recorded a macro, but it writes the date in the active cell

function Button() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getActiveRangeList().setValue(new Date());
};

I expect the output to be the current date and time reflected in the first empty cell of column K.

Upvotes: 0

Views: 50

Answers (2)

Cooper
Cooper

Reputation: 64042

If all columns do not contain data it could be different than the sheets lastRow + 1.

function Button() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(getColumnHeight(11) + 1, 11).setValue(new Date());
};

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  while(vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}

Upvotes: 0

J. G.
J. G.

Reputation: 1832

If you are looking for the last row of the sheet

function Button() {
var spreadsheet = SpreadsheetApp.getActive();
var last = spreadsheet.getLastRow();
spreadsheet.getRange(last, 11).setValue(new Date());
};

Upvotes: 2

Related Questions