Clear all column's data

[Herbert_Coleman]

One more question is there a way to select a button that would go to a sheet and clear the data from all columns? I realize this could be very dangerous if you make a mistake when setting up you could destroy your sheet. However, once you have the correct sheet selected, I’d like to be able to clear all the data from the answer sheet to make it ready for the next test. Of course, I need to leave the column headings alone; just clear out all the data below.

[Jeff_Hager]

This would probably require some sort of script set up with an onChange trigger. When you set a value somewhere in your app and it updates the sheet, then the trigger will run and can spin through a sheet you specify and clear out the cells you want in that sheet. Then the script would finally clear out the value that you set to trigger the process.

[Herbert_Coleman]

So I would want something like onChange

select sheet !Tests
select A2:M
perform function Edit menu:Delete values
end selection

Is the right logic (I’ll have to study the syntax)?
I ran a macro to clear the selection to get the syntax.
I guess now I need to figure out how to make it trigger on when a specific string is entered into a specific cell.

[Jeff_Hager]

Here’s a handful of posts that might help. As far as the trigger, be sure to look for this icon when writing the script. This allows you to set up a trigger. Just make sure it’s an onChange trigger.

Trigger script onEdit event from App

function trailerDelete(e) { //This is the onChange(e) trigger var app = SpreadsheetApp; var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var targetSheet = app.getActiveSpreadsheet().getSheetByName(“Report”); // You can replace “Report” in the above line, to the sheet that you are referencing. //Logger.log(targetSheet.getRange(2, 2).getValue() + " Test"); if (e.changeType==“EDIT”){ for (var i = 2; i < 6;i++){ if (targetSheet.getRange(i,2).getValue() ==""){ targetSheet.…

Reset values daily

The only recommendation would be to look at a bunch of script writing tutorials and write one. Then 2, then 3, etc… Then keep writing them until you are confident that it does what you want. To simply clear a few columns is pretty straight forward. If you wanted to clear the contents of columns E and F on Sheet2, this is the script. function clearAFewColumns() { var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2'); var lastRow = sheet.getLastRow(); // Column E = 5; F = 6 …

Clear cell after set time

This is the script I use: function clearRange() { //replace 'Sheet1' with your actual sheet name //replace 'Range' with the range you want to clear var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1); sheet.getRange('Range').clearContent(); } [Screen Shot 2020-04-02 at 9.42.59 PM]

Option to delete all child-items

When you get into the script editor one of the icons is a picture of a clock. That is where you would set up the timed trigger. I lied, the code is actually 44 lines of code but I did throw in some comments to hopefully make it easier to follow. I did use arrays as that is the fastest way to scan through data and write to a sheet in mass. So basically you would set up a trigger to run this code, maybe nightly to clean up the orphan’d children. Here is the sheet. Make a copy of it and then t…

[ToOFa_Apps]

Is there a better way to clear one full column than with scripts?

This post is 2 years ago which is like a thousand Glide “dog-years” (I think glide adds new features by the hour🤣)
Really need to clear one whole column in one click.

[ToOFa_Apps]

I managed to use the Multiple Row Reset Concept App for some ideas.
Definitely does the trick for now. Bit of a workaround but at least i don’t have to use scripting etc.

How to clear whole column?

I managed to use the Multiple Row Reset Concept App for some ideas. Definitely does the trick for now. Bit of a workaround but at least i don’t have to use scripting etc.

[Herbert_Coleman]

Working on it off and on (mostly off) for 2 years, I finally have success. I created a macro to delete rows 2-200. Then I created a column and cell for my trigger word. I created a button to enter the trigger word. Finally, set up the “on change” trigger. The clearance doesn’t happen until the trigger word is entered and there’s a change. I also used the get.SheetByName function to prevent accidental erasure of the wrong page. Thanks for everyone’s input.

[Darren_Murphy]

This is what I use:

function reset_sheet(sheetname) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);

  var lastrow = sheet.getLastRow();
  var lastcol = sheet.getLastColumn();
  sheet.getRange(2, 1, lastrow, lastcol).clear();
  sheet.getBandings().forEach(function (banding) {
    banding.remove();
  });
  var last_used_row = sheet.getMaxRows() - 3;
  if (last_used_row > 10) {
    sheet.deleteRows(3, last_used_row);
  }
}

The above will clear all data, leaving the header row intact, plus two empty rows.
It also removes any banding (alternate row highlighting).

[Herbert_Coleman]

Here’s how I solved it. Keep in mind I have a few goals. I don’t want my customers to ever have to visit the spreadsheet. This gives them the ability to update everything from the app. So first I recorded a simple macro of me deleting 200 rows while keeping the header row. Then I added a column with the header “Clear” for the trigger word (“ClearAll”). Then I modified the macro to only run on this tab when the trigger word was in the cell and to replace the trigger word when it was done with “Running”. Then I set the macro to trigger on change. Finally, I created a button in the admin panel of the app to change the trigger word to “ClearAll”.

Here’s the macro

/** @OnlyCurrentDoc */

function ClearTest() {

var valueactivecell =SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’tab’).getRange(‘cell’).getValue();

if(valueactivecell == ‘ClearAll’)

var spreadsheet =SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’tab’);

spreadsheet.getRange(‘2:200’).activate();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

spreadsheet.getRange(‘cell’).activate().setValue(‘Running’);

}

[Darren_Murphy]

@Herbert_Coleman

var valueactivecell =SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’tab’).getRange(‘cell’).getValue();

A couple of tips…

You have multiple calls to SpreadsheetApp.getActiveSpreadsheet(), which is wasteful. Better to call that once at the start and store the Spreadsheet object in a variable…

var ss = SpreadsheetApp.getActiveSpreadsheet();

Then you can just refer to ss in subsequent lines.

Also, if you use a Named Range for your trigger cell, it’s much easier to get/set the values in that cell. For example…

To get the cell value:

var valueactivecell = ss.getRangeByName('cell').getValue();

And then to set the value:

ss.getRangeByName('cell').setValue('Running');

Also, you are only ever clearing 200 rows. What happens if there are 250 rows, or 300? Does that matter?
You can find the last used row in the sheet with: spreadsheet.getLastRow();
If you want to select everything excluding the header row, you can do something like:
var wholeRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())

[Herbert_Coleman]

I’m sure it prolly wasn’t clear but “tab” and “cell” were actually placeholders for the names of the tab [‘Test’] and the reference for the cell [M2’]. Not sure if that marks a diff. for RangeByName or not. I thought about the multiple calls but since it was only a few lines, I wasn’t too concerned. I was just really happy to get it working. I may do your suggested clean-up before releasing it. To be honest I never have more than 125 rows of data. The 200 was to cover the worse possible cases. I could do the whole range but I didn’t want to waste time with extra selection. Once again, might clean up in the final edition. Thanks for the suggestions.