Using Checkbox to Transpose Paste onto Target Range using Google Sheets

Question

The script inserted copies the values in range Sheet1!A5:F5 then transpose pastes into the first row and second column of Sheet2.

The script is able to copy and transpose paste under the last row of the second column on continuous use until it is reset.

.

Current Result Before Paste - Copies Range Sheet1 A5:F5

Current Result After Paste - Pastes to Range 2nd Column in Sheet2

.

.

.

My aim would be:

1. for the script to work with the checkbox in cell B2.
2. to create a target range for the values to be pasted to: Sheet2! B7:B - hopefully still allowing the transpose paste to function through getLastRow()+1.

Expected Result Before Paste - Checkbox = True, Copying Range Sheet1 A5:F5

Expected Result After Paste - Paste to Range Sheet2 B7:B

Have tried inserting:

if (sheet.getSheetName() != "*DuplioMltSlds" || range.getA1Notation() != "D13" || !range.isChecked()) return;

as well as changing the function to onEdit. Have also tried various var row = ss.getRange("Sheet2!B7:B") options, however, have not been able find a suitable result.

Sample Sheet

Script source and script:

function copyTransposeAndDelete () {

  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange("Sheet1!A5:F5");
  var destSheet = ss.getSheetByName("Sheet2");
  var destRange = destSheet.getRange(destSheet.getLastRow()+1, 2, source.getWidth(), source.getHeight());
  destRange.setValues(transpose(source.getValues()));
}

function transpose(array) {
  return array[0].map(function(row, i) { 
    return array.map(function(col) { 
      return col[i]; 
    });
  });
}

Answer 1

I believe your goal is as follows.

• You want to copy and paste the cells "A5:F5" of "Sheet1" to the cell "B2" of "Sheet2". In this case, you want to copy the values by transposing.
• When the values are copied again, you want to paste the transposed values to (the last row + 1) of column "B" of "Sheet2".
• You want to run the script when the checkbox of cell "B2" of "Sheet1" is checked.

In this case, how about the following modification?

Modified script:

function onEdit(e) {
  // Ref: https://stackoverflow.com/a/44563639
  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 { source, range } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "B2" || !range.isChecked()) return;
  var srcRange = sheet.getRange("A5:F5");
  var dstSheet = source.getSheetByName("Sheet2")
  var row = dstSheet.get1stNonEmptyRowFromBottom(2) + 1;
  var dstRange = dstSheet.getRange("B" + (row < 7 ? 7 : row));
  srcRange.copyTo(dstRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
}

• In your situation, I thought that copyTo(destination, copyPasteType, transposed) might be suitable. Because, in this method, the values can be copied by transposing.
• When the OnEdit trigger is used, you can use the event object. By this, the process cost will become low a little. Ref

Note:

• When you use this script, please check the checkbox of "B2" of "Sheet1". By this, the script is run. When you directly run the script with the script editor, an error like TypeError: Cannot destructure property 'source' of 'e' as it is undefined. occurs. Please be careful about this.

References:

• copyTo(destination, copyPasteType, transposed)
• Event Objects

Added:

If you want to put the values to the column "B" of "Sheet2" even when the column "B" of "Sheet2" has already had the values after row 7, how about the following sample script?

Sample script:

function onEdit(e) {
  // Ref: https://stackoverflow.com/a/44563639/7108653
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 7) {
    const range = this.getRange(offsetRow, columnNumber, 2);
    const values = range.getDisplayValues();
    if (values[0][0] && values[1][0]) {
      return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
    } else if (values[0][0] && !values[1][0]) {
      return offsetRow + 1;
    }
    return offsetRow;
  };

  var { source, range } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "B2" || !range.isChecked()) return;
  var srcRange = sheet.getRange("A5:F5");
  var dstSheet = source.getSheetByName("Sheet2")
  var row = dstSheet.get1stEmptyRowFromTop(2);
  var dstRange = dstSheet.getRange("B" + row);
  srcRange.copyTo(dstRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
}

Answer 2

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1")
  const vs  = sh.getRange("A4:F4").getValues().flat().map(e => [e]);
  const dsh = ss.getSheetByName("Sheet2");
  dsh.getRange(7,2,vs.length,vs[0].length).setValues(vs)
}