Badly need help on the script

[Anna]

I've already spent days trying to figure out how to write the script. Here's what  i currently have

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)

I want to set it up so that it runs on each lastrow with each new submission. The previous entries will also remain unchanged with each trigger. Meaning if I change the values on the lookup data, previous entries will not be affected. Thank you!

}

[Edward Ulle]

I'm not sure what your question is.  Instead of B2 and J2 you want it to be the last row?

const searchValue =  wsLogsheet .getRange(wsLogsheet.getLastRow(),2).getValue(); // ???

wsLogsheet.getRange(wsLogsheet.getLastRow(),10).setValue(match); // ???

[Anna]

Yes, I want the script to run on each new form submission only which would ideally be the last row. I've tried modifying it as well with lastrow but it does not return any values. The cell which I'm hoping to populate the looked up value remains blank

[Ron Meske]

How I have done something similar is to 

1. Attach a spreadsheet to a Form for the Form submissions.
   
2. Attach your script to the spreadsheet
3. Create the trigger for the spreadsheet using the event "On form submit"
4. As part of the event object you will receive the responses and they will be written as the last row of the sheet.

I use what is passed in the event object instead of reading from the sheet itself.

[Tin Balbalosa]

Thank you for your response. I've done it as such, the form and script does work if I've only set it up to as single reference. B2 and J2, but whenever I try to add last row trigger, it no longer works. I want it to trigger for every last row on the submitted response but I have difficulty doin so. Here's the last script it was modified to

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);

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/DNTzW1nJSKY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e64f3ba2-f897-41db-be13-ca6d4433617fn%40googlegroups.com.