按LastRow运行触发器

Question

我已经花了好几天时间试图弄清楚如何编写所需的脚本。以下是我目前拥有的

function setUpTrigger(){
ScriptApp.newTrigger('LOOKUP')
.forForm('11jqQgzXiCT0XH8fAlfFS8y3_XgdY69L5QfoHAFwW0rk')
.onFormSubmit()
.create();
}
function LOOKUP() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const wsLogsheet = ss.getSheetByName ("Respondent Log")
const wsLimit = ss.getSheetByName("Limit")
const limitData=  wsLimit.getRange(2,1, wsLimit.getLastRow()-1,3).getValues()
const searchValue =  wsLogsheet .getRange("B2").getValue()
const matchRow = limitData .find(r => r[0] == searchValue)
const match = matchRow ? matchRow[2]: null
wsLogsheet.getRange("J2").setValue(match)

我想设置它，使它在每个新提交的最后一行(J列(上运行。之前的条目也将随每个触发器保持不变。这意味着，如果我更改查找数据上的值，以前的条目将不会受到影响。这是修改过的代码，但不会运行。

function setUpTrigger(){
ScriptApp.newTrigger('LOOKUP')
.forForm('11jqQgzXiCT0XH8fAlfFS8y3_XgdY69L5QfoHAFwW0rk')
.onFormSubmit()
.create();
}
function LOOKUP() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const wsLogsheet = ss.getSheetByName ("Respondent Log")
const wsLimit = ss.getSheetByName("Limit")
const limitData=  wsLimit.getRange(2,1, 
wsLimit.getLastRow()-1,3).getValues()
const searchValue =  wsLogsheet 
.getRange(wsLogsheet.getLastRow(),2).getValue();
const matchRow = limitData .find(r => r[0] == searchValue)
const match = matchRow ? matchRow[2]: null
wsLogsheet.getRange(wsLogsheet.getLastRow(),10).setValue(match); 

Answer 1

请尝试以下方式：

function LOOKUP(e) {
  const log = e.source.getSheetByName("Respondent Log")
  const lim = e.source.getSheetByName("Limit")
  const limvs = lim.getRange(2, 1, lim.getLastRow() - 1, 3).getValues()
  const searchValue = log.getRange("B2").getValue();
  const row = limvs.find(r => r[0] == searchValue)
  log.getRange("J2").setValue(row[2]);
}
function setUpTrigger() {
  ScriptApp.newTrigger('LOOKUP').forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create()
}