Dynamic dropdown data validation

[Juan Cedeno]

I have a Google Spreadsheet that so far has two sheets. One of them is the active sheet where I am working and it is called Week 2, but I don’t want to set this name because I plan to duplicate this sheet and keep all its capabilities. The other sheet is called Standards. In the sheet called Standards, column B contains a list of standards, and column A contains the name of the subject of each standard in column B, in the same row. On the sheet called Week 2 row 2 columns C to I, there are the names of the subjects that are contained in column A of the sheet called Standards. What I want to do is the following, I want to be able to, when I click on cells C5, C11, C17, C23, C29, D5, D11, D17, D23, D29, E5, E11, E17, E23, E29, F5, F11, F17, F23, F29, G5, G11, G17, G23, G29, H5, H11, H17, H23, H29, I5, I11, I17, I23, the script will read the content of the cell on row 2 of the corresponding column and get a pulldown menu containing the standards corresponding to the content of the cell on row 2 of the same column which we call Subject. So basically the script will be triggered by any of the cells I listed before, then it will read the content of the row 2 of the corresponding column, then it will go to the sheet called Standards, and filter the content of column B by the Subject which is the label contained in Standards column A and return a data validation pulldown menu on the cell that has been activated.

This is one of the versions of the script I have been working with for days, and I can't get it to work.

function onEdit(e) {

var activeSheet = e.source.getActiveSheet();

// Check if the edited cell is in Week 2 sheet and falls within specified range

if (activeSheet.getName() !== 'Week 2' || ![5,11,17,23,29].includes(e.range.getRow()) || !['C','D','E','F','G','H','I'].includes(e.range.getColumnLetter())) {

return;

}

// Get the corresponding subject for the edited cell from row 2 of same column

var subjectColumnIndex = e.range.getColumn();

var subjectsSheet = e.source.getSheetByName('Standards');

var selectedSubject = subjectsSheet.getRange(2 , subjectColumnIndex).getValue();

// Filter standards based on selected subject

var standardsData = subjectsSheet.getDataRange().getValues().filter(function(row){

return row[0] === selectedSubject;

});

// Extract standard values for dropdown menu options

var standardsList=[];

for (var i=0; i<standardsData.length; i++){

standardsList.push(standardsData[i][1]);

}

// Set data validation with created dropdown list only if there are any matching standards found

if (standardsList.length >0){

e.range.setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(standardsList).build());

} else {

e.range.clearDataValidations();

}

}

Could anyone help me with that?

[samuel gorgi]

hello all

Im tryin to send an email with certain data to every form submitter, I added a trigger to send the email every time someone submits a form but it sends the email to all submitters, even the previous ones, I want it to send the email only to the new submitter(last row)

Thanks in advance

this is the code I use

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()

var sheet1=ss.getSheetByName('Form responses 1');

var sheet2=ss.getSheetByName('message');

var subject = sheet2.getRange(2,1).getValue();

var n=sheet1.getLastRow();

for (var i = 2; i < n+1 ; i++ ) {

var emailAddress = sheet1.getRange(i,3).getValue();

var name=sheet1.getRange(i,4).getValue();

var ServiceAcquired=sheet1.getRange(i,1).getValue();

var message = sheet2.getRange(2,2).getValue();

message=message.replace("<name>",name).replace("<service>",ServiceAcquired);

MailApp.sendEmail(emailAddress, subject, message);

}

}

 

CONFIDENTIALITY NOTICE: The contents of this email message and any attachments are intended solely for the addressee(s) and may contain confidential and/or privileged information and may be legally protected from disclosure. If you are not the intended recipient of this message or their agent, or if this message has been addressed to you in error, please immediately alert Cicero District 99 by reply email, and then delete this message and any attachments. If you are not the intended recipient, you are hereby notified that any use, dissemination, copying, or storage of this message or its attachments is strictly prohibited.   

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/52a3d33f-c572-4e04-a2d3-835d14222542n%40googlegroups.com.

[Jon Couch]

Samuel, this may get a little confusing because you've added your question to someone else's question. But if your trigger is the form submit then you only need to execute your mail routine on the last row. You are currently looping through all the rows which is why everyone is getting another email. If you decide to use some other trigger where you can't rely on your target source as the last row, then you loop through all the rows like you have done, but you only send the email if Column E is blank. It appears you're adding a timestamp in Column E when you send your email. If not, that's what you need to do to be able to determine if an email has already gone out. I hope this helps, Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADo-STHWMgL-CcML%2BdDphjUKgQaLHaMktmPUN19avEQyQPhVsQ%40mail.gmail.com.

[Juan Cedeno]

I solved this with some workaround, but never got to decipher why the onEdit wasn't working properly whenever I tried to modify any of the cells I wanted to be watched by the script.