-1

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:

testHeader dataInserted
testA 9/8/2022
TestB 9/8/2022

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

testHeader dataInserted
testA 9/8/2022
TestB 9/8/2022
9/8/2022
9/8/2022
9/8/2022

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

silentninja89
  • 171
  • 1
  • 10

1 Answers1

2

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.

Argyll
  • 8,591
  • 4
  • 25
  • 46
  • it's still inserting data on columns that don't have data – silentninja89 Aug 09 '22 at 15:13
  • @silentninja89: please see edits – Argyll Aug 09 '22 at 15:34
  • @silentninja89: As well, you should clarify in your question how exactly you need to determine the cells you need to populate. Match non-empty cells to the column to the left? Match all cells up to the last cell with content to the column to the left? Populate up to the last occupied row of the whole sheet? etc etc etc – Argyll Aug 09 '22 at 15:40
  • sorry about that, I though that I was clear on my question (sorry English isn't my first language), but yeah the last row containing data in an specific column that I know will always have info in it. – silentninja89 Aug 09 '22 at 15:44
  • with the workaround that you linked it actually worked perfectly, thanks a lot. – silentninja89 Aug 09 '22 at 15:56