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 – Mielie Jonker Dec 5 '18 at 17:24
  • the Script is under sorting Hat – Mielie Jonker 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.

| improve this answer | |
  • I have changed w1 to 1W but now the script won't save. ReferenceError:"ActiveCell" is not defined. (line9,"Sorting Hat") – Mielie Jonker 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 – Mielie Jonker 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. – Mielie Jonker 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.