- 203
- 349 820
Sheets Ninja
United States
Приєднався 24 лис 2020
Google Sheets & Google Apps Script tutorials to help make your life easier and your Google Sheets feel a little less like a chore and a little more magical.
Stop Wasting Time With Pre-Filled Responses for Google Forms
Are there options in Google Form that you wish could be automatically selected? With the prefilled option in Google Forms, you can do just that! This quick video walks you through how to select options and get a custom link that will take you to the Google Form with those options prefilled or preselected.
If you want to accomplish this using options in Google Sheets, then check out this video: ua-cam.com/video/APTGPeyCn-E/v-deo.html
If you want to accomplish this using options in Google Sheets, then check out this video: ua-cam.com/video/APTGPeyCn-E/v-deo.html
Переглядів: 164
Відео
Pre Fill Google Form From Google Sheets
Переглядів 209День тому
Ever wanted to pre-fill a Google Form using data in Google Sheets? Look no further than this tutorial, that will walk you through it, step-by-step. You can make a copy of the final Google Sheet here: bit.ly/4iKmYaZ
Adding a Top Navigation Bar in Google Sheets
Переглядів 33514 днів тому
Ever wanted to add website-style navigation to your Google Sheet? Check out an easy method to add a top navigation bar to your Google Sheets in this quick tutorial.
Download Images or Pictures from Google Sheets
Переглядів 10614 днів тому
Ever wanted to download pictures from Google Sheets to your computer, but not sure how? I go through each method for the different ways images can be incorporated into Google Sheets.
Stop Ticking Checkboxes One at a Time in Google Sheets
Переглядів 11414 днів тому
Ever wished you could check or uncheck more than one checkbox at a time? Learn how to check or uncheck a row, a column, a selection, or an entire tab with a single click.
Let's Create an Automatic Appointment Slot Booking System in Forms, Sheets, and Calendar
Переглядів 32921 день тому
In this video, I'm going to show you how to set up an appointment or event registration or booking system when you have limited slots available for each time slot. We use Google Forms to show the available slots and allow guests or users to select and sign up, and then Google Sheets & Google Apps Script to manage the events and update what options are available on the Google Form, and then fina...
Toggle Show / Hiding All Rows Meeting Condition
Переглядів 12021 день тому
Let's walk through how to quickly hide or show all rows matching a particular criteria in Google Sheets just by clicking a checkbox. You can make a copy of the Google Sheet / Script here: bit.ly/3Z5nOWU
Easily Generate Dynamic Report with Totals in Google Sheets
Переглядів 22321 день тому
Ever need to generate reports from data in Google Sheets with totals, but you're tired of doing it manually? In this video, I walk you through how to quickly and easily set up a dynamic report template and incorporate a totals calculation right into the formula. You can make a copy of the sample file here: bit.ly/3Bm2CUA
Automatically Delete Row from a Checkbox or Dropdown in Google Sheets
Переглядів 14321 день тому
Every need to automatically delete a row in Google Sheets based on a checkbox or a particular selection in a dropdown? In this video, I show you how to accomplish this with a simple script.
Auto-populate Cell Based on Dropdown Selection in Google Sheets
Переглядів 28521 день тому
Do you need to auto-populate a value in a cell based on a selection in the same row, but can't use a formula because that value changes and you don't want prior data overwritten? I walk you through creating a quick & easy script that will solve that exact problem, and bonus is this script doesn't even have to be authorized! You can make a copy of the final Google Sheet & script here: bit.ly/4fF...
Creating a Sales Dashboard in The Office - Full Tutorial Walkthrough
Переглядів 27821 день тому
If you're ready for a full, detailed walkthrough on how to build a dynamic sales dashboard, then check out this video that takes you from a simple data tab all the way to a fully-functioning dynamic dashboard. If you want to skip the video and just get the Google Sheet, you can buy the template here for only $1.99: sheets-ninja-shop.fourthwall.com/products/dynamic-sales-dashboard-in-google-sheets
Automatic Last Updated Notes with Username on Cells in Google Sheets
Переглядів 18828 днів тому
Do you ever need to track the last person to edit a cell in Google Sheets along with their email or name? In today's video, I will walk you how to build exactly that in this step-by-step tutorial. You can make a copy of the final version of the Google Sheet & script here: bit.ly/40VHyif
7 Fast & Simple Ways to Import Data into Google Sheets
Переглядів 219Місяць тому
If you need to import external data into Google Sheets, in this video, I quickly take you through 7 different ways you can accomplish this. One thing that can make external data connections confusing is some sources ban certain IPs (like Google Servers), and so you can actually have a JSON feed that would work if you pinged it from a private server, but not if you try to use it from Google Shee...
Scrape Wikipedia Data into Google Sheets Like a PRO!
Переглядів 95Місяць тому
In today's video, I'm going to walk you through how to use =IMPORTHTML() and =IMPORTXML() to pull data from Wikipedia into your Google Sheets and how to process that data into a good-looking report. I'm going to make a report of the top ten highest-grossing films of all time as a demonstration of these methods. You can make a copy of the final Google Sheet here: bit.ly/4eQxPNe
Create a Dynamic Mini Calendar in Google Sheets
Переглядів 195Місяць тому
Ever want to create a little dynamic calendar in Google Sheets? Look no further! In this step-by-step tutorial, I take you through each step of creating a mini calendar in Google Sheets, including the ability to highlight today, holidays, and special events. You can make a copy of the final example sheet here: bit.ly/3CDno2x
How to Format Part of Text in a Single Cell in Google Sheets
Переглядів 95Місяць тому
How to Format Part of Text in a Single Cell in Google Sheets
Is it Safe to Allow Access for =IMAGE() and =IMPORTHTML() formulas in Google Sheets?
Переглядів 92Місяць тому
Is it Safe to Allow Access for =IMAGE() and =IMPORTHTML() formulas in Google Sheets?
Weighted Average Scores in Google Sheets
Переглядів 72Місяць тому
Weighted Average Scores in Google Sheets
Simple Student Grading & Report with Letter & GPA in Google Sheets
Переглядів 232Місяць тому
Simple Student Grading & Report with Letter & GPA in Google Sheets
Change Default Font and Theme in Google Sheets
Переглядів 140Місяць тому
Change Default Font and Theme in Google Sheets
Add New Lines within Cell in Google Sheets
Переглядів 140Місяць тому
Add New Lines within Cell in Google Sheets
Effortlessly Move a Row to Top or Bottom of Tab in Google Sheets
Переглядів 260Місяць тому
Effortlessly Move a Row to Top or Bottom of Tab in Google Sheets
Create a Simple Weekly Timesheet in Google Sheets
Переглядів 289Місяць тому
Create a Simple Weekly Timesheet in Google Sheets
How to Add a Checkbox in Google Sheets
Переглядів 438Місяць тому
How to Add a Checkbox in Google Sheets
Auto Sort Rows in Google Sheets using Apps Script
Переглядів 247Місяць тому
Auto Sort Rows in Google Sheets using Apps Script
Send Emails Directly From Google Sheets with Apps Script
Переглядів 230Місяць тому
Send Emails Directly From Google Sheets with Apps Script
Secret Hack to Resetting Overwritable Default Values in Google Sheets
Переглядів 161Місяць тому
Secret Hack to Resetting Overwritable Default Values in Google Sheets
How to Create an Assignment Tracker in Google Sheets
Переглядів 329Місяць тому
How to Create an Assignment Tracker in Google Sheets
Thanks!
This is actually so helpful and perfect timing for the new year and my goal tracker! Thank you!
looking at the todo list formulars, could you not combine both the progress bar and the %age formulars?
Can I get you to explain to me how you dad the sum if formula mine came up as an error I need help
Nice brother, Thank you 😇
My form was linked to "Form Responses 8" tab. it mistakenly got unlinked but when I linked for to an existing spreadsheet, my only options is to insert a tab. How do re-link my original form back to the "Form Responses 8" tab. I have scripts already programed for my Form Reponses 8 tabl
That's fantastic! Eu sempre quis um modelo desse e consegui fazer igualzinho seguindo seu passo a passo. Thank you so much!
Please, I wanted to create a form to serve as receipt for my business. And since customers may not but all my products, I need the form to match the product bought to the right column in the data base. How can I achieve this?
I have been using this trick in my finance tracking for some time now and its worked great! However, over the past month it looks like this no longer works. When I try to remove the $ from the Select Data range, it comes back up every time I try to save the validation. Now whenever I drag down the data validation it copies the same row from the "sampleData" sheet. Anyone know of any ways to resolve this issue?
how to cell reference from different document how to lock there
The formula is not working in ma google sheet writing ERROR
Most likely you have a minor typo in your Google Sheet. If you carefully compare the formula there will be a slight difference between the two.
Thank you for this example. Please repeat using a searchable dropdown list filled from data on a separate sheet. This would make Google forms much more useful.
god bless u
What's the words of Hastag Mr... Please
#gid=
Hi, could you hepl me? In my database I have a start and end date, how can I capture it and not consider weekends and holidays?
OMG you saved my life!!!! Thank you!!!
You're welcome!
my man, I can barely see your code and you're barely explaining anything
Allow me to copy that Coding of Apps Script Mister.. I can't memorized...
hello, thanks for the video i have a question, i dont understand if we can do this without the column "ACTION" ?
Yes, you can. The reason I added the action column was to easily differentiate between new and existing events. If all you are doing is creating events, you can simplify the script and just add all to Google Calendar.
@@SheetsNinja Hello, thanks for your reply. To keep things simple, i already have a calendar for 2025, where every weeks is an event. all i need is if i modify something in the calendar, the events are modified too. i dont know if it simple to "modify" events or we have to erase and re write everything ? i dont know where to start. i need this for my workjob to keep things simple after modification and still have updated calendar. in 2024 i only had the calendar, but i had to inform my colleagues and bosses of any changes. thanks by advance
I love your channel so much man! 🙌
I appreciate that! Thanks so much!
Google Sheets is such a flexible tool for creating basic invoice templates. If you ever want a more polished design without spending extra time, platforms like InvoiceBerry provide great ready-made templates that you can download and start using instantly. Both options are super handy!
Hello Ninja, thank you for your helpful video. Just one question, Is it possible to move data on specific locations inside a tab based on a criteria (by month)? For example, I would only want to get the data within a specific month and paste it inside a tab wherever I want, is that possible?
I’m trying this and it’s just not working.
If you make a copy of the example, you can take a look at the code there and compare to your own code.
Hello, does this work with multiple calendars (all under 1 gmail)?
It can, you would just have to add a way to determine which calendar you want to add the event to and then have those calendar event ids in the script as well and then toggle between calendars that way.
This is working when you're trying to move rows on different tabs inside the same spreadsheet. But what if it's to move rows on a whole different document or new spreadsheet? I'm able to move links and all but smart chips are not moving correctly. HELPPPP HAHAHAHHA Thank you!
So if need to move it to another spreadsheet altogether, first of all, you need to remove the "@OnlyCurrentDoc" line in the script if you have it. Next, you need to update the targetSheet variable reference. For example, something like this: let targetSS = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID") // this is the alphanumeric id of the Google Sheet (the long string of characters after "d/" and before "/edit") Then we can update "targetSheet" to refer to that spreadsheet: let targetSheet = targetSS.getSheetByName("target_sheet_name"); After that, you can use all the same coding.
HI Ninja.. As soon as I add the search by date runs and returns "N/A" any ideas ?
If you do not have any rows that match all the criteria, you will get "N/A" as your result. Double-check your other dropdown settings to make sure you have data matching all the criteria you have selected. Otherwise you may have something like "=" instead of ">=" or "<=" which would mean it would only match the single date instead of the date range.
@SheetsNinja tks for the quick response.. Will check again tks,
Hi Ninja.. fixed.. tks for your help. It's a great Dashboard.
i hate chip, but i need to choose more than one choice in dropdown
Yeah that is super annoying they restricted the multi select to the chip. MAYBE they will update that at some point, but they've been working on and adding the smart chips functionality as part of the bigger push so it's likely not going to happen. In which case if they change how chips work and how easy they are to delete, that at least would be helpful.
for search Name Product, why that formula : "&If(C3="", , " and lower(B) contains ' " &lower(C3) &" ' ") why not and lower ( C) in C Product coloumb
In my understanding, colom B is date
You're looking at the columns in the search tab, but it's actually searching the "DATA" column, and the product is in column B there.
@@SheetsNinja "Yes, I just realized that it's a different sheet, thanks."
Thanks for the tutorial! It helped a lot :D
Awesome, so glad it was helpful!
Hi, I tried and it works except for the part sending email (I have added email to the event sheet already). Do you have the next part for sending emails? Thank you
Great, Thank you!
You are welcome! Glad it was helpful!
But first of all do I have to do that table or not ? They are giving me error in codes
I'm wondering, is it possible to apply this function to a table. So that one could include multiple tables within a sheet, and each table would behave with this function? For context I'm trying to make a stock tracking sheet. One table for each product. The goal is to have the table headers, and a single row underneath, which once filled out (stock in or out) is shuffled down and a new row appears for new stock movement to be added. Additionally the filled rows will also become hidden once they are filled, so that all that is visible is still just a single empty row beneath the table header. (Total available stock for each table/product, will be displayed by adding all figures in the incoming stock column and subtracting all in the ' outgoing' stock column.
Im getting "typeError : cannot read properties of undefined (reading 'source')" and it doesn't work. how to fix? Fantastic useful channel btw, subscribed.
thanks. great video
I've used something similar before, to make a list of all tab names in a single google sheet. I wonder now if it would be possible to somehow call a list of names of tabs which contain a certain keyword?
Yes, you would just need to include something like this in the "if" statement: if (tabs[i].getSheetName() != 'Table of Contents' && tabs[i].getSheetName().indexOf("keyword") != -1) {
@SheetsNinja oh wow, thanks for the fast response! I'll see if I can make it work in my file, if I can, that'll save me so much time..
How to export qr code to pc
🐐
Thanks for the great video.
Awesome! So glad you enjoyed this video!
Wow! Great!
Hy very amazing this kinldy ma this learn a video
I love you, Sheets Ninja
Nothing but visual clutter and broken functionality. Why, Google?
There are some "benefits" to chips (for example, with the new multi-select functionality, you HAVE to use chips), and I do appreciate the option to use the chip aesthetic, but I would have greater piece of mind if they made them harder to accidentally delete.
Thank you!
Awesome! Glad this was useful!
Hi Sheetninja, I hope you're doing well. Thanks for training at ARU. I have a question about calculating meeting times in Google Sheets. I've imported meeting data from my calendar into Google Sheets, and I'm wondering if you could make a video that shows how to calculate the total time for weekly, bi-weekly, monthly, and one-time meetings? Also if the meeting is weekly we can show it as weekly, monthly, bi-weekly and so on.
Never looked into this myself. Good stuff. Also, the music is a nice touch.
Awesome to hear! Music really makes everything better, right? Thanks for checking it out!
How can we do this same setup but when they sort the rows, by date for example, the dropdowns still work and don't give the Invalid error notice?
So if the data is going to be sorted, then you will want to use a different method. 1) is using a single cell hack, with a walkthrough here: ua-cam.com/video/rFiFxoO407M/v-deo.html Or 2) you can use a scripted method, which is resilient even when the data is sorted: ua-cam.com/video/5Yysv-QouTQ/v-deo.html
it says error on using the formula which I allow access to the excel sheet
Google added a new permissions flow for any external connections (any images pulled in or external data connections). This is designed to keep people safe, primarily from making a copy of an existing Google Sheet that has nefarious images or data connections. If you are the one that added the photo link, then you can just click "Allow" up top and you'll be fine!
@ right, how do i go about it then so do the same =image(url)=1? the url im using its actually from instagram...
@@itstammyhere Yes, you use the same formula =image("url",1) When you hit enter, you should get prompted to allow access, once you click "Allow Access", the image should display. Occasionally you may have errors trying to display image links in Google Sheets. 1) Some websites block direct linking to their images (or restricted to logged in users), and 2) sometimes what looks like the link to the actual image is actually a link to the site or webpage and won't display then either. A quick test is to paste the link in a new browser window and see what it displayed (if anything).
Wassup man you make it look so easy but ever i do it never works lol must be me..... Could you help me I made a rough rough draft would be grateful if you could help. I can send over daft in email if you agree
Nice - but then how do you export the chart? I can't see how you group the charts so you can pick up the final Sunburst up to put into another app.
You would have to export the chart one at a time. It may or may not work in your final application depending upon whether you can stack the charts there like I did in this walkthrough.