Google Apps脚本:以电子表格范围作为参数从菜单中调用函数 [英] Google Apps Script: Calling a function from menu with a spreadsheet range as the parameter

问题描述

我有一个函数，该函数接受电子表格范围作为参数，然后在与给定范围相同的行中添加日期.

I have a function that accepts a spreadsheet range as a parameter, then adds a date in the same row as the given range.

function autoDate(cell) {
  var currentDate = new Date();
  var currentMonth = currentDate.getMonth() + 1;
  var currentDateStr = new String();
  currentDateStr = currentDate.getDate() + "/" + currentMonth;
  var sheet = SpreadsheetApp.getActiveSheet();

  if (cell.getValue() == ""){
    var activeRowInt = cell.getRow();
    var dateCell = sheet.getRange(activeRowInt,3); //3 is my date column
    dateCell.setValue(currentDateStr);
  }
}

我有一个菜单，在该菜单中运行一个单独的函数，该函数以活动单元格为参数调用autoDate()(因为菜单系统不允许调用带有参数的函数)

I have a menu where I run a separate function that calls autoDate() with the active cell as the parameter (since the menu system doesn't allow calling functions with parameters)

function autoDateMenu(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  autoDate(activeCell);
}

如果我从脚本编辑器中运行autoDateMenu()，它将正常工作. 如果我通过从电子表格的菜单中选择autoDateMenu()来运行它，则会出现以下错误:

If I run autoDateMenu() from within the script editor, it works fine. If I run autoDateMenu() by selecting it from the menu of the spreadsheet, I get the following error:

TypeError:无法调用未定义的方法"getValue". (第64行，文件 代码")

TypeError: Cannot call method "getValue" of undefined. (line 64, file "Code")

第64行引用此行:

if (cell.getValue() == ""){

这是我为菜单编写的代码:

Here's the code I wrote for the menu:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Read Data",
    functionName : "readRows"
  },{
    name : "Set Date",
    functionName : "autoDateMenu"
  }];
  spreadsheet.addMenu("Script Center", entries);
}

感谢您的回复:)

推荐答案

为什么不这样做呢?我认为您在解决此参考问题时遇到了问题.

Why not just do this? I think you are having issues with references that this would solve.

菜单:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Read Data",
    functionName : "readRows"
  },{
    name : "Set Date",
    functionName : "autoDate"
  }];
  spreadsheet.addMenu("Script Center", entries);
}

自动日期功能:

function autoDate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var currentDate = new Date();
  var currentMonth = currentDate.getMonth() + 1;
  var currentDateStr = new String();
  currentDateStr = currentDate.getDate() + "/" + currentMonth;

  if (activeCell.getValue() == ""){
    var activeRowInt = cell.getRow();
    var dateCell = sheet.getRange(activeRowInt,3); //3 is my date column
    dateCell.setValue(currentDateStr);
  }
}

删除中间人的AutoDateMenu函数.

Remove the middle man AutoDateMenu function.

如果这对于您的目的不起作用，我们可以尝试其他尝试.

If this does not work for your purposes we can try something else.

这篇关于Google Apps脚本:以电子表格范围作为参数从菜单中调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！