I have a query gathering names from a master list and putting them onto a sheet. As it adds and takes away names from that sheet I wanted to add and take away datavalidation drop down boxes that go along with the names.
So I came up with the idea of finding the last row of the A column and the last row of the C column, because C is where the drop down box is and A is their name. And by getting the difference of these two columns and can determine not just the location but the magnitude of how much cells need to change.
A few things i found out was that Data validation lists aren't given with the first item, which is both good and bad. So I had to make a setValue function that sets a name on the "PLACE" the drop down boxes go, because without it, the last row of Column C would be considered less, because Data validation drop down boxes count as "".
This is why I need the difference in the two columns, so I could Add "PLACE" to the cells that are Validation boxes without replaces every single cell in Column C with the word "PLACE" Because it would happen every time the sheet was edited. I found some ways to find the last rows of individual columns I figured just columnA-ColumnC or so. Even made them in absolute. But there is one number I wasn't allowed to have them for a range or else it ruins the whole system. 0.
function setValue(cellName, value) {
SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value);
}
function onChange(e){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var FirstEmptyA = getFirstEmptyRow('A:A');
Logger.log(FirstEmptyA);
var FirstEmptyC = getFirstEmptyRow('C:C');
Logger.log(FirstEmptyC);
var dif = Math.abs(FirstEmptyA - FirstEmptyC);
Logger.log(dif);
var rangeA = ss.getRange(2,3,FirstEmptyA -1)
var rangedif =ss.getRange(FirstEmptyA+1,3,dif-1)
var EmptyArow =ss.getRange(FirstEmptyA,3)
var validationList = SpreadsheetApp.newDataValidation().requireValueInList(['PLACE','MALL','BANK','SCHOOL'], true).build()
if(FirstEmptyA - FirstEmptyC < 0){
rangedif.clearDataValidations();
rangedif.clearContent();
}
else if(FirstEmptyC - FirstEmptyA < 0) {
rangeA.setDataValidation(validationList);
rangedif.setValue('PLACE');
}
else if (FirstEmptyC-FirstEmptyA = 0){
EmptyArow.setDataValidation(validationList);
setValue('C' + FirstEmptyC,'PLACE');
}
}
// From answer https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRow(range) {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var column = spr.getRange(range);
var values = column.getValues();
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct);
}
I've been trying many different if else methods to no real help because i can't get a log whenever I use the function onChange in action. if I run onChange in the script editor everything is running fine, but if I edit anything in the sheet, it gives back the good ol' []
Things still happen, but the logs don't provide me with much info. I think it has to do with the cell I'm editing isn't in column C.
Other than that, when I do get it to do things, the placement of "PLACE" or datavalidation is usually a bit off the mark. Because I'm trying to compensate by adding or subtracting digits to make it not equal 0. Any idea how to overcome this issue?
EDIT 5/17/2019 GOT IT!
function onChange(e){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var FirstEmptyA = getFirstEmptyRow('A:A');
Logger.log(FirstEmptyA);
var FirstEmptyC = getFirstEmptyRow('C:C');
Logger.log(FirstEmptyC);
var dif = Math.abs(FirstEmptyA - FirstEmptyC);
Logger.log(dif);
var EmptyArow =ss.getRange(FirstEmptyA ,3)
var validationList = SpreadsheetApp.newDataValidation().requireValueInList(['OASIS','LAT','CQ','MDR'], true).build()
if(FirstEmptyA - FirstEmptyC < 0){
var rangedif =ss.getRange(FirstEmptyA+1,3,dif)
rangedif.clearDataValidations();
rangedif.clearContent();
}
else if(FirstEmptyC - FirstEmptyA < 0) {
var rangedif =ss.getRange(FirstEmptyC +1,3,dif)
rangedif.setDataValidation(validationList);
rangedif.setValue('OASIS');
}
}
function getFirstEmptyRow(range) {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var column = spr.getRange(range);
var values = column.getValues();
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct);
}
It was honestly just a matter of shifting the code around and adding numbers in certain areas. Getting empty of C instead of A for the second if else function.
