Google表格：所有表格中的onEdit功能都存在問題，但排除在外

代碼的目的是在表格底部（最后一行下方）移動已編輯的行（在 K 列中編輯）。我希望它在所有作業表（名稱未知）中作業，除了恒定的作業表（sheetsToExclude）。代碼按照我的意愿進行操作，但在所有作業表中，都忽略了排除。我對此非常基礎，我使用谷歌找到了這些代碼，只是通過反復試驗使它們作業，所以我想還有更多問題需要糾正。

function onEdit(e) {

  var allSheetTabs,i,L,thisSheet,thisSheetName,sheetsToExclude,value;

  sheetsToExclude = ['II Pó?rocze','I Pó?rocze','Kierowcy','Szablon','Instrukcja'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  allSheetTabs = ss.getSheets();

  L = allSheetTabs.length;

  for (i=0;i<L;i  ) {
    thisSheet = allSheetTabs[i];
    thisSheetName = thisSheet.getName();

    //continue to loop if this sheet is one to exclude
    if (sheetsToExclude.indexOf(thisSheetName) !== -1) {continue;}


  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  if(col == 11  && row > 1 && !as.getRange(row,col).getValue()=='') {  
    const row_new = as.getRange(row,1,1,col);
    row_new.copyTo(as.getRange(as.getLastRow() 1,1,1,col));
    as.deleteRow(row);
  }
  }}

uj5u.com熱心網友回復：

這對你有用嗎？

function onEdit(e) {

  if (!e.hasOwnProperty(`value`)) return

  if (e.range.getColumn() === 11 && e.range.getRow() > 1 && e.value.length) {

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    const excludeSheets = ['II Pó?rocze','I Pó?rocze','Kierowcy','Szablon','Instrukcja']

    const newRow = e.source.getActiveSheet()
                           .getRange(e.range.getRow(), 1, 1, 11)

    spreadsheet.getSheets()
               .filter(sheet => !excludeSheets.includes(sheet.getName()))
               .forEach(sheet => {

                 const destination = spreadsheet.getSheetByName(sheet.getName())

                 newRow.copyTo(destination.getRange(destination.getLastRow() 1, 1, 1, 11))
                
               })

    spreadsheet.getActiveSheet()
               .deleteRow(e.range.getRow())

  }

}

評論：

function onEdit(e) {

  // If edit does not contain a new value, exit.
  if (!e.hasOwnProperty(`value`)) return

  // If edit was made in Column 11 beyond Row 1, and value exists...
  if (e.range.getColumn() === 11 && e.range.getRow() > 1 && e.value.length) {

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    const excludeSheets = ['II Pó?rocze','I Pó?rocze','Kierowcy','Szablon','Instrukcja']

    // Get row range...
    const newRow = e.source.getActiveSheet()
                           .getRange(e.range.getRow(), 1, 1, 11)

    // Get all sheets...
    spreadsheet.getSheets()
               // But, keep only sheets that aren't in the exclusion array.
               .filter(sheet => !excludeSheets.includes(sheet.getName()))
               // For each of these sheets...
               .forEach(sheet => {
                 // Get the Sheet
                 const destination = spreadsheet.getSheetByName(sheet.getName())
                 // And "append" new row.
                 newRow.copyTo(destination.getRange(destination.getLastRow() 1, 1, 1, 11))
                
               })

    // Once complete, delete the row from the active sheet.
    spreadsheet.getActiveSheet()
               .deleteRow(e.range.getRow())

  }

}

標籤：谷歌应用脚​​本 排除

上一篇：在Google表格中添加一行（特定條件）

下一篇：在所選單元格之前添加行