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]]} })

:)

Thanks 🤗

:)

That typically means that you didn't get ratesData correctly in previous lines.

Had same error. Turned out i had .getValues instead of .getValues()

👍

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

Sure. I would suggest learning JavaScript String methods in order to understand how to do other types of matches, including contains.

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.

@@ExcelGoogleSheets hmmm yes. I'm sure I have more than a couple places I'm using a mix of filter, map or for loops that could be simplified! Thanks!

Thank You!

yes, you can put triggers on

function DOLOOKUP() { const ss = SpreadsheetApp.getActiveSpreadsheet() const ws= ss.getSheetByName("Test Drive") const wsData = ss.getSheetByName("Test Drive Result") const ratesData = wsData.getRange(6,3, wsData.getLastRow()-1,wsData.getLastColumn()-1).getValue() const searchValue = ws.getRange("F4").getValue() const matchRow = ratesData.find(r => r[0] == searchValue) console.log(matchRow) }

there is no library. so long as you are under V8 engine you can use find even if it doesn't show in autocomplete.

If you remind me what the code was in this video I might be able to help you. Otherwise I don't remember 😀

@@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) }

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?

There is but it would be really slow.

Excellent!

I guess, I did and it worked 🤔'return matchRow1 ? [matchRow1[3]] : [searchRow1[2]]'

👍

:)

If map function is not showing then you made a mistake before that.

gist.github.com/chicagocomputerclasses/b89812449243cb0d79229f4eebcd999a

@@ExcelGoogleSheets Thank you very much

Thank You!

Not sure what you mean by "dont". Any particular examples of the ones that don't work?

@@ExcelGoogleSheets Teacher...I couldn't make either of the Timestamp Scripts work. Went through them with a Fine Toothed Comb too???

@@Puner54 Share the code you've tried, maybe we can help to spot the problem.

@@ExcelGoogleSheets Teacher...I'm not certain on how to "Share" the Code? Don't I need an Email Address to do so?

Paste it in comments

Yea, this guy sucks! Get him! :)