时间戳

首页 > 解决方案 > (javascript) 如何从单个单元格指向列中的所有单元格

javascript - (javascript) 如何从单个单元格指向列中的所有单元格

问题描述

我有这个简单的代码，当有人从下拉菜单（数据验证）中选择名称时要求输入密码我的问题是它只将它定向到单个单元格，我应该添加或做什么以使其指向列中的所有单元格? 请看下文。

// define each user password
const userPasswords = { 
  "Guilherme Cudessuata": "123",
  "Manuel Jose": "456",
  "Emaculada": "789",
  "Jonata Kedia": "111"
  }

function onEdit(e) {
  const namesCell = "E4" // the cell with the dropdown
  
  if (e.range.getA1Notation() != namesCell) {
    return
  }

  // show prompt for password:
  var ui = SpreadsheetApp.getUi()
  const response = ui.prompt("Password protected range", 
    "Please enter your password:",
    ui.ButtonSet.OK_CANCEL)

  const button = response.getSelectedButton()
  const text = response.getResponseText() 

  // don't run if the user pressed cancel or close
  if (button != ui.Button.OK) {
    return
  }

  // check the passwords
  if (text != userPasswords[e.value]) {
    ui.alert('Incorrect Password', 'Please try again', ui.ButtonSet.OK);
    e.range.setValue(e.oldValue) // reset to old value
  }
}

标签： javascriptgoogle-apps-scriptgoogle-sheets

解决方案

尝试这样的事情：

function onEdit(e) {

  const sheetName = "Sheet1" //change to suit
  const namesColumn = 5 // the column with the dropdown
  const startRow = 4 //change to suit
  const endRow = 100 //change to suit

  if (e.range.getSheet().getName() != sheetName || e.range.columnStart != namesColumn || e.range.rowStart < startRow || 
  e.range.rowEnd > endRow) {
    return;
  }

  //rest of your code
}

...看看这是否有帮助？

参考：

推荐阅读