如何使Google电子表格的所有工作表在左上方显示A1? [英] How to make all sheets of a Google spreadsheet show A1 in upper left?

问题描述

我有一个72页的Google电子表格，正在尝试重置每张工作表，以便在单击其选项卡时在左上方显示A1.也就是说，如果将一张纸向下滚动而看不到A1，我希望它向后滚动以便可以.

我尝试了以下google脚本，但是没有任何作用.我从这里获得第4个(reset4)但这也不起作用.

function reset1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveSelection("A1");
  }
}

function reset2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveRange(allSheets[i].getRange("A1"));
  }
}

function reset3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setCurrentCell(allSheets[i].getRange("A1").getCell(1, 1));
  }
}

// ***** CORRECTION: 
// ***** reset4() later found to work. See accepted answer comments for details

function reset4() {
  SpreadsheetApp.getActive().getSheets().forEach(function (s) {
    s.setActiveSelection("A1");
  });
  SpreadsheetApp.flush(); // may still need to refresh page afterward
}

function reset5(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  sheets.forEach(function(sh){
    sh.activate();
    ss.getActiveSheet().getRange("A1").activate();
  });
}

我根据Rafa Guillermo的答案中提供的代码添加了第五个代码(reset5)，但这也不起作用.尽管拉法·吉列尔莫(Rafa Guillermo)的答案行得通，但我并不是说要每次单击其选项卡时都重置表，而是要在运行所需的功能之后才能重新设置.

下面的动画.gif显示了一个测试电子表格，该电子表格具有最后一个功能(reset5)和3页，其中可能有行和列.在视频的开头，单元格A1(具有红色背景色)位于每张纸的左上角.然后，我向下滚动并在每张纸上选择其他单元格，以便不再看到A1.运行该功能后，Sheet3的A1确实位于左上方.但是，问题在于Sheet1和Sheet2没有.

解决方案

答案:

设置每张纸的有效范围后，需要使用SpreadsheetApp.flush().

_{NB: 在提供了更多信息后，此答案已被编辑，原始答案在中断下方.}

更多信息:

如果我正确理解了您的情况，则希望具有以下功能:

• 不在触发器上运行.
• 运行时，重置附加到单元格A1的电子表格中所有工作表的活动单元格.

有了这些参数，我相信您在问题中提供的功能reset4()实际上可以正常工作.现在，对于已绑定的脚本文件，Google表格确实有一些奇怪的行为，并且有时有时，如果打开并长时间不使用它们，则脚本选项卡会丢失"其对电子表格的绑定状态；可以通过重新加载电子表格来解决此问题(从UI角度来看，该电子表格还会将所有工作表中的活动单元格重置为A1，但我们暂时将其搁置一旁.)

代码:

作为对上一个答案中脚本的修改，您可以运行reset5()函数，但在activate()调用后添加SpreadsheetApp.flush():

 function reset() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var range;
  sheets.forEach(function(sh) {
    range = sh.getRange("A1");
    range.activate();
    SpreadsheetApp.flush();
  });
}
 

希望对您有帮助！

参考文献:

• 类Range-方法activate() | Apps脚本

  ---

上一个答案:

可以使用触发应用脚本更改当前片的活性细胞当拉片被改变.

更多信息:

根据简单触发器文档:

当用户在电子表格中更改选择时，onSelectionChange(e)触发器将自动运行.大多数onSelectionChange(e)触发器使用事件对象中的信息进行适当响应.

代码示例:

您可以将当前活动工作表存储在PropertiesService属性中，并在更改活动选择时将其与新的活动工作表进行比较.然后，您可以从此处将活动单元格更改为该工作表的A1:

 function onSelectionChange(e) {
  var lastSheet = PropertiesService.getUserProperties().getProperty("LastActiveSheet");
  if (e.source.getActiveSheet().getName() == lastSheet) {
    return;
  }
  else {
    PropertiesService.getUserProperties().setProperty("LastActiveSheet", e.source.getActiveSheet().getName())
    SpreadsheetApp.getActiveSheet().getRange("A1").activate();
  }
}
 

