this is amazing, thank you. So well explained and easy to follow for noobs like me. And I can see you're responding to people too - really helpful! How do you ignore a blank row please? my spreadsheet is active so new rows added all the time, I'd like to run the function on rows containing data only.
Exactly what I was searching today , then just fond that you uploaded this video one day before. Great video, Keep it up. I am learning a lot from you :)
This is excellent, thank you. I'm wondering how to do this when the sheet doesn't know that it's Column H it needs to go into. Instead, to find that it's Column H and them proceed from there?
see below; the example retrieves 2 columns. You just need to make sure that the number of null statements is the same as the number of columns your getting and then for your set values you need to make sure that the number of columns matches. const matchSku = searchValues.map(searchRow => { const matchRow = srcdata.find(r => r[0] == searchRow[0]) {return matchRow ? [matchRow[4],matchRow[0]] : [[null],[null]]} })
My question is that in VBA we can utilize Worksheet fomulas in script e.g. we can write down Vlookup in Script and get the values in script, and it is far more easier to get things through scripts. Is there anyway one can get such support in Google sheet, since sheet formulas are also part of Google sheet
why i cant run setvalues from the result of array that have one row three column ? instead my getrange for paste it has 1 row 3 column please answer teacher.
Thank you so much. It work, but my data are transpossed. May you please advice how this loop should look like if Sales Date would be transposed - data from Sales Rep (col C) would be places in columns from B1 to DF1 (for eg.110 positions instead of 2041) and matched values should be returned to B10 to DF10 ( or at best to first empty row ).
Finally figured it out. Below gets to columns back. const matchSku = searchValues.map(searchRow => { const matchRow = srcdata.find(r => r[0] == searchRow[0]) {return matchRow ? [matchRow[4],matchRow[0]] : [[null],[null]]} }) and for the last line edit make sure that your .setValues(matchSku) is as wide as the range your getting
Hi, thanks for the video, very useful, what happens in case of duplicate values? I assume it takes all the values, but how can I modify the code to make the match not just one column but two columns? EX: Take all the values of column number 4 if NAME = ISABELLA and SURNAME = BRANDON? Thank you
thank you so much! this will help me at work doing lookups. is there a way to do a lookup not for the entire value but say a text string (return all values that match string "ali"?
Hello, very nice How to, and thank for help the comuunity :) But i don't have a find method, it's possible to share you'r library or what is you'r video to explain it ? :D Thk
This is so awesome and almost similar to what I've been struggling to do without success... Instead of populating the rate in all corresponding rows, how (which is what I'm trying to do) will you code it if it's a dropdownList in Sales Data sheet? I succeeded to modify your code to getValue from a dropdown selection and it works when I run the function from the script editor; but the onEdit fonction won't populate the corresponding value on dropdown selection... If you can please help with this... Again, thx...
I'm not sure where you're getting your data. For example onEdit simple trigger doesn't allow data coming from a different spreadsheet. Otherwise it should work if there are no restrictions. What's in your logs?
How can i return nothing insteand of "null" with the match function ? By "nothing" i mean => If you don't have any match jump to the next one (don't earase data already in the cell). With your method the script remove data and replace with "blank cell". How can i change this to keep my data already in cell ?
@@ExcelGoogleSheets const ratesData = wsRates.getRange(9, 1, wsRates.getLastRow() - 1, 12).getValues(); const searchValues = wsSalesData.getRange("C2:C2041").getValues(); const matchingRates= searchValues.map( searchRow => { const matchRow = ratesData.find(r => r[0] == searchRow[0]) return match = matchRow ? [matchRow[7]] : [null] }); console.log(matchinRates) wsSalesData.getRange("O2:O2041").setValues(matchinRates) So my problem is on this line " return match = matchRow ? [matchRow[7]] : [null]" > If the match isn't find i don't want null that erase my datas. I want to keep it but i don't know if there is a simple way to do it or not. Also how can i setup a dynamical Rage for those : wsSalesData.getRange("O2:O2041") wsSalesData.getRange ("C2:C2041") (i don't want to run all rows every time the script run but only those where there is data)
@@ExcelGoogleSheets I have the same question, hope you can help. In the line const match = matchRow ? matchRow[2] : null , what do I need to change to keep the current cell value and not change it to null if no match is found. Thanks!
Same question as well. I don't want it to overwrite the data currently in the cell, here is the code similar to yours: const ss = SpreadsheetApp.getActiveSpreadsheet(); const wsPaymentData = ss.getSheetByName("Payments") const wsApprovedApps = ss.getSheetByName("Test") const paymentData = wsPaymentData.getRange(2,1,wsPaymentData.getLastRow()-1,4).getValues(); const searchValues = wsApprovedApps.getRange("C2:C1990").getValues(); const matchingPayments = searchValues.map(searchRow => { const matchrow = paymentData.find(r => r[0] == searchRow[0]) return matchrow ? [matchrow[1]] : [null] }) //console.log(matchingPayments) wsApprovedApps.getRange("J2:J1990").setValues(matchingPayments)
@@johnstevens4211 Figured it out!!! You have to merge the results from the existing data and the data your filtering. I created a const called existingpayment which gets the entry for that particular column on the original data. During the filter if no match is found instead return "null" return the existing value (in my case existingpayment). Hope this helps! function matchPayment(){ //Get data from each sheet const ss = SpreadsheetApp.getActiveSpreadsheet(); const wsPaymentData = ss.getSheetByName("Payments") const wsApprovedApps = ss.getSheetByName("Approved Applications") const paymentData = wsPaymentData.getRange(2,1,wsPaymentData.getLastRow()-1,4).getValues(); const searchValues = wsApprovedApps.getRange("C2:J1990").getValues(); const matchingPayments = searchValues.map(searchRow => { const existingpayment = searchRow[7]; //extract value from existing column J const matchRow = paymentData.find(r => r[0] == searchRow[0]); //console.log(matchingPayments); return matchRow ? [matchRow[1]] : [existingpayment] //If no match is found return existing entry from column J }) //console.log(matchrow); //console.log(matchingPayments) wsApprovedApps.getRange("J2:J1990").setValues(matchingPayments) }
This code does ALMOST exactly what I want it to when applied to my spreadsheet, but it doesn't automatically update when modifying or adding rows to, using your example, the "sales data" sheet. I modified your code to use dynamic ranges rather than static, but it still doesn't update without having to run the code again, which is problematic for a number of reasons. Also, I would prefer if it simply skipped rows that didn't match rather than giving an output, as those rows in my spreadsheet are non-recurring events, and are inputted manually, so I'd appreciate it if it would stop replacing those values with a 0. Any tips?
My god you're amazing! I was trying to do something particular. I have a spreadsheet where I have to associate an ID code in an automatic way. The script should make a research in bigquery and do a match of the name and the surname and then give me back the ID code associated with the person. Like a vlookup into BigQuery. Is that possible? Do you have a video about that? I've found nothing online...
Spot on, this is so helpful. As someone new to Apps Script coding, this was a great help. How can the return be modified to skip updating (and not overlay with [null] when a match doesn't exist)? The line of code that I'd like to update to skip updating rows that don't match is currently: return matchRow ? [matchRow[7]] : null? Thank you so much!
Amazing tutorial. Explained very well.. But how to vlookup the value if the column is reverse? for example my lookup value or criteria is in the J:J column then I want to lookup in column B:B. What would be the code of that? Thank you🙏🙏
Thank you 🙏for sharing this great knowledge. One query though, can we make this script conditional based on column label, I mean whenever we run this script it will first search for specific column label, then it will paste the matching values. I would be so grateful if some one address this query.
Great video, I have tried to include a match inside the search to get for each row a different column but i could not do it, I hope you can make an extension of this tutorial with that option :) thanks,
I was wondering if there is a way, so it will keep the previous data if won't find instead of NULLing it? 😄 Is there something to replace NULL so he skips it and don't update it? And only update those which he finds?
I wouldn't replace spreadsheet VLOOKUPs with this. This is necessary only when you have similar issues that you need done in scrips. Example: you have a Web App and you need to find matching price for product selected by the user.
