I believe your goal is as follows.
- When the checkbox of column "B" is checked, you want to copy the value of column "C" to the next row of the last row of column "A".
- From your showing script, you want to use `onEdit`.
In this case, how about the following modification?
### Modified script:
When `onEdit` is used, the event object can be used. In this modification, the event object is used.
```javascript
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 sheetName = "Research";
var { range } = e;
var sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.columnStart != 2 || !range.isChecked()) return;
range.offset(0, 1).copyTo(sheet.getRange(sheet.get1stNonEmptyRowFromBottom(1) + 1, 1), { contentsOnly: true });
}
```
- In this case, please check the checkbox of column "B". By this, the script is run. When you directly run this script, an error occurs. Please be careful about this.
### References:
- [Simple Triggers](https://developers.google.com/apps-script/guides/triggers)
- [Event Objects](https://developers.google.com/apps-script/guides/triggers/events)
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:
```javascript
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)](https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination,-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](https://gist.github.com/tanaikech/4892c97df7ac0504ffd715c2dd6cd546)
### 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)](https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination,-copypastetype,-transposed)
- [Event Objects](https://developers.google.com/apps-script/guides/triggers/events)
## 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:
```javascript
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);
}
```
