Match Function with Google Sheets Apps Script -JavaScript IndexOf Method Tutorial - Part 16

[@davidduran2984]

This script was as short as it was useful. I was wondering about this for quite a while.

[@bh-db2rp]

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.

[@vlth]

Great vidéo !! (sorry for my English....) i'm wondering the same script for a row !! u give me the road !!! yessss

[@trajanoroberto120]

another great video and great work congrats

[@lazalazarevic6192]

Welcome back :) and thanx for another great video

[@rashidanwar7831]

You are doing a great job. Keep it up and thank you.

[@pan.sushant]

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

[@fantouch]

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

[@ExcelGoogleSheets]

getRange(2, 2, lr, 1).getValues().map(function(d){ return d[0] });

[@fantouch]

Thanks for the push, I will try it now and I will Google it to know what that do " funtion(d)".

[@davidramos5510]

getRange(2, 2, lr, 1).getValues().map(function(value) {return '' + value + ''});

[@yehudagoldberg8577]

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)

[@rakeshkumarrakee]

thankz for this great video bro....

[@markuswinter-cdps3008]

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]

The answer is here kzbin.info/www/bejne/iWa3k4Fpa6pri9k

[@markuswinter-cdps3008]

@@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!!

[@markuswinter-cdps3008]

@@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.

[@ExcelGoogleSheets]

Do you want to do this with a script or it doesn't matter?

[@markuswinter-cdps3008]

@@ExcelGoogleSheets Script would be preferred, I guess, but not absolutely necessary.

[@leemorris4054]

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??

[@danielromhany]

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.

[@ExcelGoogleSheets]

Watch this video kzbin.info/www/bejne/iWa3k4Fpa6pri9k

[@DavidMarcadet]

Great vidéo, thank you so much!

[@abhiskek270]

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

[@ExcelGoogleSheets]

.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;

[@chaitalikhona1617]

Can we identify the columns if the labels are in different rows?

[@walterelemino]

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.

[@ExcelGoogleSheets]

I don't know. So long as the code works, it doesn't matter.

[@walterelemino]

Learn Google Spreadsheets It is working now, I forgot the terminator at the top of my code. Thank you so much

[@ceeemmsss8149]

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

[@dewaynewest7756]

How would I compare 2 list from different sheets and return the index?

[@giamar3026]

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{*/ } } } }

[@ExcelGoogleSheets]

range.createTextFinder("cat").matchEntireCell(true).replaceAllWith("cat & dog")

[@giamar3026]

@@ExcelGoogleSheets thank you, very much!

[@JayNirmalkarx]

Dear sir how i reject duplicate entry from matching existing sheet value

[@shaitaan2011]

Anybudy tells me which coding using in Google sheet i want to know all coding of spreadsheet where will i get it.

[@Rolroorlo]

How can I use this for a single column instead of a single row

[@ericjohnson1144]

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?

[@ExcelGoogleSheets]

rows return array of arrays. Watch my array series to understand what's happening and how to fix it. kzbin.info/www/bejne/qovHhJRspZiDfrM&list=PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD

[@ericjohnson1144]

​@@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!

[@prodigroup]

What was the solution? This does not work for rows.

[@computergeeks7546]

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; }

[@ExcelGoogleSheets]

this line var columnIndex = headers.indexOf(columnlabel)+1; should be return headers.indexOf(columnlabel)+1;

[@computergeeks7546]

Thank you. It works now! :-D

[@maxieearle4364]

This doesn't seem to work if numbers are in the array? it returns an array of arrays.

[@maxieearle4364]

Another thing, how do I do this so that it updates when the spreadsheet updates

[@ExcelGoogleSheets]

convert array of arrays to regular array myArray = myArray.map(function(r){ return r[0] });

[@Realmattvevo]

Hey compare find with array and textFinder please!

[@Caldoyel]

What can I do if I want to find a value in a column instead of a row?

[@Caldoyel]

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?

[@ExcelGoogleSheets]

convert array of array to an array. Something like this should do it mylist = mylist.map(function(r){ return r[0]; });

[@Caldoyel]

@@ExcelGoogleSheets Thank you so much! That did the trick.

[@phoenixempire8886]

🙏🏻

[@vijaybabukommuri5125]

How to use vlookup function or index match in appscript

[@ExcelGoogleSheets]

You can't use the same functions in Apps Script, you have to build your own.

[@bouyya4085]

Hi, i need your help please, i need to know, how can I get the numbre of not_Empty Rows with Apps script pleaaaaaaase ??

[@ExcelGoogleSheets]

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. kzbin.info/www/bejne/nZKmY2xmjd-DjpY

[@CreemosEnJesucristo]

how can I execute script with a key like the enter ? Excelent ... God Bless You.

[@ExcelGoogleSheets]

I don't think you can.