So I have the following function in app scripts that's is inserting today's date into a new column:

function createTodayDate() {
  const ss1 = SpreadsheetApp.getActiveSpreadsheet();
  const date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy")
  ss1.getSheetByName('Sheet1').getRange('B:B').setValue(date)
  
}

However the insert is going all the going to the last row in the sheet no matter if it has data or not. So I wanted to know what to add the function mention above to get the code to do something like this:

Instead of inserting data all the way to the bottom that looks like this:

So basically I want the function to run until the last row that has data on it like the first table.

CodePudding user response：

If your column A is the longest column with content, then you can replace your last line with

var lastRowInd = ss1.getSheetByName('Sheet1').getLastRow();
ss1.getSheetByName('Sheet1').getRange(1,2,lastRowInd,1).setValue(date);

If you need to match the content of column A in particular, you will have to parse the content in column A. For example, you can use this answer if column A consists of a consecutive range of occupied cells and then completely empty cells. It depends on your exact application as to how to parse column A efficiently (and feasibly).

As well, it is probably more efficient to find the number of cells you need to populate using native Google Sheet formulas and then use Apps Script to read that result. Finding last cell of a column with content should be its own question. (And probably already answered.)

Consult the documentations on the relevant classes and methods for more info.

• sheet.getLastRow()

• sheet.getRange()