Google Apps Script function to copy data a range into another location in the same sheet-CodePudding

THE GOOGLE SHEET LINK

My current code only copies the data (coming from the top and leftmost blue table) into a different sheet in the first available row, yet I want it to be copied into the same sheet ("V / MA") and starting at a certain row ("A63:J80") (also identified as the second bottom blue table).

function moveValuesOnly () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("V / MA!A4:J21");
  var destSheet = ss.getSheetByName("V / MA results");
   // Determine the location of the first empty row.
  var destRange = destSheet.getRange(destSheet.getLastRow() 1,1);
  source.copyTo (destRange, {contentsOnly: true});
  source.clear ();
}

and also another problem I encountered even with this current code, is that the borders get removed and the pasted data does not follow my expected formatting which is specified in the table the data gets pasted.

This script is supposed to get triggered at the click of a button I have already created. It copies data from a table ("A4:J21")(top and left-most blue table).

The two tables have identical formatting and borders, so please advise me on how I should change my code, and how I can stop the borders from getting cleared as well.

CodePudding user response：

I believe your goal is as follows.

From My current code only copies the data (coming from the top and leftmost blue table) into a different sheet in the first available row, yet I want it to be copied into the same sheet ("V / MA") and starting at a certain row ("A63:J80") (also identified as the second bottom blue table)., you want to copy the range of A4:J21 to A63:J80 in the same sheet of V / MA.
You don't want to clear the borders of cells.

In your situation, I thought that this sample script might be useful. This sample script can retrieve the 1st non-empty row from the bottom of the sheet. When this script is used for your script, it becomes as follows.

Modified script:

function moveValuesOnly() {
  // This sample script is from https://stackoverflow.com/a/44563639/7108653
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("V / MA");
  var source = sheet.getRange("A4:J21");
  var row = sheet.get1stNonEmptyRowFromBottom(1);
  var destRange = sheet.getRange(row   1, 1);
  source.copyTo(destRange, { contentsOnly: true });
  source.clearContent();
}

When this script is run, the range of A4:J21 is copied to A63:J80 in the same sheet of V / MA. And, the content of the range of A4:J21 is cleared.
In order to clear only the content, you can use clearContent(). When clear() is used, the borders are cleared. I thought that this might be the reason for your issue. You can also use clear({contentsOnly: true}) instead of clearContent().
When you want to run this script by the buttom, please assign the function name moveValuesOnly to the button.

Modified script:

References: