Top 5 Features in Google Sheets for Advanced Users
Vložit
- čas přidán 7. 07. 2024
- What are top 5 features in Google Sheets for advanced users?
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
Learn more:
Intro to arrays • Google Sheets ARRAYFOR...
FILTER function • Google Sheets - Filter...
UrlFetchApp • Apps Script UrlFetchAp...
#top #googlesheets #advanced
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
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.
Query, Array Formula, Import Range, Sheets Protection, Conditional Formatting, Apps Script.
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!
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 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.
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.
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.
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.
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.
I agree with you, I would add query function, very powerful!
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.
for me the query function is a game changer and thanks for the videos you provide that made using it possible.
I didn't know open references worked horizontally too. That's pretty cool
My favorite GSheets function is =GoogleFinance() for looking up stock stuff.
I learned a lot from your videos! Thank you so much!
Good list!
Query would be at #1 for me
Man thanks for every video
Arrayformula, Query, importrange and vlookup are my top 3 although vlookup is not only for Sheets.
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.
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
would be good to see more google finance spreadsheets and stock Analysis spreadsheets
I agree with you, arrays are great
Query and array together and Importrange too
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?
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.
2:55 you can use the =VALUE() function to get a 0 if it's empty :)
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
Thank you!
Is it possible to have open references for both the row and column at the same time?
Excellent 👌
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?
Exactly the same for me!
My favorite feature is a search with highlights all findings at a time.
Impressive function yeah 👍
Great Video
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 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.
❤❤❤
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? czcams.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.
Sir we want complete courses of script for beginners to advance
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.
{\} that's why i didn't work the other day with ,
Thnak you very much
Bro I need automatically Date wise open the cell ?
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?
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
QUERY !!!!
QUERY()?! Built-in RegEx functions?!
RegEx functions are nice, I agree with you. I don't use QUERY function that often.
How isn't FILTER VIEWS included?
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.
IMPORTRANGE
Can find the person who has given me a cheque and I have to deposit
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)