Triggering script by specific cell value

问题:

I am pretty new to google sheets script development and am wondering how to trigger a clearAll script with cell value i.e. A1=100. My clearAll script works (see below), though I don't know what to add to it to trigger it using a specific cell value.

function clearAll() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var formresponses1 = ss.getSheetByName("formresponses1");
formresponses1.clearContents();}

Thanks

回答1:

If you are trying to make it so that whenever somebody puts in the value of "100", it clears the content of the entire sheet, then you can do this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActive()          //gets the active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet()  //gets the active sheet
  var cell = ss.getActiveRange()               //gets the active cell
  var cellContent = cell.getValue()            //gets the value of the active cell

  if(cellContent === 100) {
    sheet.clearContents()                      //clears the values of the entire active sheet
  }
}

If you want to make it so that whenever somebody edits the cell and makes its value "100", the code clears only that cell, then do this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActive()
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = ss.getActiveRange()
  var cellContent = cell.getValue()

  if(cellContent === 100) {
    cell.setValue("")              //clears the value of the active cell
  }
}

Of course, in the latter, due to the slight lag in Google scripts, if somebody rapidly puts in 100 in every cell they can, then some of the cells with a value of "100" will stay there, but if the person is putting the values in like a normal person rather than a spammer, then this code will work.

Also, if you are trying to make it so that if the value of a certain cell (ie: A1) is equal to "100," the script clears the entire sheet, do this:

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

  if(cellContent === 100) {
    sheet.clearContents()
  }
}

Hope I could help!

回答2:

The onEdit trigger runs when any cell in the spreadsheet is edited.

Google Documentation - Spreadsheet On Edit

There is also a change trigger. It is an installable trigger, not a simple trigger.

Available types of triggers

Quote from documentation:

An installable change trigger runs when a user modifies the structure of a spreadsheet itself — for example, by adding a new sheet or removing a column.

I think the only thing that will work for you is the On Edit trigger. The trigger gets set from the Resources menu in the Apps Script Code editor.

I don't think you can restrict the code from running only to a certain cell, or a certain value within a cell. The code will run every time you edit ANY cell.

I think that the only other alternative would be to run a time based trigger, and have the script get the value of that cell, and check the value. The shortest time interval you can use is to run a script every minute. So if you edited the cell on second 1, it would take 59 more seconds before anything happened.

If you had some type of user interface, and the value in that cell was written to when the user entered a value in an input field, you could detect that change immediately, and make something happen.