英文:
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()!="Current"){ 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 "Current" tab into a PDF and save it in the designated folder with filename "[mentor name]-[date].pdf"
// b. Rename "Current" to today's date
// c. Make a new "Current" 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("Fake invoices for testing").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("Workbook:"+workBook)
var spreadSheet = SpreadsheetApp.open(workBook).getSheetByName("Current");
Logger.log("spreadSheet:"+spreadSheet)
// download the "Current" invoice sheet as a PDF named [mentor name (from cell A1)]+current date].pdf
var today=new Date();
var date = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd") //get today's date and format it
var sheets=SpreadsheetApp.open(workBook).getSheets(); //get all the sheets
var ltrsht = SpreadsheetApp.open(workBook).getSheetByName("Current");
//now cycle through the sheets in the workbook and hide all except "Current." Have to hide them b/c no way to d.l. a single sheet
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"); //create the file name
var folderID = "13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7"; // 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<sheets.length;i++){
if(sheets[i].getName()!="Current"){ sheets[i].showSheet() }
}
//change name of "Current" sheet to today's date
SpreadsheetApp.open(workBook).getSheetByName("Current").setName(date);
//Make a copy of the "Blank" sheet and rename it "Current"
var source = SpreadsheetApp.open(workBook);
Logger.log("source:"+source)
var sheet = SpreadsheetApp.open(workBook).getSheetByName('Blank');
sheet.copyTo(SpreadsheetApp.open(workBook)).setName('Current');
// Make the new Current sheet the leftmost tab in the Workbook
// 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);
}
}
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 "Current" tab into a PDF and save it in the designated folder with filename "[mentor name]-[date].pdf"
// b. Rename "Current" to today's date
// c. Make a new "Current" 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("Fake invoices for testing").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("Workbook:" + workBook)
var spreadSheet = SpreadsheetApp.open(workBook);
var ltrsht = spreadSheet.getSheetByName("Current");
Logger.log("spreadSheet:" + ltrsht)
if (!ltrsht) {
Logger.log("Current invoice not found:" + spreadSheet);
} else {
// download the "Current" invoice sheet as a PDF named [mentor name (from cell A1)]+current date].pdf
var today = new Date();
var date = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd") //get today'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 "Current." Have to hide them b/c no way to d.l. a single sheet
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"); //create the file name
var folderID = "13KcgTUC3sw1aAa3xQWY6FkY6GwAH0cT7"; // 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 < sheets.length; i++) {
if (sheets[i].getName() != "Current") {
sheets[i].showSheet();
}
}
//change name of "Current" sheet to today's date
ltrsht.setName(date);
//Make a copy of the "Blank" sheet and rename it "Current"
Logger.log("source:" + spreadSheet)
var blank = spreadSheet.getSheetByName('Blank');
var sh = blank.copyTo(spreadSheet).setName('Current');
// Make the new Current sheet the leftmost tab in the Workbook
// var tab = SpreadsheetApp.open(workBook).getSheetByName('Current');
// SpreadsheetApp.open(workBook).setActiveSheet(tab);
// SpreadsheetApp.open(workBook).moveActiveSheet(1);
sh.activate();
spreadSheet.moveActiveSheet(0);
}
}
}
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论