google-apps-script - 分配给按钮时找不到脚本功能

Question

我在表单上有配置为运行脚本的按钮。每个按钮对于单个脚本都是唯一的。脚本在脚本编辑器中成功运行，但在单击任何按钮时显示为未找到。

下面的脚本似乎可以正常工作。我不确定我需要做什么来解决为什么分配给按钮时找不到它们。

    function myFunction() {
// Clear form
function ClearCell() {
  var ss        =SpreadsheetApp.getActiveSpreadsheet();
  var formS     =ss.getSheetByName("Entry Form"); //Form Sheet

  var rangesToClear = ["b7","d7","f7","h7","b9","d9","f9","h9","b12"];
  for (var i=0; i<rangesToClear.length; i++) {
    formS.getRange(rangesToClear[i]).clearContent();
  }
}
//-----------------------------------------------------------------------------------
//Input Values
  function SubmitData() {
    var ss      =SpreadsheetApp.getActiveSpreadsheet();
    var formS   =ss.getSheetByName("Entry Form"); //Form Sheet
    var dataS   =ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet
    
    
    var values = [[formS.getRange("b7").getValue(),
                   formS.getRange("d7").getValue(),
                   formS.getRange("f7").getValue(),
                   formS.getRange("h7").getValue(),
                   formS.getRange("b9").getValue(),
                   formS.getRange("d9").getValue(),
                   formS.getRange("f9").getValue(),
                   formS.getRange("h9").getValue(),
                   formS.getRange("b12").getValue()]];
    dataS.getRange(dataS.getLastRow()+1,1,1,9).setValues(values);
    ClearCell();
  }
//---------------------------------------------------------------------------------
  //Search Records
  var SEARCH_COL_IDX = 0;
  function Search(){
    var ss      =SpreadsheetApp.getActiveSpreadsheet();
    var formS   =ss.getSheetByName("Entry Form"); //Form Sheet  

  var str     =formS.getRange("B4").getValue();
  var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();
  for (var i=0; i<values.length;i++){
    var row = values[i];
    if (row[SEARCH_COL_IDX] ==str){
      
    formS.getRange("b7").setValue(row[0]),
    formS.getRange("d7").setValue(row[1]),
    formS.getRange("f7").setValue(row[2]),
    formS.getRange("h7").setValue(row[3]),
    formS.getRange("b9").setValue(row[4]),
    formS.getRange("d9").setValue(row[5]),
    formS.getRange("f9").setValue(row[6]),
    formS.getRange("h9").setValue(row[7]),
    formS.getRange("b12").setValue(row[8]);  
      
    } }}
  //------------------------------------------------------------------------------------
   //Update Data 
  function UpdateData(){
    var ss      =SpreadsheetApp.getActiveSpreadsheet();
    var formS   =ss.getSheetByName("Entry Form"); //Form Sheet
    var dataS   =ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet   
    
    var str    =formS.getRange("B4").getValue();
    var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();    
    for (var i=0; i<values.length; i++){
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str){
      var  INT_R = i+1
      
    var values1 = [[formS.getRange("b7").getValue(),
                   formS.getRange("d7").getValue(),
                   formS.getRange("f7").getValue(),
                   formS.getRange("h7").getValue(),
                   formS.getRange("b9").getValue(),
                   formS.getRange("d9").getValue(),
                   formS.getRange("f9").getValue(),
                   formS.getRange("h9").getValue(),
                   formS.getRange("b12").getValue()]];
      dataS.getRange(INT_R,1,1,9).setValues(values1);
      SpreadsheetApp.getUi().alert('  "Data Updated  " ');
      ClearCell();  
    } } }
  //------------------------------------------------------------------------------------
  //Delete Data
  function Delete() {
    var ss      =SpreadsheetApp.getActiveSpreadsheet();
    var formS   =ss.getSheetByName("Entry Form"); //Form Sheet
    var dataS   =ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet  

var ui = spreadsheetApp.get.Ui();
var response = ui.alert('Delete ?',ui.ButtonSet.Yes_No);

//Process the user's response.
    if (response == ui.Button.Yes) {
      
  var str = formS.getRange("B4").getValue();
  var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();
  for (var i = 0; i <values.length; i++) {
  var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {
      var INT_R = i+1
      
      dataS.deleteRow(INT_R) ;
      ClearCell();
    }}} }
}

标签： google-apps-scriptbuttongoogle-sheets

Answer 1

Google 表格找不到您的函数，因为它包含在myFunction(). 这使myFunction()Google 表格成为唯一可见的功能。要解决这个问题，只需删除myFunction(){}包含其他功能的 。

您的代码应如下所示：

// Clear form
function ClearCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Entry Form"); //Form Sheet

  var rangesToClear = ["b7", "d7", "f7", "h7", "b9", "d9", "f9", "h9", "b12"];
  for (var i = 0; i < rangesToClear.length; i++) {
    formS.getRange(rangesToClear[i]).clearContent();
  }
}
//-----------------------------------------------------------------------------------
//Input Values
function SubmitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Entry Form"); //Form Sheet
  var dataS = ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet


  var values = [
    [formS.getRange("b7").getValue(),
      formS.getRange("d7").getValue(),
      formS.getRange("f7").getValue(),
      formS.getRange("h7").getValue(),
      formS.getRange("b9").getValue(),
      formS.getRange("d9").getValue(),
      formS.getRange("f9").getValue(),
      formS.getRange("h9").getValue(),
      formS.getRange("b12").getValue()
    ]
  ];
  dataS.getRange(dataS.getLastRow() + 1, 1, 1, 9).setValues(values);
  ClearCell();
}
//---------------------------------------------------------------------------------
//Search Records
var SEARCH_COL_IDX = 0;

function Search() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Entry Form"); //Form Sheet  

  var str = formS.getRange("B4").getValue();
  var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {

      formS.getRange("b7").setValue(row[0]),
        formS.getRange("d7").setValue(row[1]),
        formS.getRange("f7").setValue(row[2]),
        formS.getRange("h7").setValue(row[3]),
        formS.getRange("b9").setValue(row[4]),
        formS.getRange("d9").setValue(row[5]),
        formS.getRange("f9").setValue(row[6]),
        formS.getRange("h9").setValue(row[7]),
        formS.getRange("b12").setValue(row[8]);

    }
  }
}
//------------------------------------------------------------------------------------
//Update Data 
function UpdateData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Entry Form"); //Form Sheet
  var dataS = ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet   

  var str = formS.getRange("B4").getValue();
  var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {
      var INT_R = i + 1

      var values1 = [
        [formS.getRange("b7").getValue(),
          formS.getRange("d7").getValue(),
          formS.getRange("f7").getValue(),
          formS.getRange("h7").getValue(),
          formS.getRange("b9").getValue(),
          formS.getRange("d9").getValue(),
          formS.getRange("f9").getValue(),
          formS.getRange("h9").getValue(),
          formS.getRange("b12").getValue()
        ]
      ];
      dataS.getRange(INT_R, 1, 1, 9).setValues(values1);
      SpreadsheetApp.getUi().alert('  "Data Updated  " ');
      ClearCell();
    }
  }
}
//------------------------------------------------------------------------------------
//Delete Data
function Delete() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Entry Form"); //Form Sheet
  var dataS = ss.getSheetByName("Traffic Discrepancy Log"); //Data Sheet  

  var ui = spreadsheetApp.get.Ui();
  var response = ui.alert('Delete ?', ui.ButtonSet.Yes_No);

  //Process the user's response.
  if (response == ui.Button.Yes) {

    var str = formS.getRange("B4").getValue();
    var values = ss.getSheetByName("Traffic Discrepancy Log").getDataRange().getValues();
    for (var i = 0; i < values.length; i++) {
      var row = values[i];
      if (row[SEARCH_COL_IDX] == str) {
        var INT_R = i + 1

        dataS.deleteRow(INT_R);
        ClearCell();
      }
    }
  }
}