Myujth

Sample Sheet

I have sheet2 setup with a complete list of work orders in column A. In column B I have a simple marker of 'TRUE' to signify the work is done. In sheet1 I have a filter to only display orders that are not marked 'TRUE' in column B from sheet2. Next to the list of orders in sheet1 are checkboxes. I would like a script that can read when a checkbox is marked, read what the item is next to the checkbox, mark the checkbox 'FALSE' to clear it, then mark the appropriate row in column B from sheet2 as 'TRUE' so the item leaves the list on sheet1.

I'll set the script up using an 'On edit' trigger, but I need help with creating the script that can do those function.

after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.

function MarkComplete() 
 var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
 var Display = ss.getSheetByName("Display"); //Loading Display sheet
 var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
 var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
 var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2

 for(var i = 0; i<data1.length;i++)
 Logger.log('List item : ' + data1[i][0]);
 Logger.log('Checked : ' + data1[i][1]); 

 if(data1[i][1] == true) //Looking for rows in Display that are checked.
 Logger.log('TRUE : ' + data1[i][0]);

 for(var n = 0; n<data2.length;n++) 
 if(data2[n][0] == data1[i][0]) //Looking for rows in Complete that match checked rows in Display
 Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
 Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display