1. Arrayformula (Never copy down a formula again!) 2. Query (Nuff said) 3. Importrange (Nuff said) 4. Google Forms connectivity with sheets and ease of use. (Incredibly powerful and understated) 5. Apps script (Just dipping my toes in here but so useful already) Love your videos, thank you for teaching me that arrayformula and query exists and how to use it.
0:18 - Arrays 2:31 - Difference between empty cell and zero 3:12 - Filter function 4:24 - Open references 6:20 - Scripting implementation 8:40 - Call external API ("bonus")
Apart from what has already been said, the most important for me is the ability to dynamically update data, combine calculations based on data from multiple sheets and sources if needed. And most importantly, in real time.
Agreed, though I feel like it doesn't exactly do much, you just wrap anything you are doing with arrays in it. Why doesn't it just check for if you are working with arrays? I could see the use of it in the case of a function like sum, except arrayformula doesn't affect sum, it just sums the entire array, no, instead to sum table like data you have to use dsum. I just don't see why they can't just make arrayformula implicit.
I no longer use spreadsheets in my work life, but I keep watching these videos for how I can develop sheets for my personal use, I always look forward to your uploads.
I agree with all your points, even learned a new one (didn't know about the open ended method to get a whole row). Another good thing about GoogleSheets in combination with Apps Script is how you can combine a GoogleSheet with a Google Calendar or GMail or whatever. ImportRange and ImportData are also awesome.
Though I commonly used the filter function, I've been amazed at how the array works. I've never utilized script aside from your guides, although I used to create a macro on excel back then.
Absolutely brilliant! Looking forward to dive into these GS tricks. I've been using Sheets for a few months now for my work as a garden and landscape designer. I will probably gather around about 2000 plants with about 20-30 important well researched aspects per plant in one plant sheet. I'ld like to make a sophisticated 'search engine' from the 'plant source sheet' to 'filter sheet' to particular 'project sheet' eg. via the 5 advanced tricks you shared in this video. This will save enormous amount of time the upcoming decades during my projects :) Thank you! For this first introduction
Filter, arrays, import range. Filter has so much power. You can logically do “or” within it, you can do arrays within it… tons of power in that function.
Thanks! I have followed many of your other videos and I like your methods of explanation very good. I am glad that you are highlighting some features of the sheets which are very useful. I find I can use your advice in many of my existing projects and make them more functional and easy to use. Your approach of combining advanced formulas along with a mixture of useful scripts is very useful. I prefer my office staff to use google sheets so that I can control my office from anywhere, I therefore like this channel because it is good for my staff as well. I wish that you keep bringing more informative videos for all.
it's dynamic theme if we set cell color as the theme has set, when we change the overall theme the whole spreadsheets which has the color will dynamically change as well
at some stage would you please show us how the Library can be used in google apps scripts or any other way to use one script within multiple projects. thank you
Open references are amazing, though every once in a while, I get annoyed that open references don't let you reference both rows and columns openly together. Something like B3:$ which would select from B3 all the way down and all the way right. The closest to this is B3:ZZZ but, while that works it is both clunky and doesn't exactly format right.
Arrayformula, query, importrange, script, and integration to google ecosystem, so easy to create automation report, even personalized dashboard for users. The main problem with google sheets is it really heavy to load or open the file when you have a large number of data and users that open or edit the file in the same time.
Is there a way to Insert a calendar (like you can insert a chart) into google sheets? So I have just a little monthly calendar chart on my sheet that I can move around while doing other things on the sheet?
I want to know if it is possible to write a script that works with google forms to create a 'sign in and sign out' form that we can run in kiosk mode on a chromebook. I have no knowlege of any kind of coding but before i even start looking i want to know if it is even possible. My expectation are. A form that asks, are you signing in or out. For sign in, the form should collect name, phone number and purpose of visit. I need the form to automatically create a time stamp (this much i know is possible). Once data has been entered, the form should return to a default page of "are you signing in or signing out. When the visitor leaves i would like the user to click sign out and the forms asks for their name. It should then check if they signed in and then display a simple message asking if they want to sign out. If they did not sign in the form should then display another message. The accompanying google sheet should also change the colour of the text if a visitor has signed out. I hope this makes sense
I regularly watch your videos. They are so interesting and useful. I have one question, I have a list of students exam numbers in Google sheets and I want to bring those numbers in Google quiz so that at the time of exam students can easily pick their numbers. Is this possible?
I have a question, can anyone help me for the right price in Google sheets ? I need to get a total count of data but data is unorganised and duplicated, I want to add duplicate and show total
I absolutely love your tutorials. Thank you very much for producing them. I'm having trouble finding one I saw a while back on creating a Product Sales Analysis. I have several clients, each with several invoices, containing a number of products and would like to generate a report that creates clients name in Col1, Invoice Col2, and Services from that inv on Col3 with price Col4. Could you link me the video I'm thinking of?
@@ExcelGoogleSheets I believe it was a query function. If memory serves, it demonstrated the functions I'm looking for. Which would be to display my annual sales data, with Col1 as Client, Col2 as Invoice, Col3 as Line Item, and Col4 as Price. The main component was the ability to return the list of Sales People(Client) in Col1, yet skip rows determined by amount of data in Cols2-4 before returning the next Col1 Nth rows down.
@@ExcelGoogleSheets Ive been reviewing this playlist and many other videos. Not finding what I recall seeing previously, however through a comment on another post, I looked into Query Pivot functions and found it useful. Does not seem to be able to sort or order the pivot columns though, only the Group By Rows. None the less, I truly am grateful for the wealth of knowledge you share here. I love the versatility of Sheets and look forward to learning more from you.
Not relevant to this video, but can you help put up tutorial to match time zones in sheets with JS... coz every time I run a script it returns one day less than the date in sheet cells. FYI, time zone is Colombo, Sri Lanka... GMT+5:30
i don't agree javascript is more pleasant to work than VBA, it is just that you're already familiar with JS, it's a personal preference thing. On the other hand, excel has the "highlight duplicates" straight away without introducing a formula as well as dependent dropdown lists which in gsheets is much more complicated and like 10 more steps to do.
What most people watching this channel don't realize is that I use Excel more than I use Google Sheets and I do more training for Excel than Google Sheets. I've never said I like Google Sheets better than Excel in general. Each has their strengths and weaknesses.
I like this video however it was presented in a manner more of “Why I rather google sheets than other platforms” rather than speaking on advanced functions/tools as the title mentions.
I don't know about efficient, but it's definitely easier to work with. VBA might seem easy when you do basic macros, but it's painful to work with once you start doing more serious work with data.
1. Arrayformula (Never copy down a formula again!)
2. Query (Nuff said)
3. Importrange (Nuff said)
4. Google Forms connectivity with sheets and ease of use. (Incredibly powerful and understated)
5. Apps script (Just dipping my toes in here but so useful already)
Love your videos, thank you for teaching me that arrayformula and query exists and how to use it.
0:18 - Arrays
2:31 - Difference between empty cell and zero
3:12 - Filter function
4:24 - Open references
6:20 - Scripting implementation
8:40 - Call external API ("bonus")
Thank You!
My top 5 would be
1) Query (my day doesn't go without a query)
2) Filter
3) Index Match
4) Array formula
5) Java Scripts
@M Dev Oberty GAS
brother, could you tell me how can I learn all those advanced scripts features easily
Apart from what has already been said, the most important for me is the ability to dynamically update data, combine calculations based on data from multiple sheets and sources if needed. And most importantly, in real time.
One thing I love is what happens when you type sheet.new in address bar of your browser.
It also works for doc.new slide.new and script.new
I suggest you include the arrayformula function as well. This function comes in handy when building e.g. budget and forecast models
Agreed, though I feel like it doesn't exactly do much, you just wrap anything you are doing with arrays in it. Why doesn't it just check for if you are working with arrays? I could see the use of it in the case of a function like sum, except arrayformula doesn't affect sum, it just sums the entire array, no, instead to sum table like data you have to use dsum. I just don't see why they can't just make arrayformula implicit.
I no longer use spreadsheets in my work life, but I keep watching these videos for how I can develop sheets for my personal use, I always look forward to your uploads.
Query, Array Formula, Import Range, Sheets Protection, Conditional Formatting, Apps Script.
I agree with all your points, even learned a new one (didn't know about the open ended method to get a whole row).
Another good thing about GoogleSheets in combination with Apps Script is how you can combine a GoogleSheet with a Google Calendar or GMail or whatever. ImportRange and ImportData are also awesome.
Though I commonly used the filter function, I've been amazed at how the array works. I've never utilized script aside from your guides, although I used to create a macro on excel back then.
My favorite GSheets function is =GoogleFinance() for looking up stock stuff.
Absolutely brilliant! Looking forward to dive into these GS tricks.
I've been using Sheets for a few months now for my work as a garden and landscape designer. I will probably gather around about 2000 plants with about 20-30 important well researched aspects per plant in one plant sheet.
I'ld like to make a sophisticated 'search engine' from the 'plant source sheet' to 'filter sheet' to particular 'project sheet' eg. via the 5 advanced tricks you shared in this video. This will save enormous amount of time the upcoming decades during my projects :)
Thank you! For this first introduction
I agree with you, I would add query function, very powerful!
Filter, arrays, import range. Filter has so much power. You can logically do “or” within it, you can do arrays within it… tons of power in that function.
I didn't know open references worked horizontally too. That's pretty cool
for me the query function is a game changer and thanks for the videos you provide that made using it possible.
Thanks! I have followed many of your other videos and I like your methods of explanation very good. I am glad that you are highlighting some features of the sheets which are very useful. I find I can use your advice in many of my existing projects and make them more functional and easy to use. Your approach of combining advanced formulas along with a mixture of useful scripts is very useful. I prefer my office staff to use google sheets so that I can control my office from anywhere, I therefore like this channel because it is good for my staff as well. I wish that you keep bringing more informative videos for all.
it's dynamic theme if we set cell color as the theme has set, when we change the overall theme the whole spreadsheets which has the color will dynamically change as well
at some stage would you please show us how the Library can be used in google apps scripts or any other way to use one script within multiple projects. thank you
Arrayformula, Query, importrange and vlookup are my top 3 although vlookup is not only for Sheets.
Open references are amazing, though every once in a while, I get annoyed that open references don't let you reference both rows and columns openly together. Something like B3:$ which would select from B3 all the way down and all the way right. The closest to this is B3:ZZZ but, while that works it is both clunky and doesn't exactly format right.
Arrayformula, query, importrange, script, and integration to google ecosystem, so easy to create automation report, even personalized dashboard for users. The main problem with google sheets is it really heavy to load or open the file when you have a large number of data and users that open or edit the file in the same time.
Is it possible to have open references for both the row and column at the same time?
Is there a way to Insert a calendar (like you can insert a chart) into google sheets? So I have just a little monthly calendar chart on my sheet that I can move around while doing other things on the sheet?
Good list!
Query would be at #1 for me
Man thanks for every video
would be good to see more google finance spreadsheets and stock Analysis spreadsheets
Query and array together and Importrange too
2:55 you can use the =VALUE() function to get a 0 if it's empty :)
I learned a lot from your videos! Thank you so much!
I want to know if it is possible to write a script that works with google forms to create a 'sign in and sign out' form that we can run in kiosk mode on a chromebook.
I have no knowlege of any kind of coding but before i even start looking i want to know if it is even possible.
My expectation are. A form that asks, are you signing in or out. For sign in, the form should collect name, phone number and purpose of visit. I need the form to automatically create a time stamp (this much i know is possible). Once data has been entered, the form should return to a default page of "are you signing in or signing out.
When the visitor leaves i would like the user to click sign out and the forms asks for their name. It should then check if they signed in and then display a simple message asking if they want to sign out. If they did not sign in the form should then display another message. The accompanying google sheet should also change the colour of the text if a visitor has signed out.
I hope this makes sense
This is not possible with Google Forms, but it's possible with Apps Script Web App.
Excellent 👌
I agree with you, arrays are great
I regularly watch your videos. They are so interesting and useful. I have one question, I have a list of students exam numbers in Google sheets and I want to bring those numbers in Google quiz so that at the time of exam students can easily pick their numbers. Is this possible?
I have a question, can anyone help me for the right price in Google sheets ?
I need to get a total count of data but data is unorganised and duplicated, I want to add duplicate and show total
I absolutely love your tutorials. Thank you very much for producing them. I'm having trouble finding one I saw a while back on creating a Product Sales Analysis. I have several clients, each with several invoices, containing a number of products and would like to generate a report that creates clients name in Col1, Invoice Col2, and Services from that inv on Col3 with price Col4. Could you link me the video I'm thinking of?
Are you referring to Pivot Tables or QUERY function?
@@ExcelGoogleSheets I believe it was a query function. If memory serves, it demonstrated the functions I'm looking for. Which would be to display my annual sales data, with Col1 as Client, Col2 as Invoice, Col3 as Line Item, and Col4 as Price.
The main component was the ability to return the list of Sales People(Client) in Col1, yet skip rows determined by amount of data in Cols2-4 before returning the next Col1 Nth rows down.
This? th-cam.com/play/PLv9Pf9aNgemvAMlqvHP9RhXPW98g_eo7d.html
@@ExcelGoogleSheets Ive been reviewing this playlist and many other videos. Not finding what I recall seeing previously, however through a comment on another post, I looked into Query Pivot functions and found it useful. Does not seem to be able to sort or order the pivot columns though, only the Group By Rows.
None the less, I truly am grateful for the wealth of knowledge you share here. I love the versatility of Sheets and look forward to learning more from you.
My favorite feature is a search with highlights all findings at a time.
Impressive function yeah 👍
Bro I need automatically Date wise open the cell ?
Sir we want complete courses of script for beginners to advance
Not relevant to this video, but can you help put up tutorial to match time zones in sheets with JS... coz every time I run a script it returns one day less than the date in sheet cells. FYI, time zone is Colombo, Sri Lanka... GMT+5:30
Great Video
any javascript library to read tabular data from an image?
Depends. Where is the picture located? And what kind of tabular output do you need?
i don't agree javascript is more pleasant to work than VBA, it is just that you're already familiar with JS, it's a personal preference thing. On the other hand, excel has the "highlight duplicates" straight away without introducing a formula as well as dependent dropdown lists which in gsheets is much more complicated and like 10 more steps to do.
I knew VBA before I started using JavaScript. But of course, everybody has their own preference.
What most people watching this channel don't realize is that I use Excel more than I use Google Sheets and I do more training for Excel than Google Sheets. I've never said I like Google Sheets better than Excel in general. Each has their strengths and weaknesses.
Thank you!
How isn't FILTER VIEWS included?
Exactly the same for me!
QUERY()?! Built-in RegEx functions?!
RegEx functions are nice, I agree with you. I don't use QUERY function that often.
❤❤❤
I like this video however it was presented in a manner more of “Why I rather google sheets than other platforms” rather than speaking on advanced functions/tools as the title mentions.
{\} that's why i didn't work the other day with ,
Thnak you very much
QUERY !!!!
Can find the person who has given me a cheque and I have to deposit
IMPORTRANGE
Since WHEN is JavaScript easier or more efficient than Visual Basic???
Are you ok??
I agree with Jim's question..a reply would be very wellcome. ;)
I don't know about efficient, but it's definitely easier to work with. VBA might seem easy when you do basic macros, but it's painful to work with once you start doing more serious work with data.
@@ExcelGoogleSheets totally agree :-)
MAP. BYROW, BYCOL, LAMBDA(variable, LAMBDA(...)(...))(YOUR_VALUE)