Add date and text at the last row after a copy paste

Question

I'm breaking my head here trying to figure it out a way to achieve this in a simples way.

I got working a code that copies a range from a sheet and paste in another at the last row.

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Review");
  var pasteSheet = ss.getSheetByName("Log");

  // get source range
  var source = copySheet.getRange(3,2,15,5);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,3,15,5);

  // copy values to destination range
  source.copyTo(destination, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}

The problem is, at the destination sheet, I still have columns A and B empty. By each row in the source (varies from time to time) copied, I need to place at column A todays date and column B the text "Review".

Like: Date (column A) | Revview (column B) | Pasted Data from Source (column C and other)

Do I need to write a completely new code to check for empty or can I implement both solutions into this code?

Answer 1

The code should fill the two cells before your data. It also caters for the setup where you have three areas in the same sheet.

Each copyInfoXd_button function will be assigned to your button image.

function copyInfo1d_button() {
  copyInfo(2, "B1:B");
}

function copyInfo7d_button() {
  copyInfo(9, "I1:I");
}

function copyInfo30d_button() {
  copyInfo(16, "P1:P");
}


function copyInfo(startingCol, subjectCol)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Review");
  var pasteSheet = ss.getSheetByName("Log");
  
  const sourceCols = 4; //actual number of columns to copy, not including the empty column H
  const destCols = 6; //actual number of columns to write to, from DATE to OBS
  
  //find the last row from a colum
  var avals = copySheet.getRange(subjectCol).getValues();
  var lastRow = avals.filter(String).length;
  
  var rowsToCopy = lastRow - 2; //skip the first 2 rows as they are headers

  // get source range
  var source = copySheet.getRange(3,startingCol,rowsToCopy,sourceCols); // row, column, number of rows and number of columns
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,rowsToCopy,destCols);

  var data = [];

  var sourceValues = source.getValues();

  //construct new data for the destination range
  for (var row in sourceValues) {
      var destRow = [Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'MMMM dd, yyyy'), 'Review'];
      destRow = destRow.concat(sourceValues[row]);
      data.push(destRow);
  }

  destination.setValues(data);
}