CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
weizer
weizer

Reputation: 1127

How to find the last row in column A and print the timestamp by using google apps script?

I want to print the timestamp in the last row of column A once I click a button so that I don't need to use the shortcut key CTRL + SHIFT + ; to generate the timestamp as shown in the screenshot below:

enter image description here

This is my script:

function myFunction() {
  const dt = Utilities.formatDate(new Date(), 'Asia/Singapore', 'HH:mm:ss');
  var ss = SpreadsheetApp.getActiveSheet ();
  var direction = SpreadsheetApp.Direction
  var aLast = ss.getRange("A"+(ss.getLastRow()+1)).getNextDataCell(direction.UP).getRow()
  var destRange = ss.getRange(aLast+1,1);

  destRange.setValue(dt)
}

I assigned the function to the button and run the script, but this is the error that show to me:

enter image description here

I'm still new to google apps script, may I know where did I did wrong in my script? Any advise will be appreciated!

Upvotes: 1

Views: 75

Answers (1)

Tanaike
Tanaike

Reputation: 201613

In your situation, I thought that the last row might be the same with the bottom row of the sheet. In this case, such error occurs. In your script, when the values of ss.getLastRow() and ss.getMaxRows() are the same, I think that the error occurs. If my understanding is correct, in order to avoid this, how about the following modification?

Modified script:

function myFunction() {
  const dt = Utilities.formatDate(new Date(), 'Asia/Singapore', 'HH:mm:ss');
  var ss = SpreadsheetApp.getActiveSheet();
  var direction = SpreadsheetApp.Direction

  // I modified below script.
  var lastRow = ss.getLastRow();
  var destRange;
  if (lastRow == ss.getMaxRows()) {
    destRange = ss.getRange("A" + (lastRow + 1));
  } else {
    var aLast = ss.getRange("A" + (lastRow + 1)).getNextDataCell(direction.UP).getRow();
    destRange = ss.getRange(aLast + 1, 1);
  }
  destRange.setValue(dt);
}

References:

Upvotes: 2

Related Questions