脚本无法正确打开其他电子表格 [英] Script won't open another spreadsheet properly

问题描述

当我使用openById()时，我的脚本退出.

My script quits when I use openById().

该脚本应该能够检测用户何时修改了主列表电子表格中的两个相邻列(即，用户在作业列表中输入了作业的名称和日期).然后，它调用一个函数来创建新的电子表格(现有模板电子表格的副本).尚未创建新的电子表格.

The script is supposed to detect when a user has modified two adjacent columns in a master list spreadsheet (i.e. user enters the name and date of a job on a list of jobs). It then invokes a function to create a new spreadsheet (a copy of an existing template spreadsheet). The new spreadsheet isn't being created.

我已将代码精简化，并使用记录器缩小了问题的范围.我不太确定这里发生了什么，但是在我的openById()变量赋值被记录之后，什么也没有.

I've stripped my code down to the bare bones and narrowed down the problem using the logger. I'm not quite sure what's going on here, but nothing after my variable assignment of openById() is getting logged.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = e.range;
  //var newId;
  //var newName;
  //I've removed the last two variables for debugging purposes. They'll be used to name the new spreadsheet.

  Logger.log("1");

  //Check if edit occurred in relevant range
  if((range.getColumn() !== 1) && (range.getColumn() !== 2)) return;

  Logger.log("2");

  //Check if both columns were filled after edit
  if((range.getColumn() == 1) && (range.offset(0,1).isBlank() == true)) return;
  if((range.getColumn() == 2) && (range.offset(0,-1).isBlank() == true)) return;

  Logger.log("3");

  //Temporarily removed but this is for naming the new spreadsheet
  /*if(range.getColumn() == 1) newName = range.offset(0,7).getValue();
  if(range.getColumn() == 2) newName = range.offset(0,6).getValue();*/

  //Check whether the edits occurred on the jobs list or receptions list (indicated by '2' or '3' in L1)
  if(sheet.getRange('L1').getValue() == 2) newJob();
  if(sheet.getRange('L1').getValue() == 3) newReception();

  Logger.log("11");

  //Once again, only temporarily removed.
  /*SpreadsheetApp.openById(newId).rename(newName);*/


}

function newJob() {
  Logger.log("4");

  var templateSS = SpreadsheetApp.openById("Spreadsheet ID Redacted");
  Logger.log("5");

  var newSS = templateSS.copy("Untitled Job");
  Logger.log("6");

  var originalFolder = DriveApp.getFolderById("Folder ID Redacted");
  Logger.log("7");

  var newSSFile = DriveApp.getFileById(newSS.getId());
  Logger.log("8");

  //Copy file to the correct directory and delete the instance created in root
  originalFolder.addFile(newSSFile);
  Logger.log("9");

  DriveApp.getRootFolder().removeFile(newSSFile);
  Logger.log("10");

  return(newSSFile);

} 

/*Didn't bother pasting newReception here because it's nearly identical to newJob*/

记录器应记录所有数字1-11.它仅记录1,2,3,4.在第4点之后和第5点之前停止了脚本.

The logger should log all numbers 1-11. It's only logging 1,2,3,4. Something is stopping the script after point number 4 and before point number 5.

推荐答案

您将无法通过

You won't be able to do this with a simple trigger (it won't have the right authorization), use an installable trigger for your function onEdit for this purpose instead. This will prompt you for the authorization it needs to run properly.

下面的脚本应该为您设置好了，您只需要运行一次就可以了.

The below script should set it up for you, all you need to do is run it once and you should be good to go.

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

这篇关于脚本无法正确打开其他电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！