Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.6k views
in Technique[技术] by (71.8m points)

google apps script - onEdit trigger for specific sheets in a spreadsheet?

I'm having trouble with finding good resources on how to setup onEdit triggers. I have a function that I only want to run when specific sheets are edited. For example say I have Sheet1, Sheet2, Sheet3, Sheet4, Sheet5. My script pulls data from sheets 2, 3, 4 and populates sheet 1. I only want my script ran when someone edits sheets 2, 3, or 4. How would I setup this trigger?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

There are multiple ways to accomplish this, each having its own merits. Do you have specific setup for each case? Do you run different functions for each case? etc.

  • Use an if statement to check the name for each sheet
    • if (name === "name 2" || name === "name 3" || ...) { /* common code */ ...
  • Use a switch statement with case fall-through (or no fall through, allowing specific setup based on the sheet)
    • switch (name) { case "name 2": ...
  • Use an array to hold the desired "valid" sheet names, and check if the edited sheet name is in the array with .indexOf()
    • var names = ["sheet 2 name", "sheet 3 name" ... ]; if (names.indexOf(name) { ...
  • and more

In all cases, you will want to grab the edited sheet's name from the event object:

// My simple-trigger function that runs on edit:
function onEdit(eventObject) {
  if (!eventObject) {
    /* the function was run from the script editor */
    return;
  }
  var editedSheetName = eventObject.range.getSheet().getName();
  /* perform some sort of comparison to figure out if 'editedSheetName'
     is one we want to work on, and if so, do so. */
  ...

You should review a Javascript language reference (such as the one on Mozilla Developer Network) to become familiar with execution flow control.

You can review what properties are available in the event object in the Apps Script trigger documentation: https://developers.google.com/apps-script/guides/triggers/events


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...