Google Sheets 脚本:循环正常运行一次,然后出现错误。

huangapple go评论58阅读模式
英文:

Google Sheets script: loop runs properly once, then throws an error

问题

以下是您提供的Google Apps脚本的翻译部分:

以下是一个我凑合拼凑而成的独立Google Apps脚本(几乎没有编程经验)。基本上,它应该循环遍历Google Drive文件夹中的所有Google Sheets工作簿。对于每个工作簿,它会隐藏除名为“Current”的工作表之外的所有工作表,将该工作表下载为PDF,然后创建一个新的空白的“Current”工作表。这在8个月前完美运行。但现在它对文件夹中的第一个工作簿正常工作,然后在第二个工作簿上失败,并显示错误:“异常:您无法隐藏文档中的所有工作表”,错误出现在这一行:

if(sheets[i].getName()!="Current"){ sheets[i].hideSheet() }

以下是完整的脚本:

function saveInvoice() {

  //该脚本将按以下方式处理所有导师发票:
  // 1. 获取发票文件夹中的所有工作簿
  // 2. 对于每个工作簿:
  //    a. 将“Current”选项卡转换为PDF并将其保存在指定文件夹中,文件名为“[导师姓名]-[日期].pdf”
  //    b. 将“Current”重命名为今天的日期
  //    c. 从空白发票模板创建一个新的“Current”
  //    d. 将新的“Current”移到工作簿中最左边的选项卡
  //    e. 重复以上步骤

  //获取当前文件夹中的电子表格
  var invoiceFolder = DriveApp.getFoldersByName("Fake invoices for testing").next(); //文件夹名称需要每年更新!这是包含电子表格发票的文件夹。
  var invoiceSheets = invoiceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  Logger.log(invoiceSheets)

  while (invoiceSheets.hasNext()) // 循环遍历文件夹内的所有工作簿。
  {
    var workBook = invoiceSheets.next(); //获取下一个工作簿
    Logger.log("工作簿:"+workBook)

    var spreadSheet = SpreadsheetApp.open(workBook).getSheetByName("Current");
    Logger.log("电子表格:"+spreadSheet)

    // 将“Current”发票工作表下载为PDF,命名为[导师姓名(从单元格A1中获取)+当前日期].pdf

    var today=new Date();
    var date = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd") //获取今天的日期并进行格式化

    var sheets=SpreadsheetApp.open(workBook).getSheets(); //获取所有工作表

    var ltrsht = SpreadsheetApp.open(workBook).getSheetByName("Current");

    //现在循环遍历工作簿中的工作表,并隐藏除“Current”之外的所有工作表,因为没有办法下载单个工作表
    for(var i =0;i<sheets.length;i++){
      if(sheets[i].getName()!="Current"){ sheets[i].hideSheet() }
    }
    var pdf = DriveApp.getFileById(workBook.getId());
    var theBlob = pdf.getBlob().getAs('application/pdf').setName(ltrsht.getRange("A1").getValue()+"-"+date+".pdf"); //创建文件名
    var folderID = "13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7"; // 保存在文件夹中的文件夹ID **每年需要更新**
    var folder = DriveApp.getFolderById(folderID); //获取目标文件夹
    var newFile = folder.createFile(theBlob); //保存文件

    //现在取消隐藏其他工作表
    var sheets=SpreadsheetApp.open(workBook).getSheets(); //获取所有工作表
    for(var i =0;i<sheets.length;i++){
      if(sheets[i].getName()!="Current"){ sheets[i].showSheet() }
    }

    //将“Current”工作表的名称更改为今天的日期
    SpreadsheetApp.open(workBook).getSheetByName("Current").setName(date);

    //制作“Blank”工作表的副本并将其命名为“Current”
    var source = SpreadsheetApp.open(workBook);
    Logger.log("source:"+source)
    var sheet = SpreadsheetApp.open(workBook).getSheetByName('Blank');

    sheet.copyTo(SpreadsheetApp.open(workBook)).setName('Current');

    // 将新的“Current”工作表设置为工作簿中最左边的选项卡
    // var tab = SpreadsheetApp.open(workBook).getSheetByName('Current');
    // SpreadsheetApp.open(workBook).setActiveSheet(tab);
    // SpreadsheetApp.open(workBook).moveActiveSheet(1);

    const dss = SpreadsheetApp.open(workBook);
    const sh = dss.getSheetByName('Current').activate();
    dss.moveActiveSheet(0);
  }
}

我怀疑我可能因为经验不足而遗漏了一些明显的东西。尤其令人困惑的是,这个相同的脚本一年前完美运行。欢迎任何指导!

英文:

Following is a standalone Google Apps script I cobbled together (with very little programming experience). Basically, it's supposed to cycle through all the Google Sheets workbooks in a Google Drive folder. For each workbook, it hides all sheets except the one named "Current;" downloads that sheet as a PDF; then creates a new blank "Current" sheet. This worked perfectly 8 months ago. But now it works for the first workbook in the folder, then fails on the second workbook with the error, "Exception: You can't hide all the sheets in a document" on this line:

if(sheets[i].getName()!=&quot;Current&quot;){ sheets[i].hideSheet() }

Here's the complete script:

function saveInvoice() {

  //This script will process all the mentor invoices as follows:
  // 1. Get all the workbooks in the invoice folder
  // 2. For each workbook:
  //    a. Turn the &quot;Current&quot; tab into a PDF and save it in the designated folder with filename &quot;[mentor name]-[date].pdf&quot;
  //    b. Rename &quot;Current&quot; to today&#39;s date
  //    c. Make a new &quot;Current&quot; from the blank invoice template
  //    d. Move the new Current to be the leftmost tab in the workbook
  //    e. Lather, rinse, repeat.


//get the speadsheets in the current folder
var invoiceFolder = DriveApp.getFoldersByName(&quot;Fake invoices for testing&quot;).next(); //THE FOLDER NAME NEEDS TO BE UPDATED EVERY YEAR! This is the folder with the speadsheet invoices.
var invoiceSheets = invoiceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
Logger.log(invoiceSheets)


while (invoiceSheets.hasNext()) // Loops through all Workbooks inside the folder.
  {
    var workBook = invoiceSheets.next(); //get the next workbook
    Logger.log(&quot;Workbook:&quot;+workBook)


    var spreadSheet = SpreadsheetApp.open(workBook).getSheetByName(&quot;Current&quot;);
    Logger.log(&quot;spreadSheet:&quot;+spreadSheet)


      // download the &quot;Current&quot; invoice sheet as a PDF named [mentor name (from cell A1)]+current date].pdf

        var today=new Date();
        var date = Utilities.formatDate(new Date(), &quot;GMT+1&quot;, &quot;yyyy-MM-dd&quot;) //get today&#39;s date and format it

      
        var sheets=SpreadsheetApp.open(workBook).getSheets(); //get all the sheets

        var ltrsht = SpreadsheetApp.open(workBook).getSheetByName(&quot;Current&quot;);


        //now cycle through the sheets in the workbook and hide all except &quot;Current.&quot; Have to hide them b/c no way to d.l. a single sheet
        for(var i =0;i&lt;sheets.length;i++){
          if(sheets[i].getName()!=&quot;Current&quot;){ sheets[i].hideSheet() }
          }
          var pdf = DriveApp.getFileById(workBook.getId());
          var theBlob = pdf.getBlob().getAs(&#39;application/pdf&#39;).setName(ltrsht.getRange(&quot;A1&quot;).getValue()+&quot;-&quot;+date+&quot;.pdf&quot;); //create the file name
          var folderID = &quot;13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7&quot;; // Folder id to save in a folder **NEEDS TO BE UPDATED EVERY YEAR**
          var folder = DriveApp.getFolderById(folderID); //get the target folder
          var newFile = folder.createFile(theBlob); //save the file
      


      //now unhide the other sheets
       var sheets=SpreadsheetApp.open(workBook).getSheets(); //get all the sheets
        for(var i =0;i&lt;sheets.length;i++){
          if(sheets[i].getName()!=&quot;Current&quot;){ sheets[i].showSheet() }
          }


      //change name of &quot;Current&quot; sheet to today&#39;s date
      SpreadsheetApp.open(workBook).getSheetByName(&quot;Current&quot;).setName(date);


      //Make a copy of the &quot;Blank&quot; sheet and rename it &quot;Current&quot;
      var source = SpreadsheetApp.open(workBook);
      Logger.log(&quot;source:&quot;+source)
      var sheet = SpreadsheetApp.open(workBook).getSheetByName(&#39;Blank&#39;);

      sheet.copyTo(SpreadsheetApp.open(workBook)).setName(&#39;Current&#39;);


      // Make the new Current sheet the leftmost tab in the Workbook
      // var tab = SpreadsheetApp.open(workBook).getSheetByName(&#39;Current&#39;);
      // SpreadsheetApp.open(workBook).setActiveSheet(tab);
      // SpreadsheetApp.open(workBook).moveActiveSheet(1);

      const dss = SpreadsheetApp.open(workBook);
      const sh = dss.getSheetByName(&#39;Current&#39;).activate();
      dss.moveActiveSheet(0);
  }

}

I suspect I'm missing something obvious because of my inexperience. What's especially puzzling is the fact that this same script worked flawlessly a year ago. Any guidance is much appreciated!

答案1

得分: 2

以下是代码部分的翻译:

function saveInvoice() {

  //此脚本将按以下方式处理所有导师发票:
  // 1. 获取发票文件夹中的所有工作簿
  // 2. 对于每个工作簿:
  //    a. 将“Current”选项卡转换为PDF并保存在指定文件夹中,文件名为“[导师姓名]-[日期].pdf”
  //    b. 将“Current”重命名为今天的日期
  //    c. 从空白发票模板创建一个新的“Current”
  //    d. 将新的“Current”移动到工作簿中最左边的选项卡
  //    e. 重复以上步骤。

  //获取当前文件夹中的电子表格
  var invoiceFolder = DriveApp.getFoldersByName("Fake invoices for testing").next(); //此处需要每年更新文件夹名称!这是包含电子表格发票的文件夹。
  var invoiceSheets = invoiceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  Logger.log(invoiceSheets)

  //循环遍历文件夹中的所有工作簿。
  while (invoiceSheets.hasNext()) {
    var workBook = invoiceSheets.next(); //获取下一个工作簿
    Logger.log("工作簿:" + workBook)

    var spreadSheet = SpreadsheetApp.open(workBook);
    var ltrsht = spreadSheet.getSheetByName("Current");
    Logger.log("电子表格:" + ltrsht)

    if (!ltrsht) {
      Logger.log("找不到当前发票:" + spreadSheet);
    } else {
      //下载“Current”发票表格为PDF,文件名为[导师姓名(从单元格A1中获取)]+当前日期.pdf

      var today = new Date();
      var date = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd") //获取今天的日期并格式化

      var sheets = spreadSheet.getSheets(); //获取所有工作表

      //现在循环遍历工作簿中的工作表并隐藏除“Current”外的所有工作表。必须隐藏它们,因为无法下载单个工作表
      ltrsht.showSheet();
      for (var i = 0; i < sheets.length; i++) {
        if (sheets[i].getName() != "Current") {
          sheets[i].hideSheet()
        }
      }
      var pdf = DriveApp.getFileById(workBook.getId());
      var theBlob = pdf.getBlob().getAs('application/pdf').setName(ltrsht.getRange("A1").getValue() + "-" + date + ".pdf"); //创建文件名
      var folderID = "13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7"; //保存到文件夹的文件夹ID**需要每年更新**
      var folder = DriveApp.getFolderById(folderID); //获取目标文件夹
      var newFile = folder.createFile(theBlob); //保存文件

      //现在取消隐藏其他工作表
      for (var i = 0; i < sheets.length; i++) {
        if (sheets[i].getName() != "Current") {
          sheets[i].showSheet();
        }
      }

      //将“Current”工作表的名称更改为今天的日期
      ltrsht.setName(date);

      //创建“Blank”工作表的副本并将其重命名为“Current”
      Logger.log("源:" + spreadSheet)
      var blank = spreadSheet.getSheetByName('Blank');

      var sh = blank.copyTo(spreadSheet).setName('Current');

      //将新的“Current”工作表设置为工作簿中最左边的选项卡
      sh.activate();
      spreadSheet.moveActiveSheet(0);
    }
  }

}

希望这对你有所帮助。如果你需要进一步的帮助,请随时提问。

英文:

The error is a result of the "Current" sheet either being hidden or not existing in the given Spreadsheet. Modified it to log anytime a spreadsheet doesn't have a "Current" sheet and to show the "Current" sheet before hiding the rest. I also made some improvements that should speed things up with fewer calls to SpreadsheetApp.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

function saveInvoice() {
//This script will process all the mentor invoices as follows:
// 1. Get all the workbooks in the invoice folder
// 2. For each workbook:
//    a. Turn the &quot;Current&quot; tab into a PDF and save it in the designated folder with filename &quot;[mentor name]-[date].pdf&quot;
//    b. Rename &quot;Current&quot; to today&#39;s date
//    c. Make a new &quot;Current&quot; from the blank invoice template
//    d. Move the new Current to be the leftmost tab in the workbook
//    e. Lather, rinse, repeat.
//get the speadsheets in the current folder
var invoiceFolder = DriveApp.getFoldersByName(&quot;Fake invoices for testing&quot;).next(); //THE FOLDER NAME NEEDS TO BE UPDATED EVERY YEAR! This is the folder with the speadsheet invoices.
var invoiceSheets = invoiceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
Logger.log(invoiceSheets)
// Loops through all Workbooks inside the folder.
while (invoiceSheets.hasNext()) {
var workBook = invoiceSheets.next(); //get the next workbook
Logger.log(&quot;Workbook:&quot; + workBook)
var spreadSheet = SpreadsheetApp.open(workBook);
var ltrsht = spreadSheet.getSheetByName(&quot;Current&quot;);
Logger.log(&quot;spreadSheet:&quot; + ltrsht)
if (!ltrsht) {
Logger.log(&quot;Current invoice not found:&quot; + spreadSheet);
} else {
// download the &quot;Current&quot; invoice sheet as a PDF named [mentor name (from cell A1)]+current date].pdf
var today = new Date();
var date = Utilities.formatDate(new Date(), &quot;GMT+1&quot;, &quot;yyyy-MM-dd&quot;) //get today&#39;s date and format it
var sheets = spreadSheet.getSheets(); //get all the sheets
//now cycle through the sheets in the workbook and hide all except &quot;Current.&quot; Have to hide them b/c no way to d.l. a single sheet
ltrsht.showSheet();
for (var i = 0; i &lt; sheets.length; i++) {
if (sheets[i].getName() != &quot;Current&quot;) {
sheets[i].hideSheet()
}
}
var pdf = DriveApp.getFileById(workBook.getId());
var theBlob = pdf.getBlob().getAs(&#39;application/pdf&#39;).setName(ltrsht.getRange(&quot;A1&quot;).getValue() + &quot;-&quot; + date + &quot;.pdf&quot;); //create the file name
var folderID = &quot;13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7&quot;; // Folder id to save in a folder **NEEDS TO BE UPDATED EVERY YEAR**
var folder = DriveApp.getFolderById(folderID); //get the target folder
var newFile = folder.createFile(theBlob); //save the file
//now unhide the other sheets
for (var i = 0; i &lt; sheets.length; i++) {
if (sheets[i].getName() != &quot;Current&quot;) {
sheets[i].showSheet();
}
}
//change name of &quot;Current&quot; sheet to today&#39;s date
ltrsht.setName(date);
//Make a copy of the &quot;Blank&quot; sheet and rename it &quot;Current&quot;
Logger.log(&quot;source:&quot; + spreadSheet)
var blank = spreadSheet.getSheetByName(&#39;Blank&#39;);
var sh = blank.copyTo(spreadSheet).setName(&#39;Current&#39;);
// Make the new Current sheet the leftmost tab in the Workbook
// var tab = SpreadsheetApp.open(workBook).getSheetByName(&#39;Current&#39;);
// SpreadsheetApp.open(workBook).setActiveSheet(tab);
// SpreadsheetApp.open(workBook).moveActiveSheet(1);
sh.activate();
spreadSheet.moveActiveSheet(0);
}
}
}

<!-- end snippet -->

huangapple
  • 本文由 发表于 2023年5月31日 23:55:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76375340.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定