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 :)
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.
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!
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 ).
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"?
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,
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...
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?
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?
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🙏🙏
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
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.
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...
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?
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]]} })
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.
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
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
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?
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")
@@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
Interesting and well explained as always! You make learning App Scripts for dummies like me a breeze.
:)
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 :)
:)
Amazing video, love these apps script videos!
Will definitely use it sometime, thanks!
Thanks a lot for your interesting videos ... and many thanks for your wonderful presentation. You would be ( are ) a very good teacher 😊❤
Awesome👍
Thanks 🤗
This is a great video. Well presented, and very easy to follow. Thank you! I've subscribed
👍
Very informative.
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.
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!
This was amazing - thank you very much!
can we have trigger for this? in case there's any additional data from worksheet regularly
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 ).
Really cool video, thanks a lot for uploading it!
:)
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"?
Sure. I would suggest learning JavaScript String methods in order to understand how to do other types of matches, including contains.
Thank you very much sir; I finally got it working with a drop down List as desired; :) :)
Excellent!
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,
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?
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?
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🙏🙏
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
I wonder which use case should this be better or if it has a performance benefit?
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!
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...
Thanks for the video! Is there any way in which we can copy the source formatting to the lookup cell?
There is but it would be really slow.
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?
sir ,What should I do if I want to pick the value of 2 columns.
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]]}
})
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.
Hi Sir,
In this video you used the Vlookup only one column if we pick the multiple columns then how to do this?
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
Great explanation. Is there a way to automatically run the code without having to manually run the code. If not available, can it run with a button?
yes, you can put triggers on
Is the performance be better if we use app script?
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
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
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.
man you're good
Thank You!
map function also not coming. please suggest for both
If map function is not showing then you made a mistake before that.
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 guess, I did and it worked 🤔'return matchRow1 ? [matchRow1[3]] : [searchRow1[2]]'
👍
how to make it renewed every i edit it
The .find method doesn't work, any idea? "Error TypeError: ratesData.find is not a function"
That typically means that you didn't get ratesData correctly in previous lines.
Had same error. Turned out i had .getValues instead of .getValues()
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
?
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)
}
Can we run it automatically?
Can you share your file in your video ?
gist.github.com/chicagocomputerclasses/b89812449243cb0d79229f4eebcd999a
@@ExcelGoogleSheets Thank you very much
Teacher...some of your AppsScript work and some dont???
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
Please give solution to repeat selected rows on every print . Thanks
This guy never shares his codes :(
Yea, this guy sucks! Get him! :)
How can i contact you... Urgent?!!!!!