formulasgoogle sheets
So using the example data below, how can I prevent a duplicate entry with an informative message. The duplicate entry should be based on the row and not the whole sheet.
So, if I was to add a second column named Colour2, and I added 'Blue' for Bob. I want it to prevent the entry with a suitable message. But I dont want it to prevent the entry of 'Blue' for Alex as he hasnt chosen Blue yet.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
I don't think rowHeight can help you here, because it refers to the entire row, not to any particular cell in it. Sorting each column by RowHeight would just rearrange the rows without making your spreadsheet more compact.
Here is the script that sorts each column by the length of cell content, ascending. The first row (headers) is left in place; empty cells are ignored.
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var width = range.getWidth();
var height = range.getHeight();
var column, i, j, content;
for (j = 1; j <= width; j++) {
column = [];
for (i = 2; i <= height; i++) {
content = range.getCell(i,j).getValue();
if (content) {
column.push(content);
}
}
column.sort(function (a,b) {return a.length - b.length;});
for (i = 0; i < column.length; i++) {
range.getCell(i+2,j).setValue(column[i]);
}
for (i = column.length; i < height-1; i++) {
range.getCell(i+2,j).setValue("");
}
}
}
But in practice, this particular sort does not help all that much, because (as you noticed) the longer texts can still match up against shorter texts. Here is another sort, somewhat along the lines of what you mentioned: after sorting, each column is dropped down so that all the longest entries are in the same row. This will typically result in some empty cells at the top.
function myFunction2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var width = range.getWidth();
var height = range.getHeight();
var column, i, j, content;
for (j = 1; j <= width; j++) {
column = [];
for (i = 2; i <= height; i++) {
content = range.getCell(i,j).getValue();
if (content) {
column.push(content);
}
}
column.sort(function (a,b) {return a.length - b.length;});
for (i = 0; i < column.length; i++) {
range.getCell(height-column.length+i+1,j).setValue(column[i]);
}
for (i = 2; i <= height-column.length; i++) {
range.getCell(i,j).setValue("");
}
}
}
Best Answer
I have a feeling that the real-life implementation you have in mind will be more complex than the sample. But for your sample as given:
Add header "Colour2" into Column C.
Select cell C2.
Choose "Data" from the top menu, then choose "Data validation" from the drop-down menu.
When the Data Validation dialog opens, append ":C" to the end of the range showing in the top text box beside "Cell range:"
Click the drop-down next to "Criteria:" and choose "Custom formula is" from the bottom of that list.
In the textbox beside "Custom formula is," enter this formula:
=COUNTIF(B2:C2,B2)<2Beside "On invalid data:" check "Reject input."
Check the box beside "Appearance:", delete the technical language in that text box, and enter something that will serve as both a short preemptive tip and an explanation if a duplicate is entered (e.g., "Avoid duplicate entries").
Click the blue "Save" button.
Another "gentler" approach might be to use conditional formatting. It won't prevent duplicate input, but it will certainly let you know that you need to change duplicates to something else:
Add header "Colour2" into Column C.
Select cell C2.
Choose "Format" from the top menu, then choose "Conditional formatting" from the drop-down menu.
When the Conditional Formatting dialog opens, append ":C" to the end of the range showing in the text box below "Apply to range."
Click the drop-down below "Format cells if" and choose "Custom formula is" from the bottom of that list.
In the textbox below "Custom formula is," enter this formula:
=COUNTIF(B2:C2,B2)>1Click the mint green button that says "Default" under "Formatting style." Select the "123" in red font on a white background. Click the strikethrough button below that (the icon is a small "S" with a line through it).
Click the blue "Done" button, then close the Conditional Formatting dialog by clicking the "X" in the top right corner.
Now, any time a duplicate is entered, the text will turn red with a line through it — clear indication that it should be changed.