将所有工作表滚动到打开的最后一行 [英] Scroll all worksheets to last row on open

问题描述

我构建了一个电子表格，该电子表格包含7张工作表，每张工作表都填充了许多行数据.我已经厌倦了手动向下滚动到每张纸的最后一行，所以我构造了一个简单的脚本函数，以编程方式查找并转到一张纸的最后一行.手动运行时，此功能在大多数情况下有效.但是，当我尝试使用从电子表格打开时"安装的事件触发器执行此功能时，它无法正常启动.

I've built a spreadsheet that has 7 sheets, each of which is filled with many rows of data. I got tired of manually scrolling down to the last row of each sheet, so I constructed a simple script function to programmatically find and go to a sheet's last row. This function works, for the most part, when run manually. However, when I tried to execute this function with a "From Spreadsheet, On Open" installed event trigger, it doesn't fire properly.

功能如下:

function LastRowOnOpen() {
  var ss = SpreadsheetApp.getActive();
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('302 Ashfrd'));
   var lastRow = sheet.getLastRow();

   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('3220 Blmnt'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('3302 Blmnt'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('1035 Bnhm'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('Deer Park'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('3000-102 Lrn'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();

   var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('2249 Wht'));
   var lastRow = sheet.getLastRow();
   lastRow = lastRow + 5;
   sheet.getRange(lastRow, 1).activate();
   sheet.showSheet();  

P.S.

我还尝试使用包含以下内容的for循环:

I also tried using a for loop containing:

var sheet = SreadsheetApp.setActiveSheet(ss.getSheetByName('2249 Wht'));
var lastRow = sheet.getLastRow();
lastRow = lastRow + 5;
sheet.getRange(lastRow, 1).activate();
sheet.showSheet();   `   

更改sheet = getSheetByname方法以减少代码行，但是除了循环的最后一次迭代之外，所有其他代码均被忽略.

varying the sheet = getSheetByname method to reduce lines of code, however all but the last iteration of the loop was ignored.

推荐答案

没有一个很好的答案(我可以找到)完整地解决了这个主题:

There isn't a good answer (that I could find) that addresses this topic in full:

您当前的脚本忽略"除了循环的最后一次迭代之外的所有迭代"的问题源于Google自己的

The issue with your current script "ignoring" "all but the last iteration of the loop" stems from Google's own internal optimizations of the Spreadsheet Service:

脚本通常需要从电子表格中读取数据，执行计算，然后将数据结果写到电子表格中. Google Apps脚本已经进行了一些内置的优化，例如使用超前缓存来检索脚本可能获得的内容，并使用写入缓存来保存可能设置的内容.

Scripts commonly need to read in data from a spreadsheet, perform calculations, and then write out the results of the data to a spreadsheet. Google Apps Script already has some built-in optimization, such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.

换句话说，将对脚本进行分析，并有效地确定仅需要最后一次activate调用.为了解决这个问题，我们使用 SpreadsheetApp.flush() 和 Utilities.sleep ，如

In other words, your script is analyzed and effectively only the last activate call is determined to be needed. To counter this, we use SpreadsheetApp.flush() and Utilities.sleep, as done here.

function onOpen(e) { // "Simple Trigger" from reserved function name.
  // https://developers.google.com/apps-script/guides/triggers/events#open
  scrollAll(e.source);
}
function scrollAll(wb) {
  (wb ? wb : SpreadsheetApp.getActive()).getSheets().forEach(
    function (sheet) {
      wb.setActiveSheet(sheet).setActiveSelection("A" + sheet.getLastRow());
      SpreadsheetApp.flush();
      Utilities.sleep(1000); // may need to be adjusted
    });
}
function scrollActive() { // Macro target
  const s = SpreadsheetApp.getActiveSheet();
  s.setActiveSelection("A" + s.getLastRow());
}

要使用键盘快捷键将当前工作表重新滚动到底部，您可以在项目的

To have a keyboard shortcut to re-scroll the current sheet to the bottom, you would include the following in your project's manifest file to bind the scrollActive function as a macro with a keyboard shortcut:

"sheets": {
  "macros": [{
    "menuName": "Scroll To Last Row",
    "functionName": "scrollActive",
    "defaultShortcut": "Ctrl+Alt+Shift+N" // N= 1, 2, 3, 4, 5, 6, 7, 8, 9, or 0
  }]
}

这篇关于将所有工作表滚动到打开的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！