Google Sheets - Clear Contents, Keep Formulas

[@sherblang1472]

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! 👍🏻

[@ExcelGoogleSheets]

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.

[@m0hal0nka]

@@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.

[@jordancote7972]

Best google teacher on youtube!

[@nishantagarwal5165]

Best Google Sheets teacher in the World

[@vegas1961]

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!

[@vinodkumarPrajapativnd]

That script part is magic. Just get and set that's it.

[@mdamirhossain9376]

Thanks for this awesome tutorial. I always check this channel If I need any help.

[@brianmorrison9168]

I'll stick with the first method ... easy ! Many thanks

[@nurulalhadad7574]

Like your contents, explained step by step, its easy to understand

[@nomeshwersharma2570]

You and your work are awesome

[@profocelsomarin4123]

excelente!

[@ghandimauler]

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

[@ZetaCheese]

great video thanks, very creative but simple solution using find and replace

[@gregoryunderwood4121]

Yes!!!! Thank you!!! Again, you're a life saver!! ❤️❤️❤️

[@STJANET3224GAMING]

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.

[@BettieJane]

Mind blown. This is awesome! Now I would love to learn how to keep those formulas and perform a row insert! :D

[@professorlanguagepartners3112]

Yes, I'm looking for the exact same operation. I hope someone from up above hears our plea. 🙏

[@rOCKER6930]

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

[@professorlanguagepartners3112]

@@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

[@SandyBoone]

Thank you for your excellent teaching - you're very clear and I can follow what and why. Very thankful for your help.

[@pabami34]

Wow. Great tip!

[@deals4platter]

Thank you. Keep them coming.

[@msshannypooable]

Thank you.

[@mattwall1189]

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]

👍

[@AAAnastasia27]

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-gt9bp7zz2p]

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?

[@MrEffectual]

Thank you. saved me hours

[@emilianoreyes]

Thank you!

[@raulbezerra1929]

Perfect!

[@MonjiGhanem]

Very helpful! Thaaaaaaanks

[@ometkumarkundu4947]

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.raetai]

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

[@townssanford1096]

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

[@abdullahquhtani4247]

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.

[@peaceloveabundanceyogawith1182]

Thank you for this!

[@ExcelGoogleSheets]

:)

[@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.

[@AmandavilleTV]

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.

[@jasonwessel6379]

You could use Conditional formatting ~ if the value is equal to zero then the text color is white

[@Yakub64Games]

Thanks! That helped a lot

[@Sam-007]

Hidden Formulas in Excel show in Google sheets How to hide formula in Google sheets... ? Plz reply

[@dougcrossley]

Is this really the simplest way???

[@gonefishing2006]

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.

[@samintino2000]

when i click run it says ReferenceError: ws i not defined. i copied your steps very precisely. it does say deploy at the top

[@zongariyaporn3763]

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?

[@ExcelGoogleSheets]

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.

[@venusmirabueno1643]

Hello sir? is it possible to hide not all formulas but some in one cell?

[@coreymccall2963]

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.

[@ExcelGoogleSheets]

Form ID is in the link if you open the form.

[@coreymccall2963]

@@ExcelGoogleSheets Okay Thank you I will look for it and what about my questions about the code?

[@shrimpfarming5257]

i'll just do it manually for now lol

[@littlenose4085]

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.

[@ExcelGoogleSheets]

I don't understand how that happens. I'm missing something from your workflow.

[@justbie9948]

why i cant find the script editor on my google sheets? does it need a subscription?

[@ExcelGoogleSheets]

under Extensions->Apps Script now

[@UXSpecialist]

ARE YOU KIDDING ME??? WHAT THE HECK WAS GOOGLE THINKING??? ADD THIS FUNCTIONALITY NOW. WHAT A JOKE!!!

[@tyleredwards2097]

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

[@ExcelGoogleSheets]

getRange not getRang

[@BustaCap]

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?

[@ExcelGoogleSheets]

I don't have a video covering that particular thing.

[@tomsawkins9828]

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

[@tomsawkins9828]

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); } }

[@bonishimori301]

Hi! How do you add a default text on a dropdown cell after clearing that cell?

[@sharitambasco9780]

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.

[@donavonlewis1039]

How could I do this across 2 tabs, same range?

[@ExcelGoogleSheets]

Either do each tab separately or use a macro

[@ishanharshvardhan6687]

this is great , but is it possible to clear content without removing all the checkboxes

[@ExcelGoogleSheets]

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!!!!!

[@warehouse1holitech451]

Sir how to automates what's app with Google sheet

[@ExcelGoogleSheets]

You'll need to find Whatsapp API access. I don't use or work with whatsapp, so I don't know.

[@warehouse1holitech451]

@@ExcelGoogleSheets ok sir thanks you for information 👍🙏🙏🙏🙏

[@leemunhwaikpm-guru6143]

how about many sheets?

[@ExcelGoogleSheets]

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.

[@allypreeves]

THIS DOESN'T WORK PLEASE HELP MEEEEE

[@JamesMcCabe703]

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); }

[@ExcelGoogleSheets]

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.

[@thailandlocal7828]

Hard to follow