Hi, this is very interesting. However I ask for a range which is a column and I get a range of values in an array of an array. [[valrow1],[valrow2],[valrow3]] but I cannot find a way for the indexOf function to find my value. I used lookupvalue = "valrow3" or ["valrow3"] or "[valrow3]", none of them work. I am certainly not well versed in the syntax for array and I cannot find an answer. thanks for your help.
Trying to run the same code to find index of date but it never matches - checked both date formats are same and ran it through to check via if loop to verify both are same but doesnt work with while loop and index of it doesnt read it as same. Date format as shown in logger example - Sun Aug 29 14:30:00 GMT-4:00 2021
Question, I want to use the same idea but instead of search in what column, I need what row. the problem is that the values are arrays inside of singles arrays. I want to find the name: "John" and the ...getRange(2, 2, lr, 1).getValues(); --> is [ [Carlos], [Mike], [John], [Tom]... ] I do not know why when we look by columns the values are simple as an array inside of and array, any tricks? for now I a using Match function in the sheet and then I get the value to the scrip. Regards CW
Great video. I'm trying to modify this function to produce a row index but it doesn't show the range as CSV in the log, instead every cell data is in square brackets. Any ideas??
I have a question, why my indexOf(SearchElement) is not showing when I try to put period after lookUpRangeValues? I copied all you code even the names are the same.
So where is part 17? Trying to add this to an existing script so that the formula remains static even if columns are added/removed. Right now my script is based on column numbers, so if a column is added or removed, I lose my formula
Hi LCS, Thanks for this video. Can you please help me out with an issue I am facing? My range is a single column. when i am cheking the logs, I am getting only the 1st element of the column. var arr2 = ss.getRange(2,1,r-1,1).getValues()[0]; Logger.log(arr2); result = [Dog] (complete contents of the column -> Dog, Cat, Deer, Mouse, Dog) Thanks
.getValues()[0] gets you the first row in the column. You can search your column to find Deer like this. var arr2 = ss.getRange(2,1,r-1,1).getValues(); var deerPosition = arr2.findIndex(function(x){return x[0] == "Deer"}) + 1;
Thanks for another great video. This is going to sound silly, but how much changes for completing this down a column instead of across rows? Does the array return change format?
@@ExcelGoogleSheets Thanks. I knew it was a silly question. Also, figured it out watching your Arrays & Map Method video. Awesome series. Thanks so much for doing them!!
@@ExcelGoogleSheets Please pardon my lack of knowledge with this stuff. What I've been trying to do is match the text string of cell on one sheet(1) to a cell on another sheet (2). Have now been able to accomplish this via indexOf. Now, I'm trying use offset to uncheck a checkbox on the same row as my matched cell on sheet 2. Cannot, for the life of me, figure out how to use the indexOf position (happens to be 11) as a reference to do this. Do I need to convert the position to the actual value (text string) of the cell? What am I missing, or is there a completely different (and better) way of accomplishing this? Thanks in advance.
Hi! In the 11:00 minute the log returns the value 0.0. I can understand that why the lookupRangeValues.indexOf(lookupValue) returns something like false ( in this case 0.0), but than you add 1 to it , so why isn't it returns 1.0? Could you help me with that? Thank you for your answer in advance.
Hi, i ask you for help! first do you have any video where i can learn how to buil an script to replace text in column when it match with ALL cell content. I can't do it as the text is added (and not replaced)when i run my script twice using range.createTextFinder("cat").replaceAllWith("cat & dog"), even using replace(old,new). Thank you. And Also i use a separate sheet where one column have old values and side column new values, my goal is the system loop these column and the sheet where values are, but got errors too: function FindAndReplace4() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lrow = ss.getLastRow(); var rng = ss.getRange(2, 7, lrow - 1, 1); var data = rng.getValues(); //where listed categories var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('animalsToreplace'); var lrow1 = ss1.getLastRow(); var rng1 = ss1.getRange(2, 1, lrow1 - 1, 1); var data1 = rng1.getValues(); for (var i=0; i < data.length; i++) { for (var j=0; j < data1.length; j++) { if (data[i][0] == data1[j][0]) { data.setValue(data1[j][1]); /*} else if (data[i][0] == "y") { data.setValue("Yes"); }else{*/ } } } }
I'm trying to do this with rows, but i'm logging 0 each time. var lr = ss.getLastRow(); var lookupRowValues = ss.getRange(4,2,lr,1).getValues()[0]; var indexRow = lookupRowValues.indexOf("AH-POR-DIF-BLK") + 1; Logger.log(indexRow); what am I doing wrong?
rows return array of arrays. Watch my array series to understand what's happening and how to fix it. ua-cam.com/video/tZdSa6p3PHQ/v-deo.html&list=PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD
@@ExcelGoogleSheets Thank you so much. I got it cleared up! I'm a supply chain manager for a small company, and I've been able to automate so much of my work thanks to your videos!
The log says "Undefined". Can you help me what's wrong with my script, @Learn Google Spreadsheets? As note: I have "Sheet1" and "Sheet2", and the column "Alfa", "Beta", Gamma". Here is my script: function myFunction(){ var index = ColumnIndex("Sheet1","Beta"); Logger.log(index); } function ColumnIndex(sheetname,columnlabel){ var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); var lastColumnIndex = activeSheet.getLastColumn(); var headers = activeSheet.getRange(1, 1, 1, lastColumnIndex).getValues()[0]; var columnIndex = headers.indexOf(columnlabel)+1; }
Right now the lookupRangeValues is giving me an array of arrays but I want to search the array of arrays and return the array position I'm looking for. Is there a way to do that?
I'm not entirely sure what you're trying to do, but skillwise everything you need is in this video and series associated with it. ua-cam.com/video/gaC290XzPX4/v-deo.html
This script was as short as it was useful. I was wondering about this for quite a while.
Hi, this is very interesting. However I ask for a range which is a column and I get a range of values in an array of an array.
[[valrow1],[valrow2],[valrow3]] but I cannot find a way for the indexOf function to find my value.
I used lookupvalue = "valrow3" or ["valrow3"] or "[valrow3]", none of them work.
I am certainly not well versed in the syntax for array and I cannot find an answer.
thanks for your help.
Great vidéo !!
(sorry for my English....)
i'm wondering the same script for a row !!
u give me the road !!! yessss
another great video and great work
congrats
Welcome back :) and thanx for another great video
Trying to run the same code to find index of date but it never matches - checked both date formats are same and ran it through to check via if loop to verify both are same but doesnt work with while loop and index of it doesnt read it as same. Date format as shown in logger example - Sun Aug 29 14:30:00 GMT-4:00 2021
You are doing a great job. Keep it up and thank you.
Question, I want to use the same idea but instead of search in what column, I need what row. the problem is that the values are arrays inside of singles arrays. I want to find the name: "John" and the ...getRange(2, 2, lr, 1).getValues(); --> is [ [Carlos], [Mike], [John], [Tom]... ]
I do not know why when we look by columns the values are simple as an array inside of and array, any tricks?
for now I a using Match function in the sheet and then I get the value to the scrip.
Regards
CW
getRange(2, 2, lr, 1).getValues().map(function(d){ return d[0] });
Thanks for the push, I will try it now and I will Google it to know what that do " funtion(d)".
getRange(2, 2, lr, 1).getValues().map(function(value) {return '' + value + ''});
What would I do if I want to get a match that is not exact? Where the formula I would write would look like MATCH(value,array,-1)
thankz for this great video bro....
Great video. I'm trying to modify this function to produce a row index but it doesn't show the range as CSV in the log, instead every cell data is in square brackets. Any ideas??
Can we identify the columns if the labels are in different rows?
Great vidéo, thank you so much!
I have a question, why my indexOf(SearchElement) is not showing when I try to put period after lookUpRangeValues? I copied all you code even the names are the same.
I don't know. So long as the code works, it doesn't matter.
Learn Google Spreadsheets It is working now, I forgot the terminator at the top of my code. Thank you so much
How would I compare 2 list from different sheets and return the index?
So where is part 17? Trying to add this to an existing script so that the formula remains static even if columns are added/removed. Right now my script is based on column numbers, so if a column is added or removed, I lose my formula
Hi LCS, Thanks for this video. Can you please help me out with an issue I am facing? My range is a single column. when i am cheking the logs, I am getting only the 1st element of the column.
var arr2 = ss.getRange(2,1,r-1,1).getValues()[0];
Logger.log(arr2);
result = [Dog] (complete contents of the column -> Dog, Cat, Deer, Mouse, Dog)
Thanks
.getValues()[0] gets you the first row in the column.
You can search your column to find Deer like this.
var arr2 = ss.getRange(2,1,r-1,1).getValues();
var deerPosition = arr2.findIndex(function(x){return x[0] == "Deer"}) + 1;
Thanks for another great video. This is going to sound silly, but how much changes for completing this down a column instead of across rows? Does the array return change format?
The answer is here ua-cam.com/video/S5TbN36E8Uw/v-deo.html
@@ExcelGoogleSheets Thanks. I knew it was a silly question. Also, figured it out watching your Arrays & Map Method video. Awesome series. Thanks so much for doing them!!
@@ExcelGoogleSheets Please pardon my lack of knowledge with this stuff. What I've been trying to do is match the text string of cell on one sheet(1) to a cell on another sheet (2). Have now been able to accomplish this via indexOf. Now, I'm trying use offset to uncheck a checkbox on the same row as my matched cell on sheet 2. Cannot, for the life of me, figure out how to use the indexOf position (happens to be 11) as a reference to do this. Do I need to convert the position to the actual value (text string) of the cell? What am I missing, or is there a completely different (and better) way of accomplishing this? Thanks in advance.
Do you want to do this with a script or it doesn't matter?
@@ExcelGoogleSheets Script would be preferred, I guess, but not absolutely necessary.
How can I use this for a single column instead of a single row
🙏🏻
Hi! In the 11:00 minute the log returns the value 0.0.
I can understand that why the lookupRangeValues.indexOf(lookupValue) returns something like false ( in this case 0.0), but than you add 1 to it , so why isn't it returns 1.0?
Could you help me with that? Thank you for your answer in advance.
Watch this video ua-cam.com/video/S5TbN36E8Uw/v-deo.html
Dear sir how i reject duplicate entry from matching existing sheet value
Anybudy tells me which coding using in Google sheet i want to know all coding of spreadsheet where will i get it.
Hey compare find with array and textFinder please!
This doesn't seem to work if numbers are in the array? it returns an array of arrays.
Another thing, how do I do this so that it updates when the spreadsheet updates
convert array of arrays to regular array myArray = myArray.map(function(r){ return r[0] });
Hi, i ask you for help! first do you have any video where i can learn how to buil an script to replace text in column when it match with ALL cell content. I can't do it as the text is added (and not replaced)when i run my script twice using range.createTextFinder("cat").replaceAllWith("cat & dog"), even using replace(old,new). Thank you.
And Also i use a separate sheet where one column have old values and side column new values, my goal is the system loop these column and the sheet where values are, but got errors too:
function FindAndReplace4() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lrow = ss.getLastRow();
var rng = ss.getRange(2, 7, lrow - 1, 1);
var data = rng.getValues();
//where listed categories
var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('animalsToreplace');
var lrow1 = ss1.getLastRow();
var rng1 = ss1.getRange(2, 1, lrow1 - 1, 1);
var data1 = rng1.getValues();
for (var i=0; i < data.length; i++) {
for (var j=0; j < data1.length; j++) {
if (data[i][0] == data1[j][0]) {
data.setValue(data1[j][1]);
/*} else if (data[i][0] == "y") {
data.setValue("Yes");
}else{*/
}
}
}
}
range.createTextFinder("cat").matchEntireCell(true).replaceAllWith("cat & dog")
@@ExcelGoogleSheets thank you, very much!
I'm trying to do this with rows, but i'm logging 0 each time.
var lr = ss.getLastRow();
var lookupRowValues = ss.getRange(4,2,lr,1).getValues()[0];
var indexRow = lookupRowValues.indexOf("AH-POR-DIF-BLK") + 1;
Logger.log(indexRow);
what am I doing wrong?
rows return array of arrays. Watch my array series to understand what's happening and how to fix it. ua-cam.com/video/tZdSa6p3PHQ/v-deo.html&list=PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD
@@ExcelGoogleSheets Thank you so much. I got it cleared up! I'm a supply chain manager for a small company, and I've been able to automate so much of my work thanks to your videos!
What was the solution? This does not work for rows.
How to use vlookup function or index match in appscript
You can't use the same functions in Apps Script, you have to build your own.
The log says "Undefined". Can you help me what's wrong with my script, @Learn Google Spreadsheets?
As note: I have "Sheet1" and "Sheet2", and the column "Alfa", "Beta", Gamma".
Here is my script:
function myFunction(){
var index = ColumnIndex("Sheet1","Beta");
Logger.log(index);
}
function ColumnIndex(sheetname,columnlabel){
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
var lastColumnIndex = activeSheet.getLastColumn();
var headers = activeSheet.getRange(1, 1, 1, lastColumnIndex).getValues()[0];
var columnIndex = headers.indexOf(columnlabel)+1;
}
this line
var columnIndex = headers.indexOf(columnlabel)+1;
should be
return headers.indexOf(columnlabel)+1;
Thank you. It works now! :-D
What can I do if I want to find a value in a column instead of a row?
Right now the lookupRangeValues is giving me an array of arrays but I want to search the array of arrays and return the array position I'm looking for. Is there a way to do that?
convert array of array to an array. Something like this should do it mylist = mylist.map(function(r){ return r[0]; });
@@ExcelGoogleSheets Thank you so much! That did the trick.
Hi, i need your help please, i need to know, how can I get the numbre of not_Empty Rows with Apps script pleaaaaaaase ??
I'm not entirely sure what you're trying to do, but skillwise everything you need is in this video and series associated with it. ua-cam.com/video/gaC290XzPX4/v-deo.html
how can I execute script with a key like the enter ? Excelent ... God Bless You.
I don't think you can.