Timestamp for Google Spread Sheets(for only filled rows data)-CodePudding

In the spreadsheet, I want to add a timestamp for a pre-selected (E20: N48) row and column data. For example: If the E20: E22 cell of Sheet-1 is filled with some data, then the timestamp will be set from cell A2 to cell A4 in column 1 of Sheet-2. Similarly, the timestamp will be set for the data that will be up to the last row each time. As you can see in my code, 29 is given as the last row, due to which the first 2 rows are full of data but the timestamp is being set up to the remaining 28 rows including the first 2 rows.

Special Note: I only want to add timestamps for rows filled with data.

Here is my code:

function save (){

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sheet.getSheetByName('Sheet-1'); //sheetstore
  var data = sheet.getSheetByName('Sheet-2');
  var getValue = ss.getRange('E20:N48').getValues();
  var lastRow = data.getLastRow() 1;
  var updateTable = data.getRange(lastRow,2,29,10).setValues(getValue); //From column 2, total 29 column, total 10 rows

  //Timestamp for cloumn 1
  var timestamp = Utilities.formatDate(new Date(), "GMT 6:00", "yyyy-MM-dd HH:mm:ss");
  Logger.log(lastRow)
  var date = data.getRange(lastRow,1,29,1).setValue(timestamp); //From cloumn 1 to 29 rows, every time print in column 1
}

CodePudding user response：

This function guarantees that you only take rows that have data in every column so you can continue to put timestamps in column 1 of Sheet-2 since only rows that are completely filled are placed there.

function save() {
  const sss = SpreadsheetApp.getActive();
  const sh1 = sss.getSheetByName('Sheet-1'); 
  const sh2 = sss.getSheetByName('Sheet-2');
  const vs1 = sh1.getRange('E20: N48').getValues().filter(r => !r.some(e => !e));
  const lr = sh2.getLastRow()   1;
  const vs2 = sh2.getRange(lr, 2, vs1.length, vs1[0].length).setValues(vs1);
  const timestamp = Utilities.formatDate(new Date(), "GMT 6:00", "yyyy-MM-dd HH:mm:ss");
  sh2.getRange(lr, 1, vs1.length, 1).setValue(timestamp); 
}