google-apps-script - onEdit 多个功能特定单元格

Question

我试图在编辑一个单元格时同时触发多个功能。

我尝试了两种不同的方法，但它不起作用。当我在没有单元格引用的情况下运行第一个选项时，它正在工作，但是一旦我尝试使其依赖于一个单元格，它就会停止工作。

选项1。

function onEdit(e)
{
  if (e.range.getA1Notation() === '') {
    
    function AllData(){
      importData1(); 
      SpreadsheetApp.flush();
      importData2(); 
      SpreadsheetApp.flush();
      importData3();
    }
  }
}
var sourceSpreadsheetID = "XXX";
var sourceWorksheetName = "Sheet1";
var targetSpreadsheetID = "yyy";
var targetWorksheetName = "Sheet 2";

function importData1() {
  
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var thisData = thisSpreadsheet.getRangeByName("data1");
  
  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var toRange = toWorksheet.getRange(9, 2, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}


var sourceSpreadsheetID = "XXX";
var sourceWorksheetName = "Sheet1";
var targetSpreadsheetID = "yyy";
var targetWorksheetName = "Sheet 2";

function importData2() {
  
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var thisData = thisSpreadsheet.getRangeByName("data2");
  
  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var toRange = toWorksheet.getRange(9, 7, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

var sourceSpreadsheetID = "XXX";
var sourceWorksheetName = "Sheet1";
var targetSpreadsheetID = "yyy";
var targetWorksheetName = "Sheet 2";

function importData3() {
  
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var thisData = thisSpreadsheet.getRangeByName("data3");
  
  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var toRange = toWorksheet.getRange(9, 11, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

选项 2。

function createOnEditTrigger() {
 var ss = SpreadsheetApp.openById(targetSpreadsheetID);
 ScriptApp.newTrigger("importData")
   .forSpreadsheet(ss)
   .onEdit()
   .create();
}

var sourceSpreadsheetID = "1qu_AheZoX6Z4H1GF2yBwvlFxLlCkQhmuYfg-fP8z2kc";
var sourceWorksheetName = "tankers-tool-database";
var targetSpreadsheetID = "1ozefsBT-LBmWXPI4QqDG4BSAzfmY_Yqp2q_sXTevhpk";
var targetWorksheetName = "tankers-search-db";

function importData(e) {
 if (e.range.getA1Notation() === "A1") {

        function AllData(){
       importData1(); 
       SpreadsheetApp.flush();
       importData2(); 
       SpreadsheetApp.flush();
       importData3();
}
 }
}

function importData1() {
   
 var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
 var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
 var thisData = thisWorksheet.getDataRange();
 //Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
 var thisData = thisSpreadsheet.getRangeByName("data1");

 var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
 var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
 var toRange = toWorksheet.getRange(9, 2, thisData.getNumRows(), thisData.getNumColumns())
 toRange.setValues(thisData.getValues()); 
}



function importData2() {
   
 var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
 var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
 var thisData = thisWorksheet.getDataRange();
 //Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
 var thisData = thisSpreadsheet.getRangeByName("data2");

 var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
 var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
 var toRange = toWorksheet.getRange(9, 7, thisData.getNumRows(), thisData.getNumColumns())
 toRange.setValues(thisData.getValues()); 
}



function importData3() {
   
 var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
 var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
 var thisData = thisWorksheet.getDataRange();
 //Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
 var thisData = thisSpreadsheet.getRangeByName("data3");

 var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
 var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
 var toRange = toWorksheet.getRange(9, 11, thisData.getNumRows(), thisData.getNumColumns())
 toRange.setValues(thisData.getValues()); 
}

标签： google-apps-scriptgoogle-sheetstriggers

Answer 1

不要在函数中定义函数

只是摆脱，function AllData()而不是简单地打电话

function importData(e) {
  if (e.range.getA1Notation() === "A1") {    
      importData1(); 
      SpreadsheetApp.flush();
      importData2(); 
      SpreadsheetApp.flush();
      importData3();
    }
}

请注意，由于您的所有功能都非常相似，您可以通过使用不同参数调用相同的功能来简化我们的代码。

样本：

//global variables
var sourceSpreadsheetID = "1qu_AheZoX6Z4H1GF2yBwvlFxLlCkQhmuYfg-fP8z2kc";
var sourceWorksheetName = "tankers-tool-database";
var targetSpreadsheetID = "1ozefsBT-LBmWXPI4QqDG4BSAzfmY_Yqp2q_sXTevhpk";
var targetWorksheetName = "tankers-search-db";
var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
//assuming you built a trigger for the following function already
function importData(e) {
  if (e.range.getA1Notation() === "A1") {    
  //call the same funciton 3 times but with different parameters
    importData("data1",2); 
    SpreadsheetApp.flush();
    importData("data2", 7); 
    SpreadsheetApp.flush();
    importData("data3", 11);
  }
}

function importData(data, column) {  
  var thisData = thisSpreadsheet.getRangeByName(data);
  var toRange = toWorksheet.getRange(9, column, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}