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! 👍🏻
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.
@@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.
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!
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
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
Simply put, your channel is GOLD! For the defined range to clear, is it possible to select certain cell ranges from different columns? e.g. C6:C14, E6:E14, G4:G16? Thank you in advance!
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?
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?
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.
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.
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.
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
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?
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.
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
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); } }
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.
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?
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.
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.
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
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); }
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.
![เป็นไปได้ไหม - WanMai [Performance Video]](http://i.ytimg.com/vi/Eh5hItD4Mr0/mqdefault.jpg)
Best google teacher on youtube!
Best Google Sheets teacher in the World
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! 👍🏻
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.
@@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.
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!
Thanks!
Thank you very, very much! I was able to accomplish my task within 15 minutes of watching your video!
You and your work are awesome
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
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.
Thanks for this awesome tutorial. I always check this channel If I need any help.
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?
Wow. Great tip!
Yes!!!! Thank you!!! Again, you're a life saver!! ❤️❤️❤️
great video thanks, very creative but simple solution using find and replace
Thank you. Keep them coming.
Thank you so much for showing me how to do this. I used the Find And Replace method. Woohoo!
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
Mind blown. This is awesome! Now I would love to learn how to keep those formulas and perform a row insert! :D
Yes, I'm looking for the exact same operation. I hope someone from up above hears our plea. 🙏
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
@@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
Thank you for your excellent teaching - you're very clear and I can follow what and why. Very thankful for your help.
Awesome Tutorial. Thank you so much. I subscribed
👍
Very helpful!
Thaaaaaaanks
Thank you!
Simply put, your channel is GOLD!
For the defined range to clear, is it possible to select certain cell ranges from different columns? e.g. C6:C14, E6:E14, G4:G16?
Thank you in advance!
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?
Thank you. saved me hours
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?
thank you so much, that was awesome
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.
How can i add multiple ranges? I have different tables on one sheet. i want to keep the headers thought and delete all the info inside the tables
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.
excelente!
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.
thank you so much really its will save my time
Perfect!
Thank you for this!
:)
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
Thank you.
Thank you
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?
Thanks! That helped a lot
why i cant find the script editor on my google sheets? does it need a subscription?
under Extensions->Apps Script now
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.
You could use Conditional formatting ~ if the value is equal to zero then the text color is white
Hello sir? is it possible to hide not all formulas but some in one cell?
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?
I don't have a video covering that particular thing.
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
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);
}
}
Hi! How do you add a default text on a dropdown cell after clearing that cell?
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.
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.
Form ID is in the link if you open the form.
@@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?
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.
Hidden Formulas in Excel show in Google sheets
How to hide formula in Google sheets... ?
Plz reply
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.
I don't understand how that happens. I'm missing something from your workflow.
How could I do this across 2 tabs, same range?
Either do each tab separately or use a macro
Truly astounding that this isn't a built-in function and it requires a 5+ minute video to remedy.
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!
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
getRange not getRang
when i click run it says ReferenceError: ws i not defined. i copied your steps very precisely. it does say deploy at the top
Is this really the simplest way???
this is great , but is it possible to clear content without removing all the checkboxes
You can't clear, but you can uncheck all of them th-cam.com/video/ax3K-L-uM2I/w-d-xo.html
Why not just protect the cells to lock the formulas/ cells and then highlight page delete
Sir how to automates what's app with Google sheet
You'll need to find Whatsapp API access. I don't use or work with whatsapp, so I don't know.
@@ExcelGoogleSheets ok sir thanks you for information 👍🙏🙏🙏🙏
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 about many sheets?
th-cam.com/video/V3o2Idw2MWE/w-d-xo.html
i'll just do it manually for now lol
ARE YOU KIDDING ME??? WHAT THE HECK WAS GOOGLE THINKING??? ADD THIS FUNCTIONALITY NOW. WHAT A JOKE!!!
Help?! It deleted my words also...
This will clear everything except formulas.
You can use File-Version History to recover your data.
I need help with setting up a cash sheet with a specific formula anyone that has good knowledge would like to help me. Thankkksss!!!!!
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);
}
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.