希望对您有帮助！

参考文献:

• 简单触发器|应用脚本Google Developers

I have a 72 sheet Google spreadsheet and am trying to reset each sheet so that it shows A1 in the upper left when you click on its tab. That is, if a sheet is scrolled downward so that you can't see A1, I want it to scroll back so that you can.

I've tried the following google scripts, but nothing does the job. I got the 4th one (reset4) from here but that didn't work either.

function reset1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveSelection("A1");
  }
}

function reset2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveRange(allSheets[i].getRange("A1"));
  }
}

function reset3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setCurrentCell(allSheets[i].getRange("A1").getCell(1, 1));
  }
}

// ***** CORRECTION: 
// ***** reset4() later found to work. See accepted answer comments for details

function reset4() {
  SpreadsheetApp.getActive().getSheets().forEach(function (s) {
    s.setActiveSelection("A1");
  });
  SpreadsheetApp.flush(); // may still need to refresh page afterward
}

function reset5(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  sheets.forEach(function(sh){
    sh.activate();
    ss.getActiveSheet().getRange("A1").activate();
  });
}

I added the 5th one (reset5) based on the code supplied in the answer by Rafa Guillermo, but that didn't work either. Though the answer by Rafa Guillermo works, I didn't mean for sheets to be reset EVERY time its tab is clicked, but only after the desired function is run.

The animated .gif below shows a test spreadsheet having the last function (reset5) and 3 sheets with may rows and columns. At the beginning of the video, cell A1 (with a red background color) is at the top left corner of each sheet. Then I scroll down and over and select other cells in each sheet so that A1 is no longer seen. After running the function, Sheet3 does have A1 positioned at the top left, as desired. But, the problem is that Sheet1 and Sheet2 do not.

解决方案

Answer:

You need to use SpreadsheetApp.flush() after you set the active range of each sheet.

_{NB: This answer was edited after more information was provided, the original answer is below the break.}

More Information:

If I understand your situation correctly, you wish to have a function which:

• Does not run on a trigger.
• When run, resets the active cell of all sheets in the Spreadsheet it is attached to to cell A1.

With these parameters, I believe that the function reset4() you provided in your question does in fact work. Now, Google Sheets does have some strange behaviour with bound script files and sometimes the script tab will 'lose' it's bound status to the Spreadsheet if both are open and left unattended for long periods of time; this can be fixed by reloading the Spreadsheet (which, from a UI perspective, will also reset the active cell in all your sheets to A1, but we shall put that aside for now).

Code:

As a modification to the modification of the script in my previous answer, you can run your reset5() function but add a SpreadsheetApp.flush() after the activate() call:

function reset() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var range;
  sheets.forEach(function(sh) {
    range = sh.getRange("A1");
    range.activate();
    SpreadsheetApp.flush();
  });
}

I hope this is helpful to you!

References:

• Class Range - Method activate() | Apps Script

  ---

Previous Answer:

You can use the onSelectionChange(e) trigger in Apps Script to change the active cell of the current Sheet to A1 when the tab is changed.

More Information:

As per the Simple Triggers documentation:

The onSelectionChange(e) trigger runs automatically when a user changes the selection in a spreadsheet. Most onSelectionChange(e) triggers use the information in the event object to respond appropriately.

Code Example:

You can store the current active sheet in a PropertiesService property and compare it to the new active Sheet when the active selection is changed. From here you can then change the active cell to A1 of that sheet:

function onSelectionChange(e) {
  var lastSheet = PropertiesService.getUserProperties().getProperty("LastActiveSheet");
  if (e.source.getActiveSheet().getName() == lastSheet) {
    return;
  }
  else {
    PropertiesService.getUserProperties().setProperty("LastActiveSheet", e.source.getActiveSheet().getName())
    SpreadsheetApp.getActiveSheet().getRange("A1").activate();
  }
}

I hope this is helpful to you!

References:

• Simple Triggers | Apps Script | Google Developers

这篇关于如何使Google电子表格的所有工作表在左上方显示A1?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！