Is this even possible with Google Sheets? I've looked around and found some explanations on how to accomplish this by using scripts. However, the issue remains because I'm unable to have my script recognize my sheet. Something with not able to recognize "SpreadSheetApp" within my code.

I basically have a table I'm attempting to reference using a dependant dropdown or rather a data validation list.

Once the Sales Rep is chosen from the first Data Validation cell then that configures the states that are for that sales rep in another data validation cell adjacent.

Data Validation of sales rep, then attempting to have the "State" column show a list option for only the states for that sales rep.

Here is an image of my table for the sales reps which are on another sheet. Table of sales reps with their assigned states.

Any help would be very appreciated.

btw here's the script I can't seem to get to work at all.

function myFunction() { 
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); 
  var list = ["a","b","c"]; 

  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build(); 

My script to build a the second data validation dropdown that would be dependant on the first(Sales Rep) column.

  • Welcome. Is this even possible with Google Sheets? Yes. I'm unable to have my script recognize my sheet. I'd say that the latter problem is the more important issue at the moment. Would you please share a copy of your spreadsheet. – Tedinoz Mar 10 '20 at 4:30
  • I actually cannot share a copy. However, I can mockup an example sheet. The error that it returns when I hit run/play is with the object "SpreadsheetApp". – handpaintedstudio Mar 10 '20 at 23:10
  • The error ... is with the object "SpreadsheetApp". Right, but you use that method twice. Once in var ws and again in var rule; so we need to know which one is causing the problem. FWIW (and no surprise) I can run your script with no problems. Do you have any other scripts and/or triggers; named ranges, anything that might upset the applecart. Of course, the most obvious thing is that you either don't have a sheet called "Data", or the spelling is not as it appears - maybe leading or trailing spaces? You could test by creating a new sheet and editing the script to apply to the new sheet. – Tedinoz Mar 11 '20 at 0:20
  • Something with not able to recognize "SpreadSheetApp" within my code. Please edit your question to include the entire error message. – Tedinoz Mar 12 '20 at 9:41
  • Did you resolve this? – Tedinoz Apr 5 '20 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.