I have created a progress tracker for the Academy Trust I work in. The trust at the moment consist of 4 schools. Students move between the schools as the progress and between classes during the year depending on their abilities and needs. when a student move his/her tracking data needs to move to the correct class because teachers does not want to search.

My idea is to just change the class and then the students whole data row should move automatically to the correct class on another sheet.

The first thing I need to do is to be able to select the class from a drop down list but the list must be linked to the school. So if I set the school to "School A" then only School A's classes should be visible. The same for school B,C and D.

The Script I tried is below. It is not working and due to the fact that I do not know anything about Scripts I do not know how to fix this problem. I got the Script from Youtube and tried to edit it to fit my workbook.

function onEdit(){
  var tabLists = "lists";
  var tabValidation = "w1";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("lists")

  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 1 && ActiveCell.getRow() > 1 && ss.getSheetName() == tabValidation){

    var Sites = datass.getRange(1,6,datass.getLastColumn()).getvalue();

    var makeIndex = makes[0].indexOf(activeCell.getValue()) +1;
    if(makeindex != 0){

        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);

  • What Youtube video? Do you modified something of the original script? Where do you get stuck? Did you get an error message?
    – Rubén
    Dec 5 '18 at 17:20
  • 1
  • I tried to change the names of the sheets but when I run the Script the Logs does not pick up anything Dec 5 '18 at 17:24
  • the Script is under sorting Hat Dec 5 '18 at 17:26
  • What is "sorting Hat"? Please edit the question to add all the relevant details to it. The video title says that it's about dependent drop-downs, that isn't conditional formatting (change cell background/fonts ).
    – Rubén
    Dec 5 '18 at 17:30

One of the errors, var tabValidation = "w1"; is that the spreadsheet doesn't contain a sheet named w1 but there is one named 1W.

Other errors, maybe a typos, are

  • ActiveCell.getRow().
  • datass.getRange(1,6,datass.getLastColumn()).getvalue()

Bear in mind that Google Apps Script / JavaScript spelling is important, in other words, w is not the same than W, ActiveCell is not the same as activeCell and getvalue is not the same as getValue.

Please checkout https://developers.google.com/apps-script/guides/support/troubleshooting to learn some tools and techniques to find the above problems.

  • I have changed w1 to 1W but now the script won't save. ReferenceError:"ActiveCell" is not defined. (line9,"Sorting Hat") Dec 5 '18 at 18:53
  • @MielieJonker I already mentioned ActiveCell <- The correct spelling is activeCell.
    – Rubén
    Dec 5 '18 at 18:54
  • I fixed all the spelling errors you have mentioned. The script saves now but does not work on the workbook Dec 5 '18 at 19:00
  • @MielieJonker Have you already read developers.google.com/apps-script/guides/support/… ?
    – Rubén
    Dec 5 '18 at 19:02
  • No I am busy with that. Dec 5 '18 at 19:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.