onEdit多个功能特定的单元格 [英] onEdit multiple functions specific cell

问题描述

在编辑一个单元格时，我试图同时启动多个功能.

I am trying to get multiple functions to fire at the same time when editing one cell.

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

I have tried two different ways but it doesn't work. When I run the first option without the cell reference it is working but as soon as I try to make it dependent on the one cell it stops working.

选项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()); 
}

推荐答案

不要在函数内定义函数

只需摆脱函数AllData()，而只需调用

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

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

Mind that since all your funcitons a revery similar, you can make our code much easier by calling the same funciton with different parameters.

示例:

//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()); 
}

这篇关于onEdit多个功能特定的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！