![Sheets Ninja](/img/default-banner.jpg)
- 118
- 198 610
Sheets Ninja
United States
Приєднався 24 лис 2020
Google Sheets, now a little more magical! 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.
Easy Automatic Last Updated Timestamp in Google Sheets [No Authorization Required]
How to automatically add a "last updated" timestamp in Google Sheets using Apps Script whenever someone edits or changes the Google Sheet. This script doesn't require authorization, meaning it will automatically work for anyone who opens the Google Sheet.
You can download the Google Sheet I used to demo the project here: bit.ly/4ca4Jao
You can download the Google Sheet I used to demo the project here: bit.ly/4ca4Jao
Переглядів: 280
Відео
Easy Data Entry in Google Sheets with onEdit Apps Script for Mobile-Friendly Solution
Переглядів 38221 день тому
Here's a quick video demoing using Google Apps Script to save data from a simple form in Google Sheets to a table/database format in a different tab. This solution uses an onEdit script which means you can use this on the app or a mobile browser, which allows this functionality to work even when using a tablet or a mobile phone. At the end of the video, I also show you how to do an installed tr...
Manage Temporary Access to Google Sheets with Apps Script
Переглядів 12428 днів тому
If you ever need to manage granting temporary access to Google Sheets, you definitely need this tutorial! I walk you through all the scripting needed to make a streamlined solution to manage temporary access, whether it's for a single minute or multiple days! You can make a copy of the final Google Sheet & Apps Script here: bit.ly/3YmqMrc 0:00 Intro 0:45 Granting Access 4:45 Trigger to Remove A...
Basic Guide to Pivot Tables
Переглядів 15228 днів тому
Stop agonizing over pivot tables! In this video, I walk you step-by-step from absolute beginner to some cool pivot tables so by the end of this video, you will be able to impress your co-workers and your boss.
Effortless Google Sheets Sharing with Apps Script!
Переглядів 240Місяць тому
Stop spending hours adding and removing people from your Google Sheets manually and streamline managing access with this Apps Script tutorial. Make a copy of the final Google Sheet & Apps Script demoed here: bit.ly/46oQJIz 0:00 Intro 0:34 Basics 0:55 @OnlyCurrentDoc 1:10 Apps Script 7:22 Authorizing the Script 7:50 First Test - Adding Access 8:02 2nd Test - Removing Access 8:20 Adding Script Me...
Run Apps Script at Certain Specific Times: How to Automatically Manage Timed Triggers
Переглядів 236Місяць тому
In this video, I walk you through how to automatically manage specific time triggers in Google Apps Script. You are probably already familiar with running scripts every day and picking an hour slot (e.g. 9am), but those triggers actually run anytime between 9 and 10, so what if you want your script to actually run at 9am? In this script, we will add and automatically manage these triggers to ge...
Simple Inventory System & Report Tutorial in Google Sheets
Переглядів 210Місяць тому
In this video, I will show you how to build a simple inventory system and then how to pull that data into a monthly inventory report. You can download the final Google Sheet here: bit.ly/3zzstaq
Sparkline Charts: Inline Visuals For Your Google Sheets
Переглядів 68Місяць тому
In this video, we're looking at how to create various sparkline charts in Google Sheets, ranging from line, column, and bar sparklines in a variety of applications. You can create a copy of the Google Sheets in the video at this link: bit.ly/4bBRMGb
How to Split Text & Words From A Cell To Columns in Google Sheets
Переглядів 53Місяць тому
In this video, I will show you several ways to split text in Google Sheets, including splitting words by a space or comma, splitting by a custom string, and splitting by new lines in a single cell.
Split Multiple Lines in a Single Cell into New Cells - Google Sheets
Переглядів 64Місяць тому
If you have multiple lines of text in a single cell in Google Sheets but need to split them out into new columns, then this is the video for you with a super quick formula to solve your problem.
Google Sheets Hack: Nested Pie Chart Overlay / Sunburst Chart
Переглядів 310Місяць тому
Ever wondered if you could overlay or nest two pie charts so you could display metrics on two dimensions (otherwise known as a Sunburst chart in Excel? Check out this tutorial walk-through that explains how to set up the data and build both pie charts.
How to Make Line Charts in Google Sheets
Переглядів 47Місяць тому
In this video, I walk you through create line graph charts in Google Sheets, starting with the very basics but then also going through a number of different ways to make your charts look very professional.
Combine 2 (or more) Columns in Google Sheets
Переглядів 153Місяць тому
In this video, I show you how to easily combine 2 columns in Google Sheets, and then I show you some cool tips and tricks for combining more than 2 columns, as well as how to format dates.
Create a Basic Invoice Template in Google Sheets
Переглядів 344Місяць тому
Are you ready to build a simple invoice template in Google Sheets? Follow this simple step-by-step tutorial to build your own invoice template. You can make a copy of the template here: bit.ly/3xwhcqR
Google Sheets Sidebar Secret: Select Multiple Items - Full Tutorial
Переглядів 3332 місяці тому
Do you need to be able to select multiple items in dropdowns in Google Sheets but have been endlessly frustrated by the lack of capability to do so? No longer, using this slick, streamlined sidebar interface that allows you to pull the dropdown options from a cell (including any currently selected options), and add or update the selections with a click of a button. You can make a copy of the fi...
Google Sheets Hack: Select Multiple Items in Google Sheets
Переглядів 4072 місяці тому
Google Sheets Hack: Select Multiple Items in Google Sheets
Get ChatGPT AI in Your Google Sheets: Quick Guide
Переглядів 3352 місяці тому
Get ChatGPT AI in Your Google Sheets: Quick Guide
CSV to Google Sheets: Automate Data Imports From a Folder with Apps Script!
Переглядів 3102 місяці тому
CSV to Google Sheets: Automate Data Imports From a Folder with Apps Script!
No More Copy/Paste: Import CSV Files Directly into Google Sheets
Переглядів 2962 місяці тому
No More Copy/Paste: Import CSV Files Directly into Google Sheets
Easy Dropdown Selection Email Notifications in Google Sheets
Переглядів 6562 місяці тому
Easy Dropdown Selection Email Notifications in Google Sheets
✅ Effortless Email Notifications With a Checkbox in Google Sheets
Переглядів 3392 місяці тому
✅ Effortless Email Notifications With a Checkbox in Google Sheets
5 Ways to Automate Tab Organization in Google Sheets
Переглядів 1902 місяці тому
5 Ways to Automate Tab Organization in Google Sheets
Tables in Google Sheets! Travel Planner Table Templates [Free Sample Download]
Переглядів 2302 місяці тому
Tables in Google Sheets! Travel Planner Table Templates [Free Sample Download]
Tables in Google Sheets! Team Planning Table Templates [Free Sample Download]
Переглядів 2272 місяці тому
Tables in Google Sheets! Team Planning Table Templates [Free Sample Download]
Tables in Google Sheets! Recruiting Tracker Table Templates [Free Sample Download]
Переглядів 2202 місяці тому
Tables in Google Sheets! Recruiting Tracker Table Templates [Free Sample Download]
Tables in Google Sheets! Product Roadmap Table Templates [Free Sample Download]
Переглядів 2072 місяці тому
Tables in Google Sheets! Product Roadmap Table Templates [Free Sample Download]
Tables in Google Sheets! Marketing Tracker Table Templates [Free Sample Download]
Переглядів 4062 місяці тому
Tables in Google Sheets! Marketing Tracker Table Templates [Free Sample Download]
Tables in Google Sheets! Inventory Management Table Templates [Free Sample Download]
Переглядів 2732 місяці тому
Tables in Google Sheets! Inventory Management Table Templates [Free Sample Download]
Tables in Google Sheets! Project Management Table Templates [Free Sample Download]
Переглядів 8652 місяці тому
Tables in Google Sheets! Project Management Table Templates [Free Sample Download]
Tables in Google Sheets! Customer Relations Table Templates [Free Sample Download]
Переглядів 2502 місяці тому
Tables in Google Sheets! Customer Relations Table Templates [Free Sample Download]
I appreciate you bro. Thank you!
What if I want data to flow automatically to a table in a different tab and only that tab? The table is shifted a few columns to the right and has filters/conditional formatting/Data Validation. I guess what I'm asking here is if I can still make the date move cleanly from the form responses to the main sheet. I hear the main alternative would be to use the query function but I'm not sure how that works. Edit: I also have some stuff merged to the very right in the same rows so can the form add information in each row without messing that up?
It is possible to do custom things like you're mentioning, but requires more complexity and understanding of how things work. There are ways to reset filters to include new data, and ways to set it up with conditional formatting and data validation, and even merge cells if needed, it's just more complex and finnicky than could be quickly explained in full. A quick overview of the approach however is as follows: Filter: 1) Remove old filter. (sheet.removeFilter() ) 2) Select range containing old & new data (sheet.getRange() -- make sure to include header or title row) 3) Create filter (sheet.getRange().createFilter() ) Conditional Formatting & Data validation Typically the best approach here is to have the conditionally formatting and data validation run down to the end of the tab. If that is not possible, then if you can always have it applied to at least one row after the end of the data, then to add new data, if you can insert rows, it will keep any conditional formatting & data validation already applied to the range. If neither of these is possible, then you may need to actually build & apply conditional formatting & data validation in the script (which is a whole tutorial in and of itself). Merged cells: Merged cells can be more challenging to deal with, as if cells are already merged, you will run into errors if you try to merge in a way that doesn't work. If you are just doing a simple merge (e.g. merge across), you can select the range in the script and then do .mergeAcross()
@@SheetsNinjatysm
@@SheetsNinja Can I have a button that adds a row and prompts manual info into the new row every time, to the same table?
why does it say e is undefined
A script that is meant to run from a trigger (e.g. onEdit or onFormSubmit), passes information about that event (aka form responses) through the parameter (in this case, "e"), and so the "e is undefined" when you authorize the script means it couldn't find any information about the event, which when you authorize it, is fine.
Thanks a lot..u're solving my problem today..very appreciate!
Glad to hear that
these videos are super helpful!! do you know if it's possible to move the row to the other sheet but only in a specific columns range? So the row data would only start showing up in column C instead of A as an example, without adding more empty columns in the origin sheet. Thanks!
For sure! Instead of the line with .appendRow(), we would do this: targetSheet.getRange(targetSheet.getLastRow()+1,3,1,data[0].length).setValues(data);
Do we have a similar feature in excel?
There is a similar feature using VBA code: answers.microsoft.com/en-us/msoffice/forum/all/move-a-row-based-on-drop-down-list-to-another/5b0a19e8-e753-4538-ae15-e6b3d5e349b6
THANKS SIR
Awesome, so glad this was helpful!
Thank you! What if I wanted to add the User who last modified next to the timestamp? Is that doable?
Unfortunately that is a restricted scope, and the only way to get around it is using a simple onEdit trigger and have each person using the sheet to authorize the script.
The script works great. Thanks so much! One of the fields I am emailing is currency. This is the script line: <strong>Price</strong>: ${data[2]}<br /> . I would like it to have the currency formatting. I am brand new at this so any help would be appreciated!!!!
So before your "price" line of code, add this code in: // Format the price to USD using the locale, style, and currency. let USDollar = new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD', }); then change your script line to: <strong>Price</strong>: ${USDollar.format(data[2])}<br />
Thanks. I was using streaks but they started getting out of budget. This is great
Glad I could help!
@@SheetsNinja yup. Just finished setting it up. Now I gotta xfer all the data
Please reupload this without music. I am so overloaded trying to understand you with this loud music blaring over your soft voice. -I need the information, I don't need to be entertained with dance music as well, I promise.
Is it possible to automatically clear DataValidation on Dependent dropdown when the parent dropdown is deleted?
I've done that before. Typically I will use an if statement to determine when this happens. Let's say the parent dropdown is in column 2 (B) and the dependent dropdown in column 3 (C): if (col == 2 && val == '') { sheet.getRange(row,3).clearDataValidations() } That will reset the dependent data validation when you clear out the data in column 2.
am i able to run 2 different triggers on different tabs in google sheets?
So you have to use one trigger, and just use if statements to handle the distinctions between different tabs, or if the logic and columns are the same, the if statement can just make sure the script only runs for those two tabs.
This is extremely helpful! Thank you!
Awesome, so glad this was helpful!
Hey, thank you for this CRM template. I have a problem with the automation though, can you maybe help me out?
What issue are you having?
Hello, I found this video and it helps me a lot. But I have a question about it. If I want to keep the data of the first column (where you can set prospects or leads etc.) and not delete it, how should I change the code to do that? It is possible to keep this data without problems? Thank you
All you have to do is remove the line in the code that goes like this: data[0][0] = '';
Not working.. Do you kkow if something have changed?
It works. Initially had the same issue. Please make sure you clicked the "button" Save Allowlist in order to save your changes on the options form...
@@panagiotiskapsiotis2263 Yes, I installed and removed the extension multiple times and now it works.
Glad you figured it out! It was a little finnicky for me at times, but once you get it working, it's SO helpful and timesaving!
Hey man! in 2 weeks I've learned a lot of things with you! Man I really appreciate that!
Glad to hear it!
Hi! I've just tried to do the same but got an error Exception: Cannot call SpreadsheetApp.getUi() from this context.
Is there a way to schedule the tabs to reorder based on the month? I'd like to create a calendar with each month having it's own tab, but I'd like the front tab to be the current month. Could I have the past month's tab automatically move to the end at a specific date for each month? Thank you!
i applied your script but showed Error TypeError: Cannot read properties of undefined (reading 'range') onEdit @ Untitled.gs:2 and no timestamp appeard. please advise
That is the error you get when you authorize the script, does the script work now when you edit something in the Google Sheet?
You're such a big help! 🤩 Quick question, why other dropdowns and checkboxes in other columns don't copy as is? It changes into the "word without dropdown" and "TRUE."
So it's just copying the text. You can either copy the data validations as well (a little more complicated), or you can add the dropdowns and checkbox validation on the target sheets and then it will fill in that way.
I'm facing another problem,,, I have data validation rules set to the columns in database sheet ... so unfortunately when running the script, the new added row gets inserted at the bottom of the sheet after all the blank rows, instead of being inserted below the last edited row with data... so how can I fix this issue ? is there a way to force it to insert a new row just below if certain cell in a column is not blank (containing data) ? ... Another method would be to if there is a way to move the newly inserted data to be appended below the last cell containing data instead of at the bottom of the sheet ... Thanks, hopefully you find the solution for this issue.
So the big offender here is checkboxes, so typically I will set up dropdowns and other data validation, but then use the script to add checkboxes. You can do this by something like: targetSheet.getRange(targetSheet.getLastRow(),8).insertCheckboxes() That's what I would use after adding the new row of data.
On my sheet, we have hidden columns. Would you count the hidden columns in the array? Very cool video, this is exactly what we were looking for.
Yes, you would need to count the hidden columns as well.
How about if the checkbox was uncheck then the last update cell will be blank
If the Google Sheet registers a cell update, it will trigger the last updated timestamp functionality.
@@SheetsNinja i mean if i uncheck the checkbox, the cell for date/timestamp should be cleared. how to code for that?
@@JosephJericoMarino So you'll just want to add a new section in the script that looks something like this (assuming column 8 is where the checkbox is that you want to use to clear the timestamp and column 12 is where the timestamp goes): if (col == 8 && val == false) { let sheet = e.source.getActiveSheet(); sheet.getRange(row,12).clearContent() } And I would drop this either before or after the current if statement (remember that an if statement starts and ends with a curly bracket, so make sure to keep this out of the current if statement).
How to add last user updated? I want to track the user as well
The users are member of my google workspace who authorized to edit the file.
Will this work on a shared sheet?
If it's a shared sheet, then either everyone using the sheet has to authorize the script (and this will be necessary if you're using macros or a script triggered from a button or a menu). If you're using something like an onEdit trigger, you can have one user authorize the script if you use an installed trigger (add it manually) versus using the onEdit function name to use the simple trigger functionality.
Is there a way to keep hyperlinked text active when you move it from one section to another?
Yes, you have to do .getRichTextValues() and .setRichTextValues() to copy hyperlinked text. It can be a little more complicated to layer that in with the .getValues() and .setValues(), which is why I didn't cover it in this video.
This is an awesome function. It's actually opened up a question or new challenge. I'm using each sheet in a the workbook as a customer work page. All tabs will be identical , there are 2 cells dedicated to text "billed" and the cell next to it has a formula then spitting out a number. Ex C4 (text), C5(number) Based off of this table of contents function in your video, i'd like to query the billed number cell C5 from each tab automatically as the table of contents grows. The billed number would be populated in your column F as Cells C come to exist. If you could point me to a resource or one of your video's that'd be great. thank you. I'm a subscriber now.
So this is actually pretty simple, you can use an indirect formula to dynamically reference data within a tab. So let's say the tab names start on the tab in B5 on your sheet. In C5, I would a formula like this: =if(C5="","",indirect(C5&"!C4")) And then you can drag this formula down. I use the initial if statement to keep it from erroring out if you drag it past where you have tab names. And then what the indirect will do is take the tab names from C5 and make a cell reference something like =TabName!C4
Great video, and thank you for sharing. Quick question - I want to use this for a print request form where the checkbox is marked TRUE and email sent that it's ready to pick up for my school media center. So I actually need the checkbox to remain so I don't end up making duplicates of print jobs. I thought removing sheet.getRange(row, col).uncheck(); would do the trick, but it didn't. Any suggestions?
Nevermind - figured it out. I had a syntax error in line 16 so project wasn't saving. Thank you again! This is so helpful.
Thank you and really like your contents they clear and clean :) but could you go through that ends of your videos make a quick example of how to use these sheets in the proper way just for novice so they could get a better understanding of the afterwards .. cheers 😊
I have tried this tutorial, and it seems to keep the format of the database sheet when adding new row 🙏🏻 However, it doesn't copy the formulas from the previous row, when adding a new row. So how to fix this issue ? For example I have formulas set at column 20, 21 in target sheet, while I edited the script to only copy (transposes) data to column 1 to 18... So i want it when adding new rows, to also copy the formulas from the above last row.
I found the solution for this issue, by a another video you made earlier, is to make an array of forumlas in the first row, so it automatically applies it to the new row inserted
Awesome, glad you figured this out!
Great video! But when I executed it, I got this message in the mail: TypeError: Cannot read properties of undefined (reading 'getColumn') What is the problem?
So it looks like you have a typo wherever you have .getColumn() in the script. Do you have something like this in the script? let range = e.range; let col = range.getColumn();
@@SheetsNinja Yes, these two lines are there. If you have time .. I want to share the file with you and see the problem .. give me your email Please
Thank you very much I figured out where the problem was. It works great 🤩👍👍👍
Thank you! Great instruction
Awesome! So glad this was helpful!
Maybe a stupid question, but if I share this document to another person, will it run this function on theirs?
I just made another video with a simpler version of this script that doesn't require authorization--meaning it will automatically work for anyone who opens the Google Sheet: ua-cam.com/video/i-qd8vXUoEA/v-deo.html
This video just saved me alot of stress.. Thank you.. I have liked and subscribed😊
Awesome! Thank you!
Thanks for your great tutorial! It's really helpful! By the way it doesn't work on Android phones, right? What can we do for Android phones?
I just made a new version of this video using an onEdit script, which makes this functionality available for mobile phones or tablets: ua-cam.com/video/K5uOSI5i8ME/v-deo.html
I love you bro... very muchhhh😂😂😂
I'd hate to be your coworker at the moment! 😉
Hi - thanks for this video. Is there any way you can automate messages? I want a team member to receive the messages from fiverr and respond back without sharing the fiverr id and password. Is that possible, if yes, how?
I don't think there's a way to reply to Fiverr messages without logging into Fiverr. You could potentially set up a way to forward the body of Fiverr emails but that still wouldn't give your team member to reply to the email directly
Hands down! Super Ninja......only a few steps now I have a free tool to apply in my daily pro life. I REALLY APPRECIATE IT!
Awesome! So glad this was helpful!
Hi do you also have P&L for many store branch and with one master file
Hey, so you can search for my video on importrange and that shows you how to take data from one Google Sheets and display it in another so you can combine or compare.
Hi! do you have also bookkeeping video's?
Hey, sorry, I'm not a bookkeeper so I don't have any specific for bookkeeping.
Hi! i don't have any accounting experience but my boss is asking me to do a report with P&L and I've been searching for a guideline that led me to you video. thank you
Awesome, so glad this was helpful!
is there a way to select multiple names at once on google forms?
You can use checkboxes to select multiple items or names in Google Forms.
Hello. Thank you for this video. Update: Your script DOES run great. Thank you. There was a huge (1/2 hour or more) delay in it starting to work on my sheet, which is why I thought it didn't work. But, viola, it suddenly started worked much to my surprise. Thank you.
So this script isn't meant to run from the script editor, the onEdit trigger runs whenever the Google Sheet is modified. So if you make an edit in the Google sheet, it should run successfully.
@@SheetsNinja Wow, thank you for the quick reply. When I go to the sheet and make an edit, the updated field remains blank. Would it make any difference if the sheet has multiple tabs, as mine does? Thank you again for this help. I really appreciate it.
Ciao, molto utile il video, grazie. Ho una domanda, questa funzione "sostituisce" la funzione nativa della condivisione del file?
I'm not sure what you mean by substituting or replacing the native function. You can use this method instead of the native sharing function, or you can use it alongside the native sharing. It does not disable the share button, and you can still manage access completely separately from this method. The script only adds or removes emails in the tab, so it won't affect anything if you have separately added other people to the Google Sheet.
@@SheetsNinja Ok, thanks. All clear, thank you very much for your work, the videos are very helpful!
What should I do if I want to show present and absent? Thank you for your kind help.
If you want to show "Present" or "Absent" instead of a number, all you have to do is where it currently has a countifs to count if they show up on a day, you can put that into an if statement. E.g.: =IF(countifs('Form Responses 1'!$B:$B,$C7,'Form Responses 1'!$A:$A,">="&D$6,'Form Responses 1'!$A:$A,"<"&D$6+1)=1,"Present,"Absent") So basically if it finds that name on a certain day, it returns "Present", otherwise "Absent".
@@SheetsNinja I was trying to freeze up to column C for easier scrolling, but I continue to get error message saying merged cells. Could you please help. Thanks so much
@@helenat7468 This one is a little confusing, but you can't freeze columns or rows if you have any merged cells that merge across where you want to freeze. So for example, on your sheet, you have one or more cells that are merged starting in A, B, or C and going to D or beyond. You'll just have to look through and unmerge any rows that do that so you can freeze where you need to
very helpfull
Awesome! So glad this was helpful!
This is great! What do I need to remove if I don't want any data from the columns pulling into the email, just the notification email sent?
You can just type what you want in the htmlBody part of the sendEmail section, and don't need to include any of the template literals: ${}
I realize I'm super late to the video on this one but I am hoping you can help me, I've tried fixing it myself and I've tried looking through other comments but I cannot find my specific issue / need. I just need a single column to make updates to the timestamp. I want changes I make in col 2 to put a timestamp in col 5 and I want it to ignore col 1,3,4, and anything over 5. Please and thank you for any assistance you might provide.
All you need to do is put "col == 2" in the if statement. So something like this: if (col == 2) { sheet.getRange(row,5).set value(today); }
@@SheetsNinja sorry I'm terrible at all this, would I still need to specify that it's sheet1 somewhere? Or do I just replace the entire if statement with what you said
@@zackreynolds thank you that worked. I thought that might work before but I didn't do == in my troubleshooting I only did =
So in the example if statement, I exclude a column with "col != 12", so if you just replace that with "col == 2" then it will work the way you're hoping.
@@SheetsNinja thank you so much for the help!
Really good method. However, I noticed that if I add a row in the middle of the table, all the dropdown validations are shifted. E.g say I added a row 20, then the dropdown datavalidation will be looking at the dropdown sheet at row 19.
Yes, unfortunately with this hack, it does mess it up if you add new rows in the sheet. The only way to compensate for this is going to a scripted method. Good news is I have a great tutorial on a couple ways to do that here: ua-cam.com/video/5Yysv-QouTQ/v-deo.html