行削除　＋　Ctrl + Alt + Shift + 1 → 上のセルに文章合成プロンプト

function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('カスタムメニュー');
menu.addItem('空白行削除', 'deleteRowsWithEmptyCellsInSelection');
menu.addItem('1つ上のセルに文章合成', 'mergeCellValueUp');
menu.addToUi();
}

function deleteRowsWithEmptyCellsInSelection() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getActiveRange();
const values = range.getValues();
const rowsToDelete = [];

for (let i = 0; i < values.length; i++) {
for (let j = 0; j < values[i].length; j++) {
if (values[i][j] === '' || values[i][j] === null) {
rowsToDelete.push(range.getRow() + i);
break;
}
}
}

if (rowsToDelete.length === 0) {
SpreadsheetApp.getUi().alert('選択範囲に空白セルを含む行はありません。');
return;
}

rowsToDelete.sort((a, b) => b - a);
rowsToDelete.forEach(row => sheet.deleteRow(row));
}

// 確認メッセージなしバージョンのmergeCellValueUp
function mergeCellValueUp() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getActiveRange();
const numRows = range.getNumRows();
const numCols = range.getNumColumns();

for (let r = 0; r < numRows; r++) {
for (let c = 0; c < numCols; c++) {
const currentRow = range.getRow() + r;
const currentCol = range.getColumn() + c;

if (currentRow === 1) continue;

const currentValue = sheet.getRange(currentRow, currentCol).getValue();
const upperCell = sheet.getRange(currentRow - 1, currentCol);
const upperValue = upperCell.getValue();

if (currentValue !== '') {
const newValue = upperValue ? upperValue + '\n' + currentValue : currentValue;
upperCell.setValue(newValue);
sheet.getRange(currentRow, currentCol).setValue('');
}
}
}
}