Google表格腳本自動運行腳本并在選單欄中添加功能-有解無憂

我使用一個腳本，允許我洗掉表格中間的空行，并不斷在底部添加行，以便我可以繼續填充它。一切都很完美。如果我在“C”列中洗掉或添加值，我只想讓腳本自動運行。而且，如果可能的話，使用 onOpen(e) 在我的選單欄中添加一個函式，以防腳本不執行而我必須手動運行它。這是我的床單：

我的床單

這是腳本：

function removeEmpty() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
  const empty_rows = [];
  const lastRow = sh.getLastRow()
  const data = sh.getRange("C6:G"   lastRow).getValues();
  for (var i in data) if (data[i].join('') == '') empty_rows.push( i   6);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));
  sh.insertRowsAfter(lastRow - empty_rows.length, 5)

  var rng = sh.getRange('A6:Z6')
  rng.copyTo(sh.getRange('A'   (lastRow - empty_rows.length   1)   ':Z'   (lastRow - empty_rows.length   5)), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  // H-I-J-K-L
  var rng = sh.getRange('H'   (lastRow - empty_rows.length)   ':L'   (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('H'   (lastRow - empty_rows.length   1)   ':L'   (lastRow - empty_rows.length   5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // O
  var rng = sh.getRange('O'   (lastRow - empty_rows.length)   ':O'   (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('O'   (lastRow - empty_rows.length   1)   ':O'   (lastRow - empty_rows.length   5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // Q-R-S-T-U
  var rng = sh.getRange('Q'   (lastRow - empty_rows.length)   ':U'   (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('Q'   (lastRow - empty_rows.length   1)   ':U'   (lastRow - empty_rows.length   5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // X-Y 
  var rng = sh.getRange('X'   (lastRow - empty_rows.length)   ':Y'   (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('X'   (lastRow - empty_rows.length   1)   ':Y'   (lastRow - empty_rows.length   5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

}

uj5u.com熱心網友回復：

試試這個

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  if (sh.getName() != 'Suivi Clients') return;
  var editRange = { 
    top: 6, 
    left: 3, 
    right: 7 
  };
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;
  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;
  removeEmpty()
}

和

function onOpen() {
  SpreadsheetApp.getUi().createMenu('? M E N U ?')
    .addItem('?? remove empty lines', 'removeEmpty')
    .addToUi();
  removeEmpty()
}

uj5u.com熱心網友回復：

我認為您的目標如下：

function onEdit(e) {
  const {range} = e
  /* Ensure that the edit occurs in the column 3 === C */
  if(range.getColumn() === 3 && range.getLastColumn() === 3){
    removeEmpty()
  }
}

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu().addItem('Remove Empty', 'removeEmpty').addToUi()
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/463300.html

標籤：谷歌应用脚本谷歌表格脚本自动运行

上一篇：LEFTOUTERJOIN2sheetusing1columnviaGoogleAppScript

下一篇：具體查找公式