Clear all column's data

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.

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.

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.

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.

image

1 Like

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.

2 Likes

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.

1 Like

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.

1 Like

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

1 Like

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

}

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())

1 Like

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.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.