applying ClearContents function based on other cell value

Question

I have a google sheet that is used everyday by many people and that sheet must have "restart" and "backed up" every end of the day. Until now I did it using ClearRange function and MakeCopy function which saved the whole file on another google sheet folder of mine. These functions are working on a time based trigger that I find not relevant for me anymore and I want to activate them by an OnEdit function that will be based on cell value and kind of create "menu" that will work also for Android users.

This is what I did till now based on time trigger:

function clearRange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('work_sheet');
  sheet.getRange('A5:L200').clearContent();
  sheet.getRange('O5:P200').clearContent();
}



function makeCopy() {
// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");

// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate
var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate;

// gets the destination folder by their ID. REPLACE xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your folder's ID that you can get by opening the folder in Google Drive and checking the URL in the browser's address bar
var destination = DriveApp.getFolderById("xxxxxxxxx(my google sheet folder URL)");

// gets the current Google Sheet file
var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())

// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}

I found some codes that I tried to apply on my function but without a success:

var FunctionsCell = "B2"  // global
function onEdit(e) {    
  var editCell = e.range.getA1Notation()

switch (editCell) {
case FunctionsCell: {               
      var functionType = SpreadsheetApp.getActiveSheet().getRange(FunctionsCell).getValue()

      switch(functionType) {              
        case "Do Task 1": {
          // do something
          break
        }
        case "Do Task 2": {
          // do something
          break
        }
      }
  }
  }
}

(on this one it give me error about the "var editCell = e.range.getA1Notation()"

and that one are working only when I'm not giving a specific range

function onEdit3(e) {
  var ss = SpreadsheetApp.getActive()
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = sheet.getRange('O1')
  var cellContent = cell.getValue()

  if(cellContent === 100) {
    sheet.getRange('A5:L10').clearContents()
  }
}

i would like it to be such a menu for android users to click on a option or to write a specific word to making these two function to work. thank you guys

Cooper Cooper · Accepted Answer · 2019-06-21T17:06:20

I would need considerably more detail to complete this. But this approach should work. Remember you cannot call these functions from the script editor or launch them from a menu. You must actually run them with an onEdit() trigger inorder for the event object to be present. This sort of debugging can be difficult and will often result in improving your trouble shooting skills or possibly just make you give up.

function onEdit(e) {    
  var sh=e.range.getSheet();
  var name=sh.getName();
  if(name !='Sheet1') return;//limits the functionality to one sheet
  switch (e.range.getA1Notation()) {
    case 'B2': {               
      var functionType = e.range.getSheet().getRange("B2").getValue()
      switch(functionType) {              
        case "task1": {
          // do something
          break
        }
        case "task2": {
          // do something
          break;
        }
      }
    }
  }
}

applying ClearContents function based on other cell value

1 Answers