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!

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

3 жыл бұрын

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

8 ай бұрын

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

Best google teacher on youtube!

@nishantagarwal5165

3 жыл бұрын

Best Google Sheets teacher in the World

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

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

Thank you so much for showing me how to do this. I used the Find And Replace method. Woohoo!

Wow. Great tip!

Thank you. Keep them coming.

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

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

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

Thanks! That helped a lot

You and your work are awesome

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

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

Perfect!

Thank you!

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.

thank you so much, that was awesome

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

thank you so much really its will save my time

Very helpful! Thaaaaaaanks

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?

Thank you. saved me hours

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

@professorlanguagepartners3112

3 жыл бұрын

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

@rOCKER6930

2 жыл бұрын

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

2 жыл бұрын

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

excelente!

Thank you

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.

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.

Thank you for this!

@ExcelGoogleSheets

2 жыл бұрын

:)

Thank you.

Awesome Tutorial. Thank you so much. I subscribed

@ExcelGoogleSheets

Жыл бұрын

👍

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?

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.

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?

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?

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

3 жыл бұрын

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

@coreymccall2963

3 жыл бұрын

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

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

2 жыл бұрын

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.

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!

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

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.

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

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.

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

@sharitambasco9780

3 жыл бұрын

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.

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

2 жыл бұрын

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

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

3 жыл бұрын

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

@tomsawkins9828

3 жыл бұрын

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

3 жыл бұрын

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

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

i'll just do it manually for now lol

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

Is this really the simplest way???

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

2 жыл бұрын

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

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

@ExcelGoogleSheets

2 жыл бұрын

under Extensions->Apps Script now

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

@ExcelGoogleSheets

2 жыл бұрын

Either do each tab separately or use a macro

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

3 жыл бұрын

getRange not getRang

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

@ExcelGoogleSheets

2 жыл бұрын

You can't clear, but you can uncheck all of them kzread.info/dash/bejne/k6xnrY-FXdizYqw.html

Sir how to automates what's app with Google sheet

@ExcelGoogleSheets

3 жыл бұрын

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

@warehouse1holitech451

3 жыл бұрын

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

how about many sheets?

@ExcelGoogleSheets

3 жыл бұрын

kzread.info/dash/bejne/iGejlKudp5Wzh6g.html

I need help with setting up a cash sheet with a specific formula anyone that has good knowledge would like to help me. Thankkksss!!!!!

Help?! It deleted my words also...

@ExcelGoogleSheets

Жыл бұрын

This will clear everything except formulas. You can use File-Version History to recover your data.

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

THIS DOESN'T WORK PLEASE HELP MEEEEE

Hard to follow

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

2 жыл бұрын

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.