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

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?

CodePudding user response：

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);
}

CodePudding user response：

I made it! I'm so proud! Thank you all for the help!

I'm beginning to do stuff like this!

After the Charles answer I looked for a way to add to each row. However, I know the code might not be too elegant, and I couldn't figured it out a way of doing using script itself, so I added a helper cell inside the sheet in H1 and H2.

Here's the solution:

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

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

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

  // Sets date at Column A and "Review" at Column B.
  var data = [];
  for(var i =0; i < copySheet.getRange("H1").getValue(); i  ) {
    data.push([Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'MMMM dd, yyyy'), 'Review']);
  }
  reviewRange = pasteSheet.getRange(pasteSheet.getLastRow()-copySheet.getRange("H2").getValue(), 1, copySheet.getRange("H1").getValue(), 2)
  reviewRange.setValues(data); //set the date and 'Review' data

}