Vlookup Script Fail

[Anna]

am having trouble generating the correct result using the script below

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)

I only seem to constantly generate the first value on the table (attached). For example, i try to look up Marc. Instead of 15, i end up with 40.

[Edward Ulle]

Your are setting r[0] to search value.  For logical comparison you need to use ==.

const matchRow = limitData .find(r => r[0] == searchValue)

[Anna]

Thank you! That did it

[Anna]

You've previously provided me with the script but I've since changed it so that instead of the initial calculation being on the script, the formula will just be on the actual file but would need the lookup value to trigger by each row only with the form submission so that the value previously entered will no longer changed with a new form submission. Currently, I am only generating results for the first row. How do I set it up so that it moves row to row with each form submission? How do I go about editing the script as well so that previous entries will no longer change. Eg. Casey's limit will be changed to 30, prev entries showing her limit of 20 should remain unchanged. Thank you!

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)

[Edward Ulle]

Sorry I don’t know what you are talking about. Can you show me the script you are referring to

[Anna]

Here's the initial script your shared

function onFormSubmit(event) {

  try {

    // event is the event object { range: {}, namedValues: {}, values: [], authMode: enum, triggerUid: uid }

    let parent = e.range.getSheet().getParent(); // parent spreadsheet

    let sheetB = parent.getSheetByName("Sheet B");

    // get some table of hours limit (total unknown to me what that is)

    let hoursLimt = parent.getSheetByName("Some sheet").getRange(1,1,10,1).getValues();

    // "respondant" is in column A of the hoursLimit table and event.values[0]

    // look up respondant in the lookup table

    let respondant = hoursLimit.find( row => row[0] === event.values[0] );

    if( respondant === undefined ) throw "Respondant ["+event.values[0]+"] not found";

    sheetB.appendRow(event.values);

    SpreadsheetApp.flush();  // insure new row has been added

    // assume respondent[1] is the limit (H) and event.values[3] (D) is the form value

    // getRange(sheetB.getLastRow(),7) is column G of the new row

    sheetB.getRange(sheetB.getLastRow(),7).setValue(respondant[1]-event.values[3]);

  }

  catch(err) {

    console.log(err);

  }

}

Is there any way I can modify the script that I currently have and set it up so the lookup value is triggered on each row only with the form submission so that the value previously entered will no longer changed with a new form submission

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)

Table.png