May 9, 2019

I need a script to clear certain cells in sheets when a button is pressed

I have been going through the tutorials and other similar questions but I am unable to get a script to work.  I am needing a script that clears certain cells when a button is pressed.  In my case they are cells B5, B7, B9, B11, B15, and B19.  I have been trying for days to write a script but I cannot get anything to work.  I am new to this script writing thing and I have to say that I don't fully understand it which I know is a large part of the problem.  If someone out there can help I would greatly appreciate it.  I have a test copy of my sheet here if someone needs to look at it:  I would appreciate any tips or guidance.

This is the script that I found that I was basing my script on.

function clearRange() { //replace 'Sheet1' with your actual sheet name var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); sheet.getRange('B7:G7').clearContent(); }

(I assume that the text following the // is to be removed fromthe script as it is just directions?)
Locked
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Google user
Feb 25, 2020
Hello all, I apologize if this seems like a dumb answer because I know absolutely nothing about coding and very little about sheets, but I was able to find a solution that may be a bit easier then trying to create your own code potentially. so I dabbled around with all the answers posted here and could not for the life of me get the script editor to actually perform said script in my actual google sheets and had no way of transferring it to the sheet I was working on so I fiddled around with the record macro. I simply clicked record macro and then manually cleared out the cells I wanted to delete and then saved. Once I saved I then drew a crude button like image and assigned the macro to it by selecting assign script and then typing in the name I gave the macro......and well saved then clicked the crudely drawn button and presto! it cleared all the cells. Maybe this might be an easier solution to what you all are looking for possibly?
Gold Product Expert Alex Ivanov ✔ recommended this
Helpful?
Recommended Answer
Hi Brad!
 
Try my code 
 
function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Reset sheet')
  .addItem('Based on a permanent ranges address list', 'userActionResetByRangesAddresses')
  .addToUi();
}
function userActionResetByRangesAddresses(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangesAddressesList = ['B5', 'B7', 'B9', 'B11', 'B15', 'B19'];
  resetByRangesList_(sheet, rangesAddressesList);
}
function resetByRangesList_(sheet, rangesAddressesList){
  sheet.getRangeList(rangesAddressesList).clearContent();
}
Get down ☟ the topic and you'll see ton of other samples for this case.
Last edited Nov 15, 2019
Original Poster Brad Rohr marked this as an answer
Helpful?
All Replies (63)
Recommended Answer
May 10, 2019
Hi Brad!
 
Try my code 
 
function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Reset sheet')
  .addItem('Based on a permanent ranges address list', 'userActionResetByRangesAddresses')
  .addToUi();
}
function userActionResetByRangesAddresses(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangesAddressesList = ['B5', 'B7', 'B9', 'B11', 'B15', 'B19'];
  resetByRangesList_(sheet, rangesAddressesList);
}
function resetByRangesList_(sheet, rangesAddressesList){
  sheet.getRangeList(rangesAddressesList).clearContent();
}
Get down ☟ the topic and you'll see ton of other samples for this case.
Last edited Nov 15, 2019
Original Poster Brad Rohr marked this as an answer
May 4, 2020
https://docs.google.com/spreadsheets/d/1x2WwJSXBpPyQVL8X9iV8sMxgmzqYKbWbdT9dLW20uNk/edit?usp=sharing
Is it possible to create a button when we clicked on it, it will generate a new empty weekly schedule and the following week?
May 13, 2020
Hi Guys, 
I want to clear specific columns every time i run my script. I have been using the following; but it doesn't seem to work. Was wondering if anyone can help with this?

function main() {

   var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1HjrfwhUzsyE9mCn1meCmMU12OEOSZEqVu-Pnls0Ykjs/edit#gid=0');
 
  //Getting the sheet associated to my spreadhseet
  var sheet = spreadsheet.getSheetByName('ZebraRaw');
  
  //clear contents in my spreadsheet
  var app = SpreadsheetApp
  var activesheet = app.getActiveSpreadsheet().getActiveSheet()
  activesheet.getRange("A1:E10000").clearContent();
  
  //Pull a report
  var report = AdsApp.report(
    'SELECT CampaignName, Conversions, Date, Impressions, Cost ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    ' WHERE Cost > 0 ' +
    'DURING 20200401, 20200630 '
  );
  
  //Export To Sheet
  report.exportToSheet(sheet)
    
  }
May 17, 2020
Hello guys, I need something similar to this, but I need it for using it in google spreadsheet app on mobile phones, can someone help me? 
Basically, I need to run some code after user clicks a button or something similar to that.
Thanks!
German
May 20, 2020
Is it possible to have a button to reset an entire workbook for unlocked cells?
Jun 5, 2020
How to reset it to zero?
Hi Silvia Eleonora
 
Unfrtunately it's not possible now.
Hi User 2990053860827383205
 
Is there any data sample for us?
Jun 7, 2020
already managed it using your sample with reset to value script.
Jul 2, 2020
HI Mate!

I got the job done with this:

function clear() {
  var app = SpreadsheetApp;
  var copySheet = app.getActiveSpreadsheet().getActiveSheet();
    var clearIt = copySheet.getRange(6,5,15,1).clearContent();
    var clearIt = copySheet.getRange(23,5,15,1).clearContent();
    var clearIt = copySheet.getRange(40,5,15,1).clearContent();
    var clearIt = copySheet.getRange(57,5,15,1).clearContent();
    var clearIt = copySheet.getRange(74,5,15,1).clearContent();
    var clearIt = copySheet.getRange(91,5,15,1).clearContent();
    var clearIt = copySheet.getRange(108,5,15,1).clearContent();
    var clearIt = copySheet.getRange(125,5,15,1).clearContent();
    var clearIt = copySheet.getRange(142,5,15,1).clearContent();
    var clearIt = copySheet.getRange(159,5,15,1).clearContent();
    var clearIt = copySheet.getRange(176,5,15,1).clearContent();
    var clearIt = copySheet.getRange(193,5,15,1).clearContent();
    var clearIt = copySheet.getRange(210,5,15,1).clearContent();
    var clearIt = copySheet.getRange(227,5,15,1).clearContent();
    var clearIt = copySheet.getRange(244,5,15,1).clearContent();
//the range correspond to (row,column,number of rows,number of columns)
}
Jul 27, 2020
How are you all getting past this? Each time I try to run a script I get this!!!
Aug 21, 2020
Hello guys. 
Need help to change this script to work on a Button upon click. Can someone help?
Thanks in advance.

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Main" && r.getColumn() == 7 && r.getValue() == "Completed") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}
Last edited Aug 21, 2020
false
103143511600443082
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false
false