Seriously?!?!? 🤦🏻♂️I spent about 7 hours last week trying to code multiple functions to clear sections of sheets, but by the time it was all done it was so slow I ended up using a "hidden sheet" that was a "blank" copy (including formulas) and used "copyTo". Not an elegant solution, but it got the job done. And in less than 4 minutes you demonstrate why I really should just keep coding a hobby. Thanks for these videos - very helpful! 👍🏻
@ExcelGoogleSheets4 жыл бұрын
Something that's a hobby today may become a profession tomorrow. If you have the patience to sit for hours and figure things out, then you have the personality to become a programmer. When I see the code I wrote 4 years ago it makes me want to vomit.
@m0hal0nka9 ай бұрын
@@ExcelGoogleSheets I love how you inspired that person. Thank you very Much. And for the record, Your videos helped me creating a very complex inventory management for my business without really using any codes! Keep up the good work. May almighty guide us to the right path.
@jordancote79724 жыл бұрын
Best google teacher on youtube!
@nishantagarwal51654 жыл бұрын
Best Google Sheets teacher in the World
@vegas19612 жыл бұрын
You're really great at teaching. I love how you give more than one way to accomplish the same thing so we can choose what works best with our abilities. I saved this for future use as I'm only working on an Android tablet and find and replace will not let me search formulas (no check boxes for search within formulas...actually no check box choices at all...just a bare bones f&r). Thanks again. All of your videos are so helpful. The best I've found anywhere on the web!
@lolo9553ifyАй бұрын
Thank you so much for showing me how to do this. I used the Find And Replace method. Woohoo!
@vinodkumarPrajapativnd4 жыл бұрын
That script part is magic. Just get and set that's it.
@mdamirhossain93764 жыл бұрын
Thanks for this awesome tutorial. I always check this channel If I need any help.
@brianmorrison91683 жыл бұрын
I'll stick with the first method ... easy ! Many thanks
@nurulalhadad75742 жыл бұрын
Like your contents, explained step by step, its easy to understand
@nomeshwersharma25703 жыл бұрын
You and your work are awesome
@profocelsomarin41239 ай бұрын
excelente!
@ghandimauler7 ай бұрын
Brilliant. Both solutions are good solutions. Well presented and something I wouldn't have immediately thought of a solution for. Sheets' UI seems to come up with some really good features while missing some fairly obvious ones. There's things I can do in Excel that are just head and shoulders than Sheets, but Sheets has some ideas that aren't obvious or possible in Excel. (I'd have thought by now they'd have stolen each other's UI features...) I was thinking "Well, what if it was text and if someone started their text with a =, then wouldn't that fail to clear?". I think the reason that doesn't happen (assuming it doesn't) is because a text field that starts with the = does not actually start with =.... it has to take the form of '= and the ' will be detected first instead of the =, so thus there is not a concern with a text field starting with =. I'm impressed by the creator of this video - clear and understandable, explained a somewhat arcane solution (two actually) but in a way that is consumable. +1, Useful +1, Well Presented
@ZetaCheese2 жыл бұрын
great video thanks, very creative but simple solution using find and replace
@gregoryunderwood41214 жыл бұрын
Yes!!!! Thank you!!! Again, you're a life saver!! ❤️❤️❤️
@STJANET3224GAMING8 ай бұрын
Hellooooo how would I do this to multiple ranges but want to skip certain ranges? Eg A1:B1 skip C1:D1 and include E1:F1?
@shauntaekelly6002 Жыл бұрын
Hello! How would I do this to multiple ranges but want to skip certain ranges? Ex. A1:B1 skip C1:D1 and include E1:F1.
@BettieJane4 жыл бұрын
Mind blown. This is awesome! Now I would love to learn how to keep those formulas and perform a row insert! :D
@professorlanguagepartners31123 жыл бұрын
Yes, I'm looking for the exact same operation. I hope someone from up above hears our plea. 🙏
@rOCKER69303 жыл бұрын
best way to do it is to use array formulas - the data range upon which the formual applies increases/decreases automatically when you insert/delete rows - and the formulas remain even after you clear contents as shown in this video
@professorlanguagepartners31123 жыл бұрын
@@rOCKER6930 Thanks for the reply. I'll try to use array formulas but I can't promise that I'll get it to work. :D
@SandyBoone2 жыл бұрын
Thank you for your excellent teaching - you're very clear and I can follow what and why. Very thankful for your help.
@pabami344 жыл бұрын
Wow. Great tip!
@deals4platter4 жыл бұрын
Thank you. Keep them coming.
@msshannypooable4 ай бұрын
Thank you.
@mattwall11892 жыл бұрын
I think they have updated the commands since you made this video. If you highlight the data, then press delete on a Windows machine, it clears the contents and leaves the formulas, formatting, and data validations.
@ivanurbina2422 Жыл бұрын
Awesome Tutorial. Thank you so much. I subscribed
@ExcelGoogleSheets Жыл бұрын
👍
@AAAnastasia277 ай бұрын
Is there a way to apply this to different tabs and also to get this to reoccur every week? And what if you have dropdown menus? How do you get those to reset?
@user-gt9bp7zz2p3 жыл бұрын
I'm using a spreadsheet with Google Forms. When new Google Form's data is entered, I want to implement the function of deleting (resetting) the remaining rows, // In conclusion, I want to keep only one row. but I don't know what to do. Can you give me some tips?
@MrEffectual2 жыл бұрын
Thank you. saved me hours
@emilianoreyes4 жыл бұрын
Thank you!
@raulbezerra19294 жыл бұрын
Perfect!
@MonjiGhanem3 жыл бұрын
Very helpful! Thaaaaaaanks
@ometkumarkundu49473 жыл бұрын
thank you so much really its will save my time
@coldato15 Жыл бұрын
This has been super helpful already. I have got this working on my sheet. HOWEVER, within the specified range, I have some text that I don't want to delete. Is there a way to get this to avoid clearing those cells as well? Thanks so much?
@pichit.raetai4 жыл бұрын
Thank you
@maricarpineda4592 Жыл бұрын
How can I delete multiple cells in different range while keeping the formula ??which I can skip few rows or columns for example A1 to b4 then at the same time I also wanted to delete d1 to f5 field using 1 app script please help me out
@townssanford10962 жыл бұрын
This didn't work for me at all. It is saying there is no functions to run. Any advice? Great video by the way. Your tutorials are great!
@htwrepublica17hoang60 Жыл бұрын
thank you so much, that was awesome
@abdullahquhtani42474 жыл бұрын
That will be great if you could ad confirmation message with Yes/No options and will be appreciated if there will be a static password with input box so we can protect the sheet and control un-Authorized users of clearing content.
@peaceloveabundanceyogawith11822 жыл бұрын
Thank you for this!
@ExcelGoogleSheets2 жыл бұрын
:)
@MortezaParkook Жыл бұрын
Thanks for the video, my google sheet doesn't have the Scrip Editor at all. Any idea how I can have it? I tired the Apps Script but I get a Bad Request Error 400.
@clarebrumby621 Жыл бұрын
Is there a way to do this for individual cells just occasionally - not the whole sheet, I have cells that require an input but also have a function. If the inputted number in that cell needs to be changed, the formula disappears.
@AmandavilleTV3 жыл бұрын
How do I remove the zeros in calculated cells without removing the formula? In other words, keep a formula cell blank until all the arguments are added.
@jasonwessel63792 жыл бұрын
You could use Conditional formatting ~ if the value is equal to zero then the text color is white
@Yakub64Games3 жыл бұрын
Thanks! That helped a lot
@Sam-0072 жыл бұрын
Hidden Formulas in Excel show in Google sheets How to hide formula in Google sheets... ? Plz reply
@dougcrossley2 жыл бұрын
Is this really the simplest way???
@gonefishing20063 жыл бұрын
So far thank you thank you thank you. ... oh .... and thank you. I tried to follow somebody else's script and for some reason failed badly.
@samintino20003 жыл бұрын
when i click run it says ReferenceError: ws i not defined. i copied your steps very precisely. it does say deploy at the top
@zongariyaporn37632 жыл бұрын
Thank you for this useful video! What I'm looking for is how to change text in a cell without affecting the formula in it? I'm trying to make a two-way calculation that if I fill a value in cell A, it'll calculate with another cell and the result will be in cell B. Now I want to be able to do the same with cell B where I can also fill a value in cell B and the result will be in cell A. But the problem is the formula in cell B will be gone if I replace it with a new value. Is there a way to make this happen?
@ExcelGoogleSheets2 жыл бұрын
Not possible with a formula. You could try to make something like this using onEdit scripts, but they don't work that fast, so its not exactly a great experience.
@venusmirabueno16433 жыл бұрын
Hello sir? is it possible to hide not all formulas but some in one cell?
@coreymccall29634 жыл бұрын
I applied the code and I was wondering where do I get the Google Form ID from? With the code, should I add it to the one that sends the email or should I just create a new script and set up a trigger for the response to show in the email? Or is there a way to code it into the HTML? Thank you, you've really been a great help to me.
@ExcelGoogleSheets4 жыл бұрын
Form ID is in the link if you open the form.
@coreymccall29634 жыл бұрын
@@ExcelGoogleSheets Okay Thank you I will look for it and what about my questions about the code?
@shrimpfarming52572 жыл бұрын
i'll just do it manually for now lol
@littlenose40852 жыл бұрын
How can I add the script to a button of a template sheet, without having the new sheets removing data from any other sheet than it's own? As for now, if I clear one sheet with my clear button, all of the sheets has their data removed.
@ExcelGoogleSheets2 жыл бұрын
I don't understand how that happens. I'm missing something from your workflow.
@justbie99482 жыл бұрын
why i cant find the script editor on my google sheets? does it need a subscription?
@ExcelGoogleSheets2 жыл бұрын
under Extensions->Apps Script now
@UXSpecialist2 жыл бұрын
ARE YOU KIDDING ME??? WHAT THE HECK WAS GOOGLE THINKING??? ADD THIS FUNCTIONALITY NOW. WHAT A JOKE!!!
@tyleredwards20973 жыл бұрын
function reset() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const ws = ss.getSheetByName('Calculator'); const rang = ws.getRang('A2:I31'); const formulas = rang.getFormulas(); //consol.log(formulas); rang.setFormulas(formulas); } This is how I have the script written, however I am still unable to 'TypeError: Cannot read property 'getRang' of null (line 5, file "Code")' is there something I am missing? please HALP
@ExcelGoogleSheets3 жыл бұрын
getRange not getRang
@BustaCap4 жыл бұрын
Do you have a video that’ll teach me how to move a row to another sheet based of a check mark or a key word in a cell?
@ExcelGoogleSheets4 жыл бұрын
I don't have a video covering that particular thing.
@tomsawkins98284 жыл бұрын
move data if key word function MoveCOLLECTED() { var ss=SpreadsheetApp.getActive(); var sh1=ss.getSheetByName("C&C Customer Collection Form"); var sh2=ss.getSheetByName("COLLECTED"); // the last number in the row below relates to copying 14 columns from 2nd number // first number = start at 4th row down // 2nd number = first column var rg1=sh1.getRange(4,1,sh1.getLastRow(),14);//starting at column2 var data=rg1.getValues(); for(var i=0;i
@tomsawkins98284 жыл бұрын
move data if checkbox(true condition) function OnEdit(e) { // assumes source data in sheet named main // target sheet of move to named Completed // getColumn with check-boxes is currently set to column I var ss = SpreadsheetApp.getActiveSpreadsheet(); var s = e.source.getActiveSheet(); var r = e.source.getActiveRange(); if(s.getName() == "ONLINERELOCATION" && r.getColumn() == 10 && r.getValue() == true) { var row = r.getRow(); var numColumns = s.getLastColumn(); var targetSS = SpreadsheetApp.openById(""); s = SpreadsheetApp.getActiveSheet(); var tempSheet = s.copyTo(targetSS); var targetSheet = targetSS.getSheetByName("SendToTotalSales"); var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); // row - whatever row was checked. 5 is 5th Column accross - 1 is 1 row - 3 copy 3 columns from the 5th tempSheet.getRange(row, 1, 1, 3).copyTo(target); } }
@bonishimori3013 жыл бұрын
Hi! How do you add a default text on a dropdown cell after clearing that cell?
@sharitambasco97803 жыл бұрын
You make the text a formula. eg if you want the cell to say "Choose an option", type in ="Choose an option" and now it won't be cleared.
@donavonlewis10392 жыл бұрын
How could I do this across 2 tabs, same range?
@ExcelGoogleSheets2 жыл бұрын
Either do each tab separately or use a macro
@ishanharshvardhan66872 жыл бұрын
this is great , but is it possible to clear content without removing all the checkboxes
@ExcelGoogleSheets2 жыл бұрын
You can't clear, but you can uncheck all of them kzbin.info/www/bejne/l6mWfGCCYtqAaKs
@haitoxtg3820 Жыл бұрын
I need help with setting up a cash sheet with a specific formula anyone that has good knowledge would like to help me. Thankkksss!!!!!
@warehouse1holitech4514 жыл бұрын
Sir how to automates what's app with Google sheet
@ExcelGoogleSheets4 жыл бұрын
You'll need to find Whatsapp API access. I don't use or work with whatsapp, so I don't know.
@warehouse1holitech4514 жыл бұрын
@@ExcelGoogleSheets ok sir thanks you for information 👍🙏🙏🙏🙏
@leemunhwaikpm-guru61433 жыл бұрын
how about many sheets?
@ExcelGoogleSheets3 жыл бұрын
kzbin.info/www/bejne/jGTSY3yarJeAjac
@alannamaniscalco154 Жыл бұрын
Help?! It deleted my words also...
@ExcelGoogleSheets Жыл бұрын
This will clear everything except formulas. You can use File-Version History to recover your data.
@allypreeves2 жыл бұрын
THIS DOESN'T WORK PLEASE HELP MEEEEE
@JamesMcCabe7032 жыл бұрын
Attempted this and getting a script error Error TypeError: Cannot read property 'getRange' of null ClearDataNotFormulas @ Code.gs:5 which points to line with my range: const range = ws.getRange("A2:AE22"); Any clues? Here's the entire script: function ClearDataNotFormulas() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const ws = ss.getSheetByName("June 2022"); const range = ws.getRange("A2:AE22"); const formulas = range.getFormulas(); // console.log(formulas); range.setFormulas(formulas); }
@ExcelGoogleSheets2 жыл бұрын
This line failed const ws = ss.getSheetByName("June 2022"); means June 2022 worksheet doesn't exist. Check the spelling and make sure your script is in the right spreadsheet